用到的jar包
代码
package com.exceloperation; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.Iterator; import java.util.List; import javax.rmi.CORBA.Tie; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.FormulaEvaluator; 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; public class ExcelOperation { /** * 存所有的excel数据 */ static List<List<Object>>all=new ArrayList<List<Object>>(); static Connection conn =null; public static void main(String[] args) { importExcel("D://数据模板1.xlsx"); // System.out.println(conn); // try{ // Statement createStatement = conn.createStatement(); // for (List<Object> list : all) { // String sql="insert into excelTable(tradType,tradTime,tradWater,institution,phone,name,"+ // "psamId,tradNo,tradNoTwo,tradMoney,tradPoundage,tradStatus,remark)"+"values("+ // "'"+list.get(1)+"','"+list.get(2)+"','"+list.get(3)+"','"+list.get(4)+"'," + // "'"+list.get(5)+"','"+list.get(6)+"','"+list.get(7)+"','"+list.get(8)+"',"+ // "'"+list.get(9)+"','"+list.get(10)+"','"+list.get(11)+"','"+list.get(12)+"','"+list.get(13)+"')"; // System.out.println(sql); // createStatement.execute(sql); // } // conn.close(); // }catch (Exception e) { // e.printStackTrace(); // } //导出 outExcel("D://123456.xls"); } static { try { String url = "jdbc:sqlserver://192.168.1.177:1433;DatabaseName=excelDataBase"; String uername = "sa"; String password = "123456"; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); conn = DriverManager.getConnection(url, uername, password); } catch (Exception e) { e.printStackTrace(); } } /** *导出excel */ private static void outExcel(String path){ try { //List<String>data=new ArrayList<String>(); String[] title={"编号","交易类型","交易时间","交易流水","隶属机构","手机号","姓名","PSAM卡号", "交易卡号","第二次交易账号","交易金额","交易手续费","交易状态","备注"}; HSSFWorkbook wb=new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet1"); for(int sheetWidth=0;sheetWidth<title.length;sheetWidth++){ sheet.setColumnWidth(sheetWidth, 3700); } HSSFRow row = sheet.createRow(0); HSSFCellStyle style=wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setWrapText(true); HSSFCell cell=row.createCell(0); //设置title for(int i=0;i<title.length;i++){ cell.setCellValue(title[i]); cell.setCellStyle(style); cell=row.createCell((short)(i+1)); } //写入数据 for(int r=0;r<all.size();r++){ List<Object> list = all.get(r); row=sheet.createRow(r+1); for(int c=0;c<title.length;c++){ row.createCell((short)c).setCellValue(list.get(c)+""); } } FileOutputStream out=new FileOutputStream(path); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 获取excel的数据 */ private static void importExcel(String path){ StringBuilder sbu=new StringBuilder(); String xls=".xls"; String xlsx=".xlsx"; String ext=path.substring(path.lastIndexOf("."), path.length()).toLowerCase(); System.out.println(ext); Workbook wl=null; if(!new File(path).exists()) { throw new RuntimeException("文件不存在!"); } try{ if(ext.equals(xls)){ wl=new HSSFWorkbook(new FileInputStream(new File(path))); } if(ext.equals(xlsx)){ wl=new XSSFWorkbook(new FileInputStream(new File(path))); } // 解析公式结果 FormulaEvaluator evaluator = wl.getCreationHelper().createFormulaEvaluator(); Sheet sheetAt = wl.getSheetAt(0); int firstRowNum = sheetAt.getFirstRowNum(); int lastRowNum = sheetAt.getLastRowNum(); for(int i=firstRowNum;i<=lastRowNum;i++){ List<Object>domian=new ArrayList<Object>(); Row row = sheetAt.getRow(i+2); short firstColumnNum = row.getFirstCellNum(); short lastColumnNum = row.getLastCellNum(); for(short j=firstColumnNum;j<lastColumnNum;j++){ Cell cell = row.getCell(new Integer(j)); CellValue eva = evaluator.evaluate(cell); if(null==eva){ continue; } // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了 // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html switch (eva.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: domian.add(eva.getBooleanValue()); sbu.append(eva.getBooleanValue()+" "); break; case Cell.CELL_TYPE_NUMERIC: if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)){ //new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue()) domian.add(cell.getDateCellValue()); sbu.append(cell.getDateCellValue()+" "); }else{ domian.add(eva.getNumberValue()); sbu.append(eva.getNumberValue()+" "); } break; case Cell.CELL_TYPE_STRING: domian.add(eva.getStringValue()); sbu.append(eva.getStringValue()+" "); break; case Cell.CELL_TYPE_FORMULA: break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } } all.add(domian); sbu.append(" "); } }catch (Exception e) { e.printStackTrace(); } System.out.println(sbu.toString()); System.out.println("----------------"); for (List<Object> list : all) { System.out.println(Arrays.asList(list.toArray())); } } }