maven 依赖 （版本必须一致，否则使用SXSSFworkbook 时程序会报错）
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
HSSFworkbook,XSSFworkbook,SXSSFworkbook 三者 区别
HSSFworkbook：操作Excel2003版本，扩展名为xls
XSSFworkbook：操作Excel2007版本，扩展名为xlsx
SXSSFworkbook ：用于大数据量导出，当数据量超过 65536后 程序 会报错：Invalid row number (65536) outside allowable range (0..65535)
例子1：简单导出Excel
@Test
public void test1() throws IOException {
// 读取文件
POIFSFileSystem fs = new POIFSFileSystem(Thread.currentThread().getContextClassLoader().getResourceAsStream("test.xls"));
// 创建一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook(fs);
// 获取第一个sheet
HSSFSheet sheet = workbook.getSheetAt(0);
System.out.println(sheet.getSheetName());
// 获取第一行
HSSFRow row = sheet.getRow(0);
// 获取第一行第一列
HSSFCell cell = row.getCell(0);
System.out.println(cell.getStringCellValue()); // 创建一行
HSSFRow row1 = sheet.createRow(1);
// 创建单元格
HSSFCell cell1 = row1.createCell(0);
// 单元格赋值
cell1.setCellValue("我是程序创建的内容");
System.out.println(cell1.getStringCellValue()); // 创建输出流
FileOutputStream os = new FileOutputStream(new File("D:\\8888.xls"));
// 输出文件
workbook.write(os);
}
例子2：导出查询数据
@Test
public void test2() {
// 模拟导出数据
Object[] obj = new Object[]{"哈哈", "呵呵", "哼哼"};
List<Object[]> list = new ArrayList<Object[]>();
// HSSFWorkbook 只支持2003版本及以下版本Excel 且容量最大为65536
for (int i = 0; i < 65536; i++) {
list.add(obj);
} export("test.xls", list, 2);
} /**
* poi 导出
* @param fileName
* @param objs
* @param rowIndex
*/
private void export(String fileName, List<Object[]> objs, int rowIndex) {
POIFSFileSystem fs = null;
FileOutputStream os = null;
try {
fs = new POIFSFileSystem(Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName));
} catch (IOException e) {
e.printStackTrace();
}
// 创建一个工作簿
try {
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFCellStyle style = setCellStyle(workbook);
// 获取一个sheet页
HSSFSheet sheet = workbook.getSheetAt(0); for (int i = rowIndex - 1; i < objs.size(); i++) {
// 创建行
HSSFRow row = sheet.createRow(i); // 创建列
for (int j = 0; j < objs.get(i).length; j++) {
HSSFCell cell = row.createCell(j);
// 设置单元格样式
cell.setCellStyle(style);
cell.setCellValue(objs.get(i)[j].toString());
}
}
// 创建输出流
os = new FileOutputStream(new File("D:\\8888.xls"));
// 输出文件
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
} /**
* 设置样式
* @param workbook
*/
private HSSFCellStyle setCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
// 字号
font.setFontHeightInPoints((short) 12);
style.setFont(font);
// 左右居中 上下居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
例子3：大数据量导出
/**
* 大数据量导出
* @throws IOException
*/
@Test
public void text2() throws IOException { XSSFWorkbook xssfWorkbook = new XSSFWorkbook(Thread.currentThread().getContextClassLoader().getResourceAsStream("bigdata.xlsx"));
SXSSFWorkbook wb = new SXSSFWorkbook(xssfWorkbook, 100); Sheet sh = wb.getSheetAt(0);
for(int rownum = 1; rownum < 75537; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
} } // // Rows with rownum < 900 are flushed and not accessible
// for(int rownum = 0; rownum < 900; rownum++){
// Assert.assertNull(sh.getRow(rownum));
// }
//
// // ther last 100 rows are still in memory
// for(int rownum = 900; rownum < 1000; rownum++){
// Assert.assertNotNull(sh.getRow(rownum));
// } FileOutputStream out = new FileOutputStream("D:\\sxssf.xlsx");
wb.write(out);
out.close(); // dispose of temporary files backing this workbook on disk
wb.dispose();
}