读取EXCEL内容并插入到数据库中
package app.creditapp.templet.action;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import app.creditapp.templet.entity.CifFinBuluInfo;
import app.creditapp.trade.Util.CommonUtil;
import app.util.DBUtils;
import com.core.domain.screen.FormData;
import com.core.service.screen.FormService;
import com.core.struts.ActionContext;
import com.core.struts.BaseFormBean;
public class CifFinBuluAction extends BaseFormBean{
private String query;
private String mk_no;
private String message="";
private String filepath;
private XSSFWorkbook xwb;
private Workbook wb;
private Workbook rwb;
private CifFinBuluInfo cifFinBuluInfo;
private FormData formciffinbulu0001;
private FormService formService = new FormService();
private DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
private String DataSql = "INSERT INTO CIF_FIN_DATA_BULU (CIF_NO,CIF_NAME,MK_NO,ITEM_NO,"
+ "Y14_BEGAMT,Y14_ENDAMT,Y15_BEGAMT,"
+ "Y15_ENDAMT,Y16_BEGAMT,Y16_ENDAMT) " + "VALUES (";
private String BaseSql = "INSERT INTO CIF_FIN_BASE_BULU "
+ "(CIF_NAME,MX_NO,RPT_DATE,CYC_TYPE,FIN_STS,RPT_CALI,IF_AUDIT,AUDIT_CON,IF_ADJ," +
"CIF_NO,OP_NO,OP_NAME,BR_NAME,AUDIT_ORG,ADJ_CAUSE) VALUES(";
public CifFinBuluAction() {
query="";
}
public String input(){
ActionContext.initialize(ServletActionContext.getRequest(),ServletActionContext.getResponse());
formciffinbulu0001 = formService.getFormData("ciffinbulu0001");
return "list";
}
public String readfileName() throws Exception{
ActionContext.initialize(ServletActionContext.getRequest(),ServletActionContext.getResponse());
this.getHttpResponse().setContentType("text/html;charset=utf-8");
PrintWriter out = this.getHttpResponse().getWriter();
message = writefileName(URLDecoder.decode(filepath, "UTF-8"),"");
out.print(message);
return null;
}
public String writefileName(String filePath,String message){
try {
File file = new File(filePath);
if(!file.isDirectory()){
message = "0@该路径"+filePath+"不存在";
} else {
String[] fileList = file.list();
if(!"".equals(fileList) && fileList != null && fileList.length > 0){
String otherfilepath = "";
for(int i = 0; i< fileList.length;i++){
String newFilePath = filePath+"\\"+fileList[i];
File readFile = new File(newFilePath);
if(!readFile.isDirectory()){
otherfilepath = otherfilepath + fileList[i]+";\n";
} else {
writefileName(newFilePath,otherfilepath);
}
}
message = "1@"+otherfilepath;
} else {
message = "0@该路径"+file+"无文件!";
}
}
} catch (Exception e) {
message = "0@Exception:"+e.getMessage();
}
return message;
}
//导入数据操作
public String ImportData(){
ActionContext.initialize(ServletActionContext.getRequest(),ServletActionContext.getResponse());
formciffinbulu0001 = formService.getFormData("ciffinbulu0001");
getFormValue(formciffinbulu0001);
cifFinBuluInfo=new CifFinBuluInfo();
setObjValue(formciffinbulu0001, cifFinBuluInfo);
System.out.println("------------------start-----------------------");
String start_date=df.format(new Date());
long time=0;
String message = ReadFile(cifFinBuluInfo);
this.addActionMessage(message);
System.out.println(message);
String end_date=df.format(new Date());
try {
time = df.parse(end_date).getTime()-df.parse(start_date).getTime();
} catch (ParseException e) {
e.printStackTrace();
}
System.out.println("总共耗时:"+time/1000+"秒");
System.out.println("------------------end-----------------------");
return "list";
}
// 遍历文件
public String ReadFile(CifFinBuluInfo cifFinBuluInfo) {
String message = "";
String path = cifFinBuluInfo.getPath();
String file_Name = cifFinBuluInfo.getFile_Name();
File file = new File(path);
int count = 0;
int sumCount = 0;
if (!file.exists()) {
path = path.replaceAll("\\\\", "/");
message = path +"该路径下不存在 ,请核实路径是否填写正确";
System.out.println(path +"该目录不存在,请核实该路径是否正确**************");
} else {
if(!"".equals(file_Name.trim()) && file_Name.trim() != null && file_Name.indexOf(";") > -1 ){
String[] fileList = file_Name.split(";");
Connection conn = null;
Statement st = null;
String FileMessage = "";
String NotFileMessage = "";
try {
conn = DBUtils.getConn();
st = conn.createStatement();
if (conn != null) {
for (int i = 0; i < fileList.length; i++) {
String filepath=path + "\\" + fileList[i];
String fileNameStr = filepath.replaceAll("\\r\\n", "");
File filePath = new File(fileNameStr);
// if(!filePath.renameTo(filePath)){
// System.out.println(filePath+" 该文件处于打开状态,请关闭该文件");
// message=(filepath.replaceAll("\\\\", "/")+"该文件处于打开状态,请关闭该文件!</br>");
// break;
// }
List<String> listResult = null;
if (fileNameStr.endsWith(".xlsx")) {
if (!filePath.exists()) {
NotFileMessage = NotFileMessage + fileNameStr +"</br>";
System.out.println(fileNameStr + "文件不存在!");
continue;
} else {
listResult = ReadExcelXlsx(filePath);
}
} else if (fileNameStr.endsWith(".xls")) {
if (!filePath.exists()) {
NotFileMessage = NotFileMessage + fileNameStr.replaceAll("\\\\", "/") +"</br>";
System.out.println(fileNameStr + "文件不存在!");
continue;
} else {
listResult = ReadXls(filePath);
}
}
if (listResult != null && listResult.size() != 0) {
for (int j = 0; j < listResult.size(); j++) {
count = count + st.executeUpdate((String)listResult.get(j));
}
sumCount =sumCount + count;
System.out.println( "该财务报表模板"+file.getName()+"共插入"+ count + "行数据");
count=0;
} else {
if(NotFileMessage.trim()==null || "".equals(NotFileMessage.trim())){
NotFileMessage = fileNameStr.replaceAll("\\\\", "/")+ "表中无数据</br>";
}else{
NotFileMessage = NotFileMessage + fileNameStr.replaceAll("\\\\", "/")+ " 表中无数据</br>";
}
System.out.println(fileNameStr+ "该EXCEL表中无数据…………………………");
}
}
FileMessage = "总共插入"+ sumCount + "行数据";
message =message + NotFileMessage + FileMessage;
} else {
message = "数据库连接失败!";
System.out.println("数据库连接失败~~~~
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.closeStatement(st);
DBUtils.closeConnection(conn);
}
} else {
message = "文件名不能为空或文件名填写不符合规范!";
System.out.println(file_Name +"文件名不能为空或文件名不符合规范!**************");
}
}
return message;
}
/*
* 读取文件名以XLS结尾的EXCEL表格
*/
public List<String> ReadXls(File file) {
List<String> list = new ArrayList<String>();
WorkbookSettings setEncode = new WorkbookSettings();
try {
wb = Workbook.getWorkbook(file);
setEncode = new WorkbookSettings();
setEncode.setEncoding("UTF-8");
rwb = Workbook.getWorkbook(file, setEncode);
Sheet[] sheets = rwb.getSheets();
for (int k = 0; k < sheets.length; k++) {
Sheet sheet = wb.getSheet(k);
System.out.println(sheet.getName());
if (k == 0) {
String BaseSqlstr = "";
String Sqlstr = "";
for (int i = 2; i < sheet.getRows(); i++) {
Cell[] cellarr = sheet.getRow(i);
if (cellarr.length == 0) {
break;
} else {
for (int j = 1; j < 10; j++) {
Cell cell = sheet.getCell(j, i);
String contents = cell.getContents();// 单元格内容
if (j == 2) {
Sqlstr += "'10000051-新会计准则财务报表',";
continue;
}
if (contents != null && !"".equals(contents)) {
Sqlstr += "'" + contents + "',";
} else {
if (j == 1) {
break;
} else {
Sqlstr += "'',";
}
}
}
}
String fixedValue = "'" + file.getName() + "','','','','','')";
BaseSqlstr = BaseSql + Sqlstr + fixedValue;
System.out.println("BaseSqlstr:" + BaseSqlstr);
list.add(BaseSqlstr);
Sqlstr = "";
}
} else {
String DataSqlstr = "";
String cif_name = "";
String Sqlstr = "";
for (int i = 0; i < sheet.getRows(); i++) {
if (i == 1 || i == 36 || i == 37 || i == 69 || i == 70 || i == 91 || i == 92) {
if (i == 69) {
mk_no = "100050-资产负债表(新)";
Sqlstr = "'100174-未结清对外担保余额',0,0,0,0,0,0)";
DataSqlstr = DataSql + cif_name + "'" + mk_no + "'," + Sqlstr;
list.add(DataSqlstr);
Sqlstr = "";
}
continue;
}
for (int j = 1; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
String contents = cell.getContents().trim();// 单元格内容
if (i == 0) {
cif_name = "'" + file.getName() + "','" + contents + "',";
break;
} else if (i > 2 && i < 69) {
mk_no = "100050-资产负债表(新)";
if (j == 1) {
Sqlstr += "'" + contents + "',";
} else {
if (contents != null && !"".equals(contents)) {
Pattern p = Pattern.compile("-[0-9]+(.[0-9]+)?|[0-9]+(.[0-9]+)?");
Matcher m = p.matcher(contents);
if (!m.matches()) {
contents = "0";
}
String contentsValue = new DecimalFormat("#0.000000").format(CommonUtil.strToDouble(contents));
if (j != 7) {
Sqlstr += contentsValue + ",";
} else {
Sqlstr += contentsValue + ")";
}
} else {
if (j != 7) {
Sqlstr += 0 + ",";
} else {
Sqlstr += 0 + ")";
}
}
}
} else if (i > 70 && i <= sheet.getRows()) {
if (i > 70 && i < 91) {
mk_no = "100053-损益表(新)";
} else {
mk_no = "100052-现金流量表(新)";
}
if (j == 1) {
Sqlstr += "'" + contents + "',";
} else {
if (contents != null
&& !"".equals(contents)) {
Pattern p = Pattern.compile("-[0-9]+(.[0-9]+)?|[0-9]+(.[0-9]+)?");
Matcher m = p.matcher(contents);
if (!m.matches()) {
contents = "0";
}
String contentsValue = new DecimalFormat("#0.000000").format(CommonUtil.strToDouble(contents));
if (j != 6) {
if (j == 7) {
break;
}
if (i > 70 && i < 91) {
Sqlstr += contentsValue + "," + 0 + ",";
} else {
Sqlstr += 0 + "," + contentsValue + ",";
}
} else {
if (i > 70 && i < 91) {
Sqlstr += contentsValue + "," + 0 + ")";
} else {
Sqlstr += 0 + "," + contentsValue + ")";
}
}
} else {
if (j != 6) {
if (j == 7) {
break;
} else if (j == 2 | j == 3) {
continue;
}
Sqlstr += 0 + "," + 0 + ",";
} else {
Sqlstr += 0 + "," + 0 + ")";
}
}
}
}
}
if (Sqlstr != null && mk_no != null && !"".equals(Sqlstr) && !"".equals(mk_no)) {
DataSqlstr = DataSql + cif_name + "'" + mk_no + "'," + Sqlstr;
System.out.println("DataSqlstr-----" + DataSqlstr);
list.add(DataSqlstr);
Sqlstr = "";
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
rwb.close();
wb.close();
}
return list;
}
/*
* 读取文件名以XLSX结尾的EXCEL表格
*/
public List<String> ReadExcelXlsx(File file) {
List<String> list = new ArrayList<String>();
System.out.println("文件名:" + file.getName());
try {
xwb = new XSSFWorkbook(file);
for (int k = 0; k < xwb.getNumberOfSheets(); k++) {
XSSFSheet sheet = xwb.getSheetAt(k);
if (sheet == null || sheet.getLastRowNum() == 0) {
break;
} else {
XSSFRow row = null;
System.out.println(xwb.getSheetName(k));
if (k == 0) {
String BaseSqlstr = "";
String Sqlstr = "";
for (int i = 2; i < sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
if (row == null) {
break;
} else {
for (int j = 1; j < 10; j++) {
XSSFCell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
cell.setCellValue("");
}
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
String contents = cell.getStringCellValue();// 单元格内容
if (j == 2) {
Sqlstr += "'10000051-新会计准则财务报表',";
continue;
}
if (contents != null && !"".equals(contents)) {
Sqlstr += "'" + contents + "',";
} else {
if (j == 1) {
break;
}else {
Sqlstr += "'',";
}
}
}
}
if (Sqlstr != null && !"".equals(Sqlstr)) {
String fixedValue = "'" + file.getName() + "','','','','','')";
BaseSqlstr = BaseSql + Sqlstr + fixedValue;
System.out.println("BaseSqlstr:" + BaseSqlstr);
list.add(BaseSqlstr);
Sqlstr = "";
}
}
} else {
String DataSqlstr = "";
String cif_name = "";
String Sqlstr = "";
for (int i = 0; i < 126; i++) {
if (i == 1 || i == 36 || i == 37 || i == 69 || i == 70 || i == 91 || i == 92) {
if (i == 69) {
mk_no = "100050-资产负债表(新)";
Sqlstr = "'100174-未结清对外担保余额',0,0,0,0,0,0)";
DataSqlstr = DataSql + cif_name + "'" + mk_no + "'," + Sqlstr;
// System.out.println("DataSqlstr-----" + DataSqlstr);
list.add(DataSqlstr);
Sqlstr = "";
}
continue;
}
row = sheet.getRow(i);
for (int j = 1; j < 8; j++) {
XSSFCell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
cell.setCellValue("");
}
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
String contents = cell.getStringCellValue().trim();
if (i == 0) {
cif_name = "'" + file.getName() + "','" + contents + "',";
break;
} else if (i > 2 && i < 69) {
mk_no = "100050-资产负债表(新)";
if (j == 1) {
Sqlstr += "'" + contents + "',";
} else {
if (contents != null && !"".equals(contents)) {
Pattern p = Pattern.compile("-[0-9]+(.[0-9]+)?|[0-9]+(.[0-9]+)?");
Matcher m = p.matcher(contents);
if (!m.matches()) {
contents = "0";
}
String contentsValue = new DecimalFormat("#0.000000").format(CommonUtil.strToDouble(contents));
if (j != 7) {
Sqlstr += contentsValue + ",";
} else {
Sqlstr += contentsValue + ")";
}
} else {
if (j != 7) {
Sqlstr += 0 + ",";
} else {
Sqlstr += 0 + ")";
}
}
}
} else if (i > 70 && i <= sheet.getLastRowNum()) {
if (i > 70 && i < 91) {
mk_no = "100053-损益表(新)";
} else {
mk_no = "100052-现金流量表(新)";
}
if (j == 1) {
Sqlstr += "'" + contents + "',";
} else {
if (contents != null && !"".equals(contents)) {
Pattern p = Pattern.compile("-[0-9]+(.[0-9]+)?|[0-9]+(.[0-9]+)?");
Matcher m = p.matcher(contents);
if (!m.matches()) {
contents = "0";
}
String contentsValue = new DecimalFormat("#0.000000").format(CommonUtil.strToDouble(contents));
if (j != 6) {
if (j == 7) {
break;
}
if (i > 70 && i < 91) {
Sqlstr += contentsValue + "," + 0 + ",";
} else {
Sqlstr += 0 + "," + contentsValue + ",";
}
} else {
if (i > 70 && i < 91) {
Sqlstr += contentsValue + "," + 0 + ")";
} else {
Sqlstr += 0 + "," + contentsValue + ")";
}
}
} else {
if (j != 6) {
if (j == 7) {
break;
} else if (j == 2 | j == 3) {
continue;
}
Sqlstr += 0 + "," + 0 + ",";
} else {
Sqlstr += 0 + "," + 0 + ")";
}
}
}
}
}
if (Sqlstr != null && mk_no != null && !"".equals(Sqlstr) && !"".equals(mk_no)) {
DataSqlstr = DataSql + cif_name + "'" + mk_no + "'," + Sqlstr;
// System.out.println("DataSqlstr-----" + DataSqlstr);
list.add(DataSqlstr);
Sqlstr = "";
}
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (xwb != null) {
xwb.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return list;
}
public String getQuery() {
return query;
}
public void setQuery(String query) {
this.query = query;
}
public CifFinBuluInfo getCifFinBuluInfo() {
return cifFinBuluInfo;
}
public void setCifFinBuluInfo(CifFinBuluInfo cifFinBuluInfo) {
this.cifFinBuluInfo = cifFinBuluInfo;
}
public FormService getFormService() {
return formService;
}
public void setFormService(FormService formService) {
this.formService = formService;
}
public FormData getFormciffinbulu0001() {
return formciffinbulu0001;
}
public void setFormciffinbulu0001(FormData formciffinbulu0001) {
this.formciffinbulu0001 = formciffinbulu0001;
}
public String getFilepath() {
return filepath;
}
public void setFilepath(String filepath) {
this.filepath = filepath;
}
}