使用poi-ooxml-3.17.jar
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
@Override
public void createExcel(String name, String time, String id, Map<String, Object> model, Map<String, Object> sdModel) throws Exception {
String filePath = PathUtil.getClasspath() + Const.REVENUESTATISTICS + id + name + time + ".xlsx";
XSSFWorkbook workbook = new XSSFWorkbook();
File fileXlsxPath = new File(filePath);
BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
if("实验室收入统计".equals(name)) {
workbook.createSheet("临床检验收入统计");
workbook.createSheet("山东大学齐鲁医院(外送检测)收入统计");
}else {
workbook.createSheet("Sheet1");
}
workbook.write(outputStream);
outputStream.close();
//这样表示SXSSFWorkbook只会保留100条数据在内存中,其它的数据都会写到磁盘里,这样的话占用的内存就会很少
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);
//获取第一个Sheet页
SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
CellStyle cellstyle = sxssfWorkbook.createCellStyle();
cellstyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
Cell cell;
Row row;
List<String> titles = (List<String>) model.get("titles");
row = sheet.createRow(0);
for(int i = 0; i < titles.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(titles.get(i));
cell.setCellStyle(cellstyle);
}
List<PageData> varList = (List<PageData>) model.get("varList");
for(int i = 0;i < varList.size();i++) {
row = sheet.createRow(i+1);
PageData vpd = varList.get(i);
for(int j = 0;j < titles.size();j++) {
cell = row.createCell(j);
cell.setCellValue(vpd.getString("var"+(j+1)));
cell.setCellStyle(cellstyle);
}
}
//自动调整列宽
sheet.trackAllColumnsForAutoSizing();
for(int i = 0; i < titles.size(); i++) {
sheet.autoSizeColumn(i);
//手动调整列宽,解决中文不能自适应问题
//单元格单行最长支持255*256宽度(每个单元格样式已经设置自动换行,超出即换行)
//设置最低列宽度,列宽约六个中文字符
int width = Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(i) * 17 / 10));
sheet.setColumnWidth(i, width);
}
if("实验室收入统计".equals(name)) {
sheet = sxssfWorkbook.getSheetAt(1);
row = sheet.createRow(0);
cell = null;
cellstyle = sxssfWorkbook.createCellStyle();
cellstyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
titles = (List<String>) sdModel.get("titles");
for(int i = 0; i < titles.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(titles.get(i));
cell.setCellStyle(cellstyle);
}
varList = (List<PageData>) sdModel.get("varList");
for(int i = 0;i < varList.size();i++) {
row = sheet.createRow(i+1);
PageData vpd = varList.get(i);
for(int j = 0;j < titles.size();j++) {
cell = row.createCell(j);
cell.setCellValue(vpd.getString("var"+(j+1)));
cell.setCellStyle(cellstyle);
}
}
//自动调整列宽
sheet.trackAllColumnsForAutoSizing();
for(int i = 0; i < titles.size(); i++) {
sheet.autoSizeColumn(i);
//手动调整列宽,解决中文不能自适应问题
//单元格单行最长支持255*256宽度(每个单元格样式已经设置自动换行,超出即换行)
//设置最低列宽度,列宽约六个中文字符
int width = Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(i) * 17 / 10));
sheet.setColumnWidth(i, width);
}
}
outputStream = new BufferedOutputStream(new FileOutputStream(filePath));
sxssfWorkbook.write(outputStream);
outputStream.flush();
sxssfWorkbook.dispose();//释放workbook所占用的所有windows资源
outputStream.close();
}