使用JXL与POI操作EXCEL
使用JXL读写文件
一、使用JXL读EXCEL
1、读取EXCEL截图：
2、步骤说明
（1）获取文件
（2）获取文件的WorkBook
（3）获取要读取的SHEET，SHEET下标从0开始
（4）首先确定参数，要读多少行与多少列，可以用程序自动获取，也可以手动控制，使用FOR循环遍历。
3、具体程序代码
/** * 使用JXL读Excel */public static void readExcelData(){File file = new File("D:/test.xls"); //1、获取文件Workbook book = null;try {book = Workbook.getWorkbook(file); //2、获取WorkbookSheet sheet = book.getSheet(1); //3、获取第 2 个SHEETStringBuilder sb = null;Cell[] cells = null;//遍历行 从第2行开始遍历到第8行 注意行索引从0开始for (int i = 1; i < 8; i++) { cells = sheet.getRow(i); //该列所有值if(cells.length == 0){ //如果该列没有值 就continue;continue;}sb = new StringBuilder("");//遍历列 从第1列开始遍历到第3列 注意列索引从0开始for (int j = 0; j < 3; j++) {int index = cells[j].getColumn();//获取该列的列号 为数值String key = NumberUtils.toLetterString(index+1); //将数值转换成对应的A，B，C...String value = cells[j].getContents().trim();//获取单元格的值String cellType = cells[j].getCellFormat().getFormat().getFormatString().toUpperCase(); //获取单元格样式即格式编码sb.append(value+",");}//省略了存储在List中的过程sb.deleteCharAt(sb.lastIndexOf(","));System.out.println(sb.toString());}book.close(); //关闭book} catch (Exception e) {e.printStackTrace();}}
4、执行结果：
5、经验：
<1>存储读取的数据，可以使用Map存储。每一行数据都是一个Map，key为列号即A,B,C…value就为单元格的值。如果有多行，就需要在创建一个List，封装这些Map，list的size即行数。完整结构为List<Map<String,String>>但List为无序的，如果你要将数据保存到数据库中，最后要回写到EXCEL中，建议Map中除了保存单元格值以外，再保存一个行号。最后获取的时候用行号排序即可。如果不牵扯到数据库，可以使用其他有序集合。
<2>获取数据后，往往要对数据的真假以及单元格格式做校验，此时，要规定一些约束。因为excel中的单元格格式太多，用程序不好控制，所以必须约束为某些固定的格式。
<3>如果excel中的sheet很多，并且保存数据库要求sheet与后台表结构一一对应，那么需要根据excel模版去创建表机构，就需要有模版管理的模版去完成配置，利用前端配置生成建表SQL。此时，可以顺便拼接入库的SQL和查询的SQL。再需要再插入或者查询时，直接使用即可。
6、补充
使用JXL读取单元格公式：
if(cells[j].getType() == CellType.NUMBER_FORMULA ||
cells[j].getType() == CellType.STRING_FORMULA ||
cells[j].getType() == CellType.BOOLEAN_FORMULA ||
cells[j].getType() == CellType.DATE_FORMULA ||
cells[j].getType() == CellType.FORMULA_ERROR){
FormulaCell fCell = (FormulaCell)cells[j];
System.out.println(fCell.getFormula());
二、使用JXL写EXCEL
1、 效果截图：
向excel的sheet中写入以下数据：
2、 步骤说明：
（1） 定义两个文件，一个是源文件，一个是副本文件。副本文件是用于写的文件。（JXL不允许对源文件做操作，允许创建副本做操作）。
（2） 打开源文件，创建一个副本文件。
（3） 打开或者创建一个SHEET。
（4） 做add操作。如果新增的单元格数值有固定格式，需要创建对应样式。
3、 具体代码实现
/** * 向excel写 */public static void writeExcelTest(){WritableWorkbook book = null; //打开一个EXCELWorkbook wb = null;File file = new File("D:/test.xls");File file2 = new File("D:/test_jxl_writer.xls");try {wb = Workbook.getWorkbook(file);// 打开一个文件的副本，并且指定数据写回到原文件book = Workbook.createWorkbook(file2, wb);WritableSheet sheet = book.createSheet("jxl_wirter", 2);//getSheet(0);//常规sheet.addCell(new Label(1,0,"aaa")); //参数：列,行,值 下标从0开始 以下类似//日期SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");java.util.Date date=sdf.parse("2012-07-09");jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-mm-dd"); jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df); jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 1,date,wcfDF); sheet.addCell(labelDTF); //百分比NumberFormat nfPERCENT_FLOAT = new jxl.write.NumberFormat("0.00%");WritableCellFormat wcfnfPERCENT_FLOAT = new jxl.write.WritableCellFormat(nfPERCENT_FLOAT);jxl.write.Number numSalerate = new jxl.write.Number(1,2,Double.parseDouble("12.6") * 0.01,wcfnfPERCENT_FLOAT);sheet.addCell(numSalerate);//会计专用jxl.write.NumberFormat nf = new jxl.write.NumberFormat("0.00");jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);jxl.write.Number number = new jxl.write.Number(1, 3,Double.parseDouble("55.3"),wcfN);sheet.addCell(number);//整数jxl.write.NumberFormat nf2 = new jxl.write.NumberFormat("0");jxl.write.WritableCellFormat wcfN2 = new jxl.write.WritableCellFormat(nf2);jxl.write.Number number2 = new jxl.write.Number(1, 4,Double.parseDouble("10.00"),wcfN2);sheet.addCell(number2);book.write();//写wb.close();//关闭流book.close();//关闭流} catch (Exception e) {e.printStackTrace();}}
4、 经验：
使用jxl写excel有一些限制。因为jxl已经没有人维护了，所以版本较低，bug也多，好处是用来读excel还是不错的选择，效率比poi要高。但写文件有一些落后，比如不支持excel宏，所以写的文件里如果含有宏，多半会报错的。所以写excel建议使用POI会更好一些。
使用POI读写文件
一、使用POI读写EXCEL
1、 前言
项目中没有使用POI读excel文件。只用来写了，所以读和写的代码和案例放在一起了，其实在使用POI操作excel的时候，读和写可以说是一体的。既可以一边读也可以一边写。因为POI操作excel的前提是将EXCEL放在内存中操作的，既然已经在内存中，我们既可以读也可以写。不过也因为如此，如果excel文件过大，容易报内存溢出的问题。
当遇到该问题时，如果是单元测试，注意修改启动参数，如果是服务器报错，就修改服务器的初始内存大小：
单元测试解决方法如图：
2、 实现目标：
3、 步骤流程：
（1） 创建获取源文件和目标文件
（2） 获取源文件的FileInputStream流
（3） 获取源文件的PoiFSFileSystem流
（4） 获取源文件的HSSFWorkBook
（5） 遍历行列（下标从0开始）使用具体方法写入
（6） 遇到有样式的单元格需要创建样式并设置
（7） 写入到FileOutputStream指定的目标文件。关闭流
4、 具体代码
/** * 生成Excel文件 * @throws IOException */public static void newExcel(String fileDir) throws IOException{HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象wb.createSheet("业务状况表");//建立新的sheet对象wb.createSheet("损益表");//建立新的sheet对象wb.createSheet("Sheet3");//建立新的sheet对象// 生成文件FileOutputStream fileOut = new FileOutputStream(fileDir);wb.write(fileOut);fileOut.close();}/** * 保存数据到excel * @param fileDir * @param list */public static void saveToExcel(String fileDir,List<String> list){ FileOutputStream fos = null;FileInputStream fis = null;POIFSFileSystem poiFS = null;HSSFWorkbook wb = null;try {fis = new FileInputStream(fileDir);} catch (FileNotFoundException e) {e.printStackTrace();}try {poiFS = new POIFSFileSystem(fis);} catch (IOException e) {e.printStackTrace();}try {wb = new HSSFWorkbook(poiFS);} catch (IOException e) {e.printStackTrace();}String[] titles = {"编号","教师名称","薪水"};HSSFFont font_bold = wb.createFont();//字体font_bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗HSSFCellStyle titleStyle= wb.createCellStyle(); titleStyle.setFont(font_bold);HSSFSheet sheet = null;if(titles != null && titles.length > 0){sheet = wb.getSheetAt(0); HSSFRow titleRow = sheet.createRow(0); for(int i=0;i<titles.length;i++){ sheet.setColumnWidth((short)i, (short)(25 * 256)); HSSFCell cell = titleRow.createCell((short)i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(titles[i])); cell.setCellStyle(titleStyle); }} Object[] strs = new Object[]{"1","小鹏老师",8000}; HSSFRow rows; int rownum = 1; //起始行 for(int i=0;i<1;i++){//list.size() rows = sheet.createRow(rownum ++);// Object obj = list.get(i);// str = list.get(i);// strs = (Object[])obj; for(int j=0;j<strs.length;j++){//第n行的列值 HSSFCell cell_id = rows .getCell((short)j); cell_id = rows.createCell((short)j); if(j == 2){ BigDecimal db = null; try { db = new BigDecimal(strs[j].toString(),java.math.MathContext.UNLIMITED); } catch (Exception e) { System.out.println("NUMBER转换异常："+"-452456465.93"); e.printStackTrace(); } cell_id.setCellValue(db.doubleValue()); HSSFDataFormat hssfDF = wb.createDataFormat(); HSSFCellStyle accountantStyle = wb.createCellStyle(); accountantStyle.setDataFormat(hssfDF.getFormat("_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);(@_)")); //jxl// accountantStyle.setDataFormat(hssfDF.getFormat("_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)")); //poi cell_id.setCellStyle(accountantStyle); }else{ cell_id.setCellType(HSSFCell.CELL_TYPE_STRING); cell_id.setCellValue(new HSSFRichTextString(strs[j].toString())); } } } try {fos = new FileOutputStream(fileDir);try {wb.write(fos);} catch (IOException e) {e.printStackTrace();}try {fis.close();fos.close();} catch (IOException e) {e.printStackTrace();}} catch (FileNotFoundException e) {e.printStackTrace();} finally{try {fis.close();fis = null;fos.close();fos = null;} catch (IOException e) {e.printStackTrace();}}}
5、 优化代码：
虽然我们实现了代码，但其实我们的代码中存在一定的缺陷，再上面的代码中，在我们写入工资一列的时候，我们创建了该列的样式为会计专用。假设这里我们要循环10000次，那么这个样式我们就需要创建10000次，不讨论性能怎么样，这种写法本身就是不合理的，写入到excel中也会报错，尤其是对低版本的excel，容易出现单元格格式丢失的问题，所以在这里我们需要优化我们的代码。
在写入过程中，难免要创建单元格对应的样式，其实这些样式无非就是单元格的格式例如会计专用、整数、日期等，既然这些样式都是比较固定和统一的，那么我们可以在for循环以外提前创建，放入在Map中，在使用的时候直接get出来就可以了。部分代码如下：
wb = new HSSFWorkbook(poiFS);
if(wb !=null){
HSSFDataFormat hssfDF = wb.createDataFormat();
HSSFCellStyle accountStyle = wb.createCellStyle();//会计专用
// accountStyle.setDataFormat(hssfDF.getFormat("_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \"-\"_ ;_ @_ ")); //jxl读取后的会计专用格式
accountStyle.setDataFormat(hssfDF.getFormat("_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"));//poi写入后为会计专用
styleMap.put("accountStyle", accountStyle);
}
另外：上述代码中我们发现当我们回写会计专用的时候，使用的样式字符串分两种，一个是jxl使用的，一个是poi使用的，两者对这类格式的读取和识别略微不同。在使用不同jar的时候建议分开使用。