POI读写海量Excel（详细解读）(3)

if (fileName.endsWith(EXCEL03_EXTENSION)){ Excel2003Reader excel03 = new Excel2003Reader(); excel03.setRowReader(reader); excel03.process(fileName); // 处理excel2007文件

} else if (fileName.endsWith(EXCEL07_EXTENSION)){ Excel2007Reader excel07 = new Excel2007Reader(); excel07.setRowReader(reader); excel07.process(fileName); } else {

throw new Exception(\文件格式错误，fileName的扩展名只能是xls或xlsx。\ } } }

public interface IRowReader { /**业务逻辑实现方法 * @param sheetIndex * @param curRow * @param rowlist */ public void getRows(int sheetIndex,int curRow, List rowlist); }

public class RowReader implements IRowReader{ /* 业务逻辑实现方法 * @see com.eprosun.util.excel.IRowReader#getRows(int, int, java.util.List) */ public void getRows(int sheetIndex, int curRow, List rowlist) { // TODO Auto-generated method stub System.out.print(curRow+\ for (int i = 0; i < rowlist.size(); i++) { System.out.print(rowlist.get(i) + \ } System.out.println(); } }

public class Main {

public static void main(String[] args) throws Exception { IRowReader reader = new RowReader(); //ExcelReaderUtil.readExcel(reader, \ ExcelReaderUtil.readExcel(reader, \ } }

public class Excel2003Writer { /** * @param args */ public static void main(String[] args) { try{ System.out.println(\开始写入excel2003....\ writeExcel(\

} /**

System.out.println(\写完xcel2003\} catch (IOException e) { }

* 写入excel并填充内容,一个sheet只能写65536行以下，超出会报异常，写入时建议使用AbstractExcel2007Writer * @param fileName * @throws IOException */ public static void writeExcel(String fileName) throws IOException{

// 创建excel2003对象

Workbook wb = new HSSFWorkbook();

// 设置文件放置路径和文件名

FileOutputStream fileOut = new FileOutputStream(fileName); // 创建新的表单

Sheet sheet = wb.createSheet(\ // 创建新行

for(int i=0;i<20000;i++){ Row row = sheet.createRow(i); // 创建单元格 Cell cell = row.createCell(0);

// 设置单元格值 cell.setCellValue(1); row.createCell(1).setCellValue(1+i); row.createCell(2).setCellValue(true); row.createCell(3).setCellValue(0.43d); row.createCell(4).setCellValue('d'); row.createCell(5).setCellValue(\ row.createCell(6).setCellValue(\第七列\ row.createCell(7).setCellValue(\第八列\ } wb.write(fileOut); fileOut.close(); } } /**

* 抽象excel2007读入器，先构建.xlsx一张模板，改写模板中的sheet.xml,使用这种方法 * 写入.xlsx文件，不需要太大的内存 * */

public abstract class AbstractExcel2007Writer { private SpreadsheetWriter sw; /**

* 写入电子表格的主要流程 * @param fileName * @throws Exception */

public void process(String fileName) throws Exception{

// 建立工作簿和电子表格对象

XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(\

// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml String sheetRef = sheet.getPackagePart().getPartName().getName(); // 保存模板

FileOutputStream os = new FileOutputStream(\wb.write(os); os.close();

// 生成xml文件

} /**

File tmp = File.createTempFile(\Writer fw = new FileWriter(tmp); sw = new SpreadsheetWriter(fw); generate(); fw.close();

// 使用产生的数据替换模板

File templateFile = new File(\

FileOutputStream out = new FileOutputStream(fileName); substitute(templateFile, tmp, sheetRef.substring(1), out); out.close();

//删除文件之前调用一下垃圾回收器，否则无法删除模板文件 System.gc();

// 删除临时模板文件

if (templateFile.isFile()&&templateFile.exists()){ templateFile.delete(); }

* 类使用者应该使用此方法进行写操作 * @throws Exception */

public abstract void generate() throws Exception; public void beginSheet() throws IOException { sw.beginSheet(); }

public void insertRow(int rowNum) throws IOException { sw.insertRow(rowNum); }

public void createCell(int columnIndex, String value) throws IOException { sw.createCell(columnIndex, value, -1); }

public void createCell(int columnIndex, double value) throws IOException { sw.createCell(columnIndex, value, -1); }

public void endRow() throws IOException { sw.endRow(); }

public void endSheet() throws IOException { sw.endSheet(); }

/** *

* @param zipfile the template file

* @param tmpfile the XML file with the sheet data

* @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml * @param out the stream to write the result to */

private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException { ZipFile zip = new ZipFile(zipfile); ZipOutputStream zos = new ZipOutputStream(out); }

@SuppressWarnings(\

Enumeration en = (Enumeration) zip.entries(); while (en.hasMoreElements()) { ZipEntry ze = en.nextElement(); if (!ze.getName().equals(entry)) { zos.putNextEntry(new ZipEntry(ze.getName())); InputStream is = zip.getInputStream(ze); copyStream(is, zos); is.close(); } }

zos.putNextEntry(new ZipEntry(entry));

InputStream is = new FileInputStream(tmpfile); copyStream(is, zos); is.close(); zos.close();

private static void copyStream(InputStream in, OutputStream out) throws IOException { byte[] chunk = new byte[1024]; int count; while ((count = in.read(chunk)) >= 0) { out.write(chunk, 0, count); } }