package com.reliable.service;import com.csvreader.CsvReader;import com.reliable.bean.FileDict;import com.reliable.dao.ReadFile;import com.reliable.util.JDBCUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.*;import java.nio.charset.Charset;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.ArrayList;public class ReadFileImpl implements ReadFile {private int MAX_CELL_NUM; //文件中最长的一行具有的单元格数private PreparedStatement preparedStatement=null;private Connection conn =null;//--------------------------------------------------public ArrayList<ArrayList<String>> readExcelFile(String path) throws IOException, SQLException {System.out.println(\"excel文件路径：\"+path); //输出文件路径ArrayList<String> tableName = new ArrayList<String>();String tableName_1=\"\"; //备份表名String tableName_2=\"\"; //操作表名ArrayList<ArrayList<String>> tableValue = new ArrayList<ArrayList<String>>(); //数据表的值File excel=new File(path);String[] split = excel.getName().split(\"\\\\.\"); //.是特殊字符，需要转义！System.out.println(split[0]+\" \"+split[1]);tableName_1=split[0]; //给备份数据表名赋值tableName_2=split[0]+\"_\"+split[1]; //给操作表名赋值System.out.println(\"备份表名：\" + tableName_1);System.out.println(\"操作表名： \" + tableName_2);tableName.add(tableName_1);tableName.add(tableName_2);tableValue.add(tableName);Workbook wb; //新建文件FileInputStream fileStream = new FileInputStream(excel); //文件流对象//根据文件后缀（xls/xlsx）进行判断if (\"xls\".equals(split[1])){//xls文件wb = new HSSFWorkbook(fileStream);}else{//xlsx文件wb = new XSSFWorkbook(fileStream);}//开始解析Sheet sheet = wb.getSheetAt(0); //读取sheet 0MAX_CELL_NUM=getMaxCellNumExcel(sheet);int firstRowIndex = sheet.getFirstRowNum(); //获取第一行索引int lastRowIndex = sheet.getLastRowNum(); //获取最后一行索引for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行Row row = sheet.getRow(rIndex); //获取行索引ArrayList<String> tempTableValue = new ArrayList<String>(); //暂存一行的值，之后放到tableValueif (row != null) {int lastCellIndex = MAX_CELL_NUM; //行的最后一个单元格for (int cIndex = 0; cIndex < lastCellIndex; cIndex++) { //遍历列（单元格）Cell cell = row.getCell(cIndex,Row.RETURN_BLANK_AS_NULL); //允许读空的单元格if ((cell == null)) {tempTableValue.add(\"NULL\");}else {cell.setCellType(Cell.CELL_TYPE_STRING); //转换单元格数据格式为StringtempTableValue.add(cell.getStringCellValue());}}tableValue.add(tempTableValue);}}System.out.println(\"读出Excel文件的数据： \"+tableValue); //输出表格的所有值wb.close();//---------------------------------------------------------------------return tableValue;}//读CSV文件public ArrayList<ArrayList<String>> readCsvFile(String path) throws SQLException, IOException {System.out.println(\"CSV文件的路径： \"+path);//首先判断编码格式String code=new String();ArrayList<ArrayList<String>> WrongData=new ArrayList<ArrayList<String>>();ArrayList<String> tableField = new ArrayList<String>(); //数据表字段名ArrayList<ArrayList<String>> tableValue = new ArrayList<ArrayList<String>>(); //数据表的值String tableName_1=\"\"; //备份表名String tableName_2=\"\"; //操作表名ArrayList<String> tableName = new ArrayList<String>(); //存放两个表名File file = new File(path);InputStream in= new FileInputStream(file);byte[] b = new byte[3];in.read(b);in.close();if (b[0] == -17 && b[1] == -69 && b[2] == -65){code=\"UTF-8\";System.out.println(file.getName() + \"：编码为UTF-8\");}else{code=\"GBK\";System.out.println(file.getName() + \"：可能是GBK，也可能是其他编码\");}try {// 创建CSV读对象CsvReader csvReader = new CsvReader(path,\',\', Charset.forName(code));String[] split1 = path.split(\"\\\\.\"); //.是特殊字符，需要转义！// System.out.println(split1[0]+\" \"+split1[1]);String[] split2 = split1[0].split(\"\\\\\\\\\");// System.out.println(split2[split2.length-1]);tableName_1=split2[split2.length-1];tableName_2=split2[split2.length-1]+\"_\"+split1[1];System.out.println(\"备份表名：\"+tableName_1);System.out.println(\"操作表名：\"+tableName_2);tableName.add(tableName_1);tableName.add(tableName_2);tableValue.add(tableName);WrongData.add(tableName);int rIndex=0;// 跳过表头// csvReader.readHeaders();boolean flag=true;while (csvReader.readRecord()){// 读一整行String resString=\"\";resString = csvReader.getRawRecord();resString=resString.replace(\",\",\" , \"); //替换， 为,空格System.out.println(resString);if(flag==true){tableField.add(resString);tableValue.add(tableField);WrongData.add(tableField);flag=false;continue; //存放表头数据}else{}String[] resString_list=resString.split(\",\");String[] tableField_list=tableField.get(0).split(\",\");ArrayList<String> tempTableValue = new ArrayList<String>(); //暂存一行的值，之后放到tableValueif(resString_list.length != tableField_list.length){ //判断这一行的长度和第一行是否一样tempTableValue.add(resString);WrongData.add(tempTableValue); //如果不一样那就是错误数据}else {tempTableValue.add(resString);tableValue.add(tempTableValue);}rIndex++;}} catch (IOException e) {e.printStackTrace();}//name1,sex1,age1// System.out.println(tableValue.get(0).get(0));if (WrongData.size()!=2){System.out.println(\"CSV文件的错误数据： \"+WrongData);return WrongData;}else {System.out.println(\"CSV文件的数据： \"+tableValue);return tableValue;}}//--------------------------------------------------//插入数据库 xls和xlsx文件public void insertExcelTable(ArrayList<ArrayList<String>> tableValue){conn=JDBCUtil.getConnection();ArrayList<String> tableName = tableValue.get(0);tableValue.remove(0); //删除第一行的值，表头信息不需要插入数据库tableValue.remove(0); //再次删除掉字段值for(int j=0;j<tableName.size();j++){for (ArrayList<String> item1 : tableValue){String INSERT_TABLE_SQL=\"INSERT INTO \"+tableName.get(j)+\" VALUES(\";String tempString=new String(); //组装SQL语句for (int i =0 ;i<item1.size();i++){if(i!=item1.size()-1){tempString=tempString+\"\\\"\"+item1.get(i)+\"\\\"\"+\",\";}else{tempString=tempString+\"\\\"\"+item1.get(i)+\"\\\"\";}}INSERT_TABLE_SQL=INSERT_TABLE_SQL+tempString+\");\";System.out.println(INSERT_TABLE_SQL);try{preparedStatement= conn.prepareStatement(INSERT_TABLE_SQL);preparedStatement.executeUpdate();conn.setAutoCommit(false);conn.commit();System.out.println(\"\\n\");}catch(SQLException e){e.printStackTrace();}}}}public void insertCsvTable(ArrayList<ArrayList<String>> tableValue){ArrayList<String> tableName = tableValue.get(0);tableName=tableValue.get(0);tableValue.remove(0);tableValue.remove(0);conn=JDBCUtil.getConnection();System.out.println(\"数据表长度： \" + tableValue.size());//插入for(int j=0;j<tableName.size();j++){for (int i=0 ;i<tableValue.size();i++){String INSERT_TABLE_SQL=\"INSERT INTO \"+tableName.get(j)+\" VALUES( \";String tempString=new String();String[] tempValue={};tempValue=tableValue.get(i).get(0).split(\"\\\\,\");// System.out.println(tempValue[tempValue.length-1]);for (int k=0 ; k<tempValue.length; k++){if(k!=tempValue.length-1){tempString=tempString +\"\\\"\" +tempValue[k]+\"\\\"\" +\",\";}else{tempString=tempString+\"\\\"\" +tempValue[k]+\"\\\"\";}}INSERT_TABLE_SQL=INSERT_TABLE_SQL+tempString+\");\";System.out.println(INSERT_TABLE_SQL);try{preparedStatement= conn.prepareStatement(INSERT_TABLE_SQL);preparedStatement.executeUpdate();conn.setAutoCommit(false);conn.commit();}catch(SQLException e){e.printStackTrace();}finally{//关闭数据库连接}}}}//--------------------------------------------------//获取excel表一行最大的单元格数目的方法public int getMaxCellNumExcel(Sheet sheet){int resNum=0;int firstRowIndex = sheet.getFirstRowNum(); //获取第一行索引int lastRowIndex = sheet.getLastRowNum(); //获取最后一行索引for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行Row row = sheet.getRow(rIndex); //获取行索引if(row.getLastCellNum()>resNum){resNum=row.getLastCellNum();}}return resNum;}//在DictServlet中被调用，同时调用read文件的方法给这个方法传参数@Overridepublic FileDict getExcelDict(ArrayList<ArrayList<String>> ExcelValue) {//根据传过来的字段名建立数据字典//描述默认 \"\"ArrayList<String> fieldDescribe=new ArrayList<>();//字段类型默认textArrayList<String> fieldType=new ArrayList<>();//大小默认显示 256ArrayList<String> fieldSize=new ArrayList<>();//单位默认 \"\"ArrayList<String> fieldUnit=new ArrayList<>();fieldSize.add(\"256\");fieldType.add(\"text\");fieldDescribe.add(\"无\");fieldUnit.add(\"无\");FileDict fileDict = new FileDict();fileDict.setFieldDescribe(fieldDescribe);fileDict.setFieldType(fieldType);fileDict.setFieldSize(fieldSize);fileDict.setTableName(ExcelValue.get(0));fileDict.setNewFieldName(ExcelValue.get(1));fileDict.setFieldName(ExcelValue.get(1));fileDict.setFieldUnit(fieldUnit);return fileDict;}@Overridepublic FileDict getCsvDict(ArrayList<ArrayList<String>> CsvValue) {FileDict fileDict = new FileDict();String[] fieldNameList=CsvValue.get(1).get(0).split(\"\\\\,\");String[] newFieldNameList=CsvValue.get(1).get(0).split(\"\\\\,\");ArrayList<String> fieldName=new ArrayList<>();ArrayList<String> newFieldName=new ArrayList<>();for (int i = 0;i<fieldNameList.length;i++){fieldName.add(fieldNameList[i]);}for (int i = 0;i<newFieldNameList.length;i++){newFieldName.add(newFieldNameList[i]);}//描述默认 \"\"ArrayList<String> fieldDescribe=new ArrayList<>();//字段类型默认textArrayList<String> fieldType=new ArrayList<>();//大小默认显示 256ArrayList<String> fieldSize=new ArrayList<>();//单位默认 \"\"ArrayList<String> fieldUnit=new ArrayList<>();fieldSize.add(\"256\");fieldType.add(\"text\");fieldDescribe.add(\"无\");fieldUnit.add(\"无\");fileDict.setFieldDescribe(fieldDescribe);fileDict.setFieldType(fieldType);fileDict.setFieldSize(fieldSize);fileDict.setFieldName(fieldName);fileDict.setNewFieldName(newFieldName);fileDict.setTableName(CsvValue.get(0));fileDict.setFieldUnit(fieldUnit);return fileDict;}@Overridepublic void createTable(ArrayList<String[]> tableInfo) {//获取SqlsessionString DROP_TABLE_1=\"\";String DROP_TABLE_2=\"\";String CREATE_TABLE_1_SQL=\"\";String CREATE_TABLE_2_SQL=\"\";DROP_TABLE_1=\"DROP TABLE IF EXISTS \"+ tableInfo.get(0)[0] +\";\";DROP_TABLE_2=\"DROP TABLE IF EXISTS \"+tableInfo.get(0)[1] +\";\";CREATE_TABLE_1_SQL=\"CREATE TABLE \"+ tableInfo.get(0)[0] +\"(\" ;CREATE_TABLE_2_SQL=\"CREATE TABLE \"+ tableInfo.get(0)[1] +\"(\" ;//生成备份表建表语句 （字段、描述、类型）CREATE_TABLE_1_SQL=getTableSQL(CREATE_TABLE_1_SQL, tableInfo.get(1) , tableInfo.get(2),tableInfo.get(3),tableInfo.get(4));//生成操作表建表语句CREATE_TABLE_2_SQL=getTableSQL(CREATE_TABLE_2_SQL, tableInfo.get(1) , tableInfo.get(2),tableInfo.get(3),tableInfo.get(4));System.out.println(CREATE_TABLE_1_SQL);System.out.println(CREATE_TABLE_2_SQL);try {conn=JDBCUtil.getConnection();preparedStatement = conn.prepareStatement(DROP_TABLE_1);preparedStatement.executeUpdate();preparedStatement= conn.prepareStatement(CREATE_TABLE_1_SQL);preparedStatement.executeUpdate();preparedStatement = conn.prepareStatement(DROP_TABLE_2);preparedStatement.executeUpdate();preparedStatement= conn.prepareStatement(CREATE_TABLE_2_SQL);preparedStatement.executeUpdate();conn.setAutoCommit(false);conn.commit();JDBCUtil.release(conn,preparedStatement);} catch (SQLException e) {e.printStackTrace();System.out.println(\"出现问题！建表失败！\");}}//获取文件的建表sql语句public String getTableSQL(String SQL,String[] tableField, String[]fieldDescribe,String[] fieldUnit, String[] fieldType ){for (int i =0 ;i<tableField.length;i++){String item= tableField[i];if(i!=tableField.length-1){SQL=SQL + \"`\" + item + \"`\" +\" text COMMENT \" +\"\\\'\" +fieldDescribe[i]+\",\"+fieldUnit[i]+\"\\\'\" +\" COLLATE utf8_general_ci,\"+\"\\n\";}else{SQL=SQL + \"`\" + item + \"`\" +\" text COMMENT \"+\"\\\'\" +fieldDescribe[i]+\",\"+fieldUnit[i]+\"\\\'\" + \" COLLATE utf8_general_ci \"+\"\\n\";}}return SQL+\") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;\";}}
爱站程序员基地
