下文不懂请留言,回复较快
依赖
<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>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
代码
/**
* LSPS销售推移 excel
*
* @param response
* @param shopId
* @param year
*/
@Override
public void sellTuiYi(HttpServletResponse response, Long shopId, String year) {
String[] strName = {"日常保养", "贴膜/内饰", "ECU升级", "包围", "轮圈", "其他底盘件等", "美容镀膜", "电子产品", "其他产品", "其他工时费", "车辆买卖"};
InputStream inputStream1 = null;
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
//边框(这个是我自己封装的工具类,在下面给出)
HSSFCellStyle cellStyle1 = BorderUtil.createCellStyle(wb);
//第一行
HSSFRow row = sheet.createRow(0);//创建行
HSSFCell cell = row.createCell(1);//创建列
cell.setCellValue("■LSPS销售推移(" + year + ")");//设值
sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 25));//合并
//第二行
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell1 = row1.createCell(0);
row1.createCell(1).setCellStyle(cellStyle1);
row1.createCell(2).setCellStyle(cellStyle1);
cell1.setCellValue("项目");
cell1.setCellStyle(cellStyle1);
cell1.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
for (int i = 1; i <= 12; i++) {
HSSFCell cell2 = row1.createCell(1 + (i * 2));
row1.createCell(2 + (i * 2)).setCellStyle(cellStyle1);
cell2.setCellValue(i + "月");
cell2.setCellStyle(cellStyle1);
cell2.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 1 + (i * 2), 2 + (i * 2)));
}
Map map = selectPartsUseSalePay(year, shopId);
for (int i = 0; i < 11; i++) {
String numAndMoney[] = {"数", "金"};
int a = 2 + (i * 2);
HSSFRow row2 = sheet.createRow(2 + (i * 2));
HSSFCell cell2 = row2.createCell(0);
row2.createCell(1).setCellStyle(cellStyle1);
HSSFCell cell3 = row2.createCell(2);
cell3.setCellValue(numAndMoney[0]);
cell3.setCellStyle(cellStyle1);
cell2.setCellValue(strName[i]);
cell2.setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(a, a + 1, 0, 1));
HSSFRow row3 = sheet.createRow(3 + (i * 2));
row3.createCell(0).setCellStyle(cellStyle1);
row3.createCell(1).setCellStyle(cellStyle1);
HSSFCell cell4 = row3.createCell(2);
cell4.setCellValue(numAndMoney[1]);
cell4.setCellStyle(cellStyle1);设置样式(每个样式不同,看掉的是那个方法)
Map<String, SaleQuery> saleQueryMap = (Map<String, SaleQuery>) map.get(i + 1);
if (saleQueryMap != null) {
String name[] = {"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"};
for (int j = 0; j < 12; j++) {
int c = 3 + j * 2;
SaleQuery saleQuery = saleQueryMap.get(name[j]);
if (saleQuery != null) {
HSSFCell cell5 = row2.createCell(3 + j * 2);
cell5.setCellValue(saleQuery.getNum());
cell5.setCellStyle(cellStyle1);
row2.createCell(4 + j * 2).setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(a, a, c, c + 1));
HSSFCell cell6 = row3.createCell(3 + j * 2);
cell6.setCellValue(String.valueOf(saleQuery.getSale()));
cell6.setCellStyle(cellStyle1);
row3.createCell(4 + j * 2).setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(a + 1, a + 1, c, c + 1));
} else {
row2.createCell(3 + j * 2).setCellStyle(cellStyle1);
row2.createCell(4 + j * 2).setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(a, a, c, c + 1));
row3.createCell(3 + j * 2).setCellStyle(cellStyle1);
row3.createCell(4 + j * 2).setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(a + 1, a + 1, c, c + 1));
}
}
} else {
for (int j = 0; j < 12; j++) {
int c = 3 + j * 2;
row2.createCell(3 + j * 2).setCellStyle(cellStyle1);
row2.createCell(4 + j * 2).setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(a, a, c, c + 1));
row3.createCell(3 + j * 2).setCellStyle(cellStyle1);
row3.createCell(4 + j * 2).setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(a + 1, a + 1, c, c + 1));
}
}
}
String fileName = "LSPS销售推移.xls";
// xls格式:response.setContentType("application/vnd.ms-excel;charset=GBK");
// xlsx格式:response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GBK");
response.setContentType("applicatin/ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + new String(fileName.getBytes("gb2312"), "iso-8859-1"));
wb.write(response.getOutputStream());
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
工具类
package com.totyu.franchise.store.utils;
import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
public class BorderUtil {
/**
* 设置单元格的边框
* @param workbook
* @return
*/
public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook){
HSSFCellStyle style = workbook.createCellStyle();
// style .setBorderBottom(HSSFBorderFormatting.BORDER_THICK);边框加粗
//设置上下左右四个边框宽度
style.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
style.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
style.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
style.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
style.setWrapText(true);//设置自动换行
//设置上下左右四个边框颜色
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
return style;
}
/**
* 设置单元格的边框并设置为百分比形式
* @param workbook
* @return
*/
public static HSSFCellStyle createDataFormat(HSSFWorkbook workbook){
HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));//设置显示的值为百分比
//设置上下左右四个边框宽度
style.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
style.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
style.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
style.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
//设置上下左右四个边框颜色
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
return style;
}
}
其他
//设置指定列的宽度
sheet.setColumnWidth(0, 5600);
//设置字体,大小,颜色
HSSFFont font = wb.createFont();
font.setBold(true);
font.setColor((short) 0);//这个之可变,具体什值是什么颜色没测过,0是黑色
font.setFontHeightInPoints((short) 24);//字体大小
font.setFontName("宋体");//什么字体
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
HSSFRow row2= sheet.getRow(7);//获取第几行,这样可防止覆盖
可能出现的问题(设置了百分比没用的格式)
解决方法:
设置您的数据作为数字(浮点),而不是文字。
指定单元格的格式为百分比。
是这样的:
cell.setCellValue(0.123); //设定值数类型的数据
CellStyle风格= workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat()的getFormat("0.000%"));
cell.setCellStyle(样式);