SXSSFWorkbook导出Excel
xmlbeans-3.1.0.jar
poi-ooxml-schemas-3.8.jar
//手写
public InputStream getTerminalunitFile() throws ParsePropertyException,IOException {
String filename = "terminalunit_" + dateNowStr + ".xlsx";
String rootPath = ServletActionContext.getServletContext().getRealPath("/");
// 生成文件的路径
String file = rootPath + "template/" + filename;
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
Sheet sheet = sxssfWorkbook.createSheet("Sheet1");
XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
// 创建第一行,作为header表头
Row header = sheet.createRow(0);
for (int cellnum = 0; cellnum < 16; cellnum++) {
Cell cell = header.createCell(cellnum);
cell.setCellStyle(xssfCellStyleHeader);
// 判断单元格
if (cellnum == 0) {
cell.setCellValue("售货机编号");
sheet.setColumnWidth(cellnum, (int) 4738);
} else if (cellnum == 1) {
cell.setCellValue("售货机名称");
sheet.setColumnWidth(cellnum, (int) 4738);
} else if (cellnum == 2) {
cell.setCellValue("资产编号");
sheet.setColumnWidth(cellnum, (int) 4738);
} else if (cellnum == 3) {
cell.setCellValue("工控机号");
sheet.setColumnWidth(cellnum, (int) 4325);
} else if (cellnum == 4) {
cell.setCellValue("工控机状态");
sheet.setColumnWidth(cellnum, (int) 4325);
} else if (cellnum == 5) {
cell.setCellValue("应用版本");
sheet.setColumnWidth(cellnum, (int) 4325);
} else if (cellnum == 6) {
cell.setCellValue("点位名称");
sheet.setColumnWidth(cellnum, (int) 4738);
} else if (cellnum == 7) {
cell.setCellValue("点位地址");
sheet.setColumnWidth(cellnum, (int) 7463);
} else if (cellnum == 8) {
cell.setCellValue("大渠道");
sheet.setColumnWidth(cellnum, (int) 4613);
} else if (cellnum == 9) {
cell.setCellValue("小渠道");
sheet.setColumnWidth(cellnum, (int) 4738);
} else if(cellnum == 10){
cell.setCellValue("组织名称");
sheet.setColumnWidth(cellnum, (int) 4838);
}else if(cellnum == 11){
cell.setCellValue("运营商名称");
sheet.setColumnWidth(cellnum, (int) 5813);
}else if(cellnum == 12){
cell.setCellValue("监控状态");
sheet.setColumnWidth(cellnum, (int) 4000);
}else if(cellnum == 13){
cell.setCellValue("签到状态");
sheet.setColumnWidth(cellnum, (int) 4000);
}else if(cellnum == 14){
cell.setCellValue("应用中方案");
sheet.setColumnWidth(cellnum, (int) 5813);
}else if(cellnum == 15){
cell.setCellValue("最新方案");
sheet.setColumnWidth(cellnum, (int) 5438);
}
}
// 封装数据
for (int j = 0; j < list.size(); j++) {
// 行 map保存的行数据
Row sxRow = sheet.createRow(j+1);
Map<String, Object> dataMap = list.get(j);
// 提出来的单元格数据
for (int cellnum = 0; cellnum < 16; cellnum++) {
Cell cell = sxRow.createCell(cellnum);
if(cellnum == 0){
if (dataMap.get("corpTerminalIdentifier") != null) {
cell.setCellValue(dataMap.get("corpTerminalIdentifier").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 1){
if (dataMap.get("name") != null) {
cell.setCellValue(dataMap.get("name").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 2){
if (dataMap.get("terminalIdentifier") != null) {
cell.setCellValue(dataMap.get("terminalIdentifier").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 3){
if (dataMap.get("terminalNum") != null) {
cell.setCellValue(dataMap.get("terminalNum").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 4){
if (dataMap.get("Terminalstatus") != null) {
cell.setCellValue(dataMap.get("Terminalstatus").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 5){
if (dataMap.get("appVersion") != null) {
cell.setCellValue(dataMap.get("appVersion").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 6){
if (dataMap.get("locationTitle") != null) {
cell.setCellValue(dataMap.get("locationTitle").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 7){
if (dataMap.get("locationAddress") != null) {
cell.setCellValue(dataMap.get("locationAddress").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 8){
if (dataMap.get("topRouteName") != null) {
cell.setCellValue(dataMap.get("topRouteName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 9){
if (dataMap.get("subRouteName") != null) {
cell.setCellValue(dataMap.get("subRouteName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 10){
if (dataMap.get("organizationName") != null) {
cell.setCellValue(dataMap.get("organizationName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 11){
if (dataMap.get("corpName") != null) {
cell.setCellValue(dataMap.get("corpName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 12){
if (dataMap.get("online") != null) {
cell.setCellValue(dataMap.get("online").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 13){
if (dataMap.get("signflag") != null) {
cell.setCellValue(dataMap.get("signflag").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 14){
if (dataMap.get("nowPlanName") != null) {
cell.setCellValue(dataMap.get("nowPlanName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 15){
if (dataMap.get("newPlanName") != null) {
cell.setCellValue(dataMap.get("newPlanName").toString());
}else {
cell.setCellValue("");
}
}
}
}
try {
OutputStream op = new FileOutputStream(file);
sxssfWorkbook.write(op);
op.close();
} catch (Exception e) {
logger.error("error:" + e.getMessage(), e);
}
FileInputStream stream = new FileInputStream(new File(file));
return stream;
}
/**
* 获取并设置header样式
*/
private XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
Font font = sxssfWorkbook.createFont();
font.setFontName("宋体");
// 字体大小
font.setFontHeightInPoints((short) 8);
// 字体粗细
font.setBoldweight((short) 20);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 将字体应用到样式上面
xssfCellStyle.setFont(font);
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
//xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 前景颜色
xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
return xssfCellStyle;
}
利用模板
//利用模板
public InputStream getTerminalunitFile() throws ParsePropertyException,IOException {
String filename = "terminalunit_" + dateNowStr + ".xlsx";
String rootPath = ServletActionContext.getServletContext().getRealPath("/");
// 生成文件的路径
String file = rootPath + "template/" + filename;
// 模板路径
String templateFileName = rootPath + "/template/terminalunit_template_new.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(templateFileName);
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook,100);
SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
// 封装数据
for (int j = 0; j < list.size(); j++) {
// 行 map保存的行数据
Row sxRow = sheet.createRow(j+1);
Map<String, Object> dataMap = list.get(j);
// 提出来的单元格数据
for (int cellnum = 0; cellnum < 16; cellnum++) {
Cell cell = sxRow.createCell(cellnum);
if(cellnum == 0){
if (dataMap.get("corpTerminalIdentifier") != null) {
cell.setCellValue(dataMap.get("corpTerminalIdentifier").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 1){
if (dataMap.get("name") != null) {
cell.setCellValue(dataMap.get("name").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 2){
if (dataMap.get("terminalIdentifier") != null) {
cell.setCellValue(dataMap.get("terminalIdentifier").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 3){
if (dataMap.get("terminalNum") != null) {
cell.setCellValue(dataMap.get("terminalNum").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 4){
if (dataMap.get("Terminalstatus") != null) {
cell.setCellValue(dataMap.get("Terminalstatus").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 5){
if (dataMap.get("appVersion") != null) {
cell.setCellValue(dataMap.get("appVersion").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 6){
if (dataMap.get("locationTitle") != null) {
cell.setCellValue(dataMap.get("locationTitle").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 7){
if (dataMap.get("locationAddress") != null) {
cell.setCellValue(dataMap.get("locationAddress").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 8){
if (dataMap.get("topRouteName") != null) {
cell.setCellValue(dataMap.get("topRouteName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 9){
if (dataMap.get("subRouteName") != null) {
cell.setCellValue(dataMap.get("subRouteName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 10){
if (dataMap.get("organizationName") != null) {
cell.setCellValue(dataMap.get("organizationName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 11){
if (dataMap.get("corpName") != null) {
cell.setCellValue(dataMap.get("corpName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 12){
if (dataMap.get("online") != null) {
cell.setCellValue(dataMap.get("online").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 13){
if (dataMap.get("signflag") != null) {
cell.setCellValue(dataMap.get("signflag").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 14){
if (dataMap.get("nowPlanName") != null) {
cell.setCellValue(dataMap.get("nowPlanName").toString());
}else {
cell.setCellValue("");
}
}else if(cellnum == 15){
if (dataMap.get("newPlanName") != null) {
cell.setCellValue(dataMap.get("newPlanName").toString());
}else {
cell.setCellValue("");
}
}
}
}
try {
OutputStream op = new FileOutputStream(file);
sxssfWorkbook.write(op);
op.close();
} catch (Exception e) {
logger.error("error:" + e.getMessage(), e);
}
FileInputStream stream = new FileInputStream(new File(file));
return stream;
}
/**
* 获取并设置header样式
*/
private XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
Font font = sxssfWorkbook.createFont();
font.setFontName("宋体");
// 字体大小
font.setFontHeightInPoints((short) 8);
// 字体粗细
font.setBoldweight((short) 20);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 将字体应用到样式上面
xssfCellStyle.setFont(font);
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
//xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 前景颜色
xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
return xssfCellStyle;
}