POI操作Excel
POI导出Excel2007比导出Excel2003要慢.
当时项目中用的JAR包:
Excel2003:poi-2.5.1.jar
Excel2007:
poi-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-ooxml-schemas-3.7-20101029.jar
xmlbeans-2.3.0.jar
dom4j-1.6.1.jar
import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import org.apache.poi.hssf.usermodel.HSSFCell; 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.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class PoiExcel { // POI导出Excel2003 public static void exportExcel2003() { try { // 创建Excel文件 FileOutputStream fileOut = new FileOutputStream("C:\\Users\\YY\\Desktop\\table.xls"); // 创建工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作表 HSSFSheet sheet = workbook.createSheet(); // 设置工作表名称,参数1为工作表下标,参数2为工作表名称,参数3为编码 workbook.setSheetName(0, "第一页", HSSFWorkbook.ENCODING_UTF_16); // 创建行 HSSFRow row = null; // 循环创建单元格(Excel2003最多能放65536行数据) for (int i = 0; i < 10; i++) { row = sheet.createRow(i); for (int j = 0; j < 5; j++) { row.createCell((short) j).setCellValue("Test_" + (j + 1) + "_" + (i + 1)); } } // 写入数据并关闭流 workbook.write(fileOut); fileOut.flush(); fileOut.close(); System.out.println("导出Excel2003完成"); } catch (FileNotFoundException e) { System.out.println(e); } catch (IOException e) { System.out.println(e); } } // POI读取Excel2003 @SuppressWarnings("static-access") public static void readExcel2003() { try { // 获得文件 InputStream is = new FileInputStream("C:\\Users\\YY\\Desktop\\table.xls"); HSSFWorkbook workbook = new HSSFWorkbook(is); // 获得工作表数量 int sheetsNum = workbook.getNumberOfSheets(); // 循环工作表 for (int i = 0; i < sheetsNum; i++) { // 获得工作表并判断是否为空 HSSFSheet sheet = workbook.getSheetAt(i); if (sheet == null) { continue; } // 获得行数量 int rowNum = sheet.getLastRowNum(); // 循环行 for (int j = 0; j <= rowNum; j++) { // 获得行并判断是否为空 HSSFRow row = sheet.getRow(j); if (row == null) { continue; } // 获得列数量 int cellNum = row.getLastCellNum(); // 输出变量 StringBuffer sb = new StringBuffer(); // 循环列 for (int k = 0; k <= cellNum; k++) { // 获得单元格并判断是否为空 HSSFCell cell = row.getCell((short) k); if (cell == null) { continue; } if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) { // 返回布尔类型的值 sb.append(String.valueOf(cell.getBooleanCellValue())); } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { // 返回数值类型的值 sb.append(String.valueOf(cell.getNumericCellValue())); } else { // 返回字符串类型的值 sb.append(String.valueOf(cell.getStringCellValue())); } sb.append(" "); } // 输入内容 System.out.println(sb.toString()); } } System.out.println("读取Excel2003完成"); } catch (FileNotFoundException e) { System.out.println(e); } catch (IOException e) { System.out.println(e); } } // POI修改Excel2003 public static void updateExcel2003() { try { // 文件地址 String excelPath = "C:\\Users\\YY\\Desktop\\table.xls"; // 获得文件 InputStream is = new FileInputStream(excelPath); HSSFWorkbook workbook = new HSSFWorkbook(is); // 获得工作表数量 int sheetsNum = workbook.getNumberOfSheets(); // 循环工作表 for (int i = 0; i < sheetsNum; i++) { // 获得工作表并判断是否为空 HSSFSheet sheet = workbook.getSheetAt(i); if (sheet == null) { continue; } // 获得行数量 int rowNum = sheet.getLastRowNum(); // 循环行 for (int j = 0; j < rowNum; j++) { // 获得行并判断是否为空 HSSFRow row = sheet.getRow(j); if (row == null) { continue; } // 获得列数量 int cellNum = row.getLastCellNum(); // 循环列 for (int k = 0; k < cellNum; k++) { // 获得单元格并判断是否为空 HSSFCell cell = row.getCell((short) k); if (cell == null) { continue; } // 如果单元格的值为"Test1_1_1",则修改为"Test_111" if (cell.getStringCellValue().equals("Test_1_1")) { cell.setCellValue("Test11"); } // 修改第3行第4列单元格的值 if (j == 2 && k == 3) { cell.setCellValue("2-3"); } } } } // 写入数据并关闭流 FileOutputStream out = new FileOutputStream(excelPath); workbook.write(out); out.flush(); out.close(); System.out.println("修改Excel2003完成"); } catch (FileNotFoundException e) { System.out.println(e); } catch (IOException e) { System.out.println(e); } } // POI导出Excel2007 public static void exportExcel2007() { try { // 创建Excel文件 FileOutputStream fileOut = new FileOutputStream("C:\\Users\\YY\\Desktop\\table.xlsx"); // 创建工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 创建工作表 XSSFSheet sheet = workbook.createSheet("第一页"); // 创建行 XSSFRow row = null; // 循环创建单元格(Excel2007最多能放1048576行数据) for (int i = 0; i < 10; i++) { row = sheet.createRow(i); for (int j = 0; j < 5; j++) { row.createCell(j).setCellValue("Test_" + (j + 1) + "_" + (i + 1)); } } // 写入数据并关闭流 workbook.write(fileOut); fileOut.flush(); fileOut.close(); System.out.println("导出Excel2007完成"); } catch (FileNotFoundException e) { System.out.println(e); } catch (IOException e) { System.out.println(e); } } public static void main(String[] args) { exportExcel2003(); updateExcel2003(); readExcel2003(); exportExcel2007(); } }