使用apache的poi自定义格式导出Excel
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
excel生成过程: excel-->sheet-->row-->cell
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建表
HSSFSheet sheet = workbook.createSheet("sheet1");
//设置样式集合（在下面）
Map<String, HSSFCellStyle> styles = addStyle(workbook);
// 每列宽度 (本次导出列数)
for (int i = 0; i < 4 ; i++) {
// 每列宽度
sheet.setColumnWidth(i, 25 * 300);
}
sheet.setColumnWidth(0, 30 * 300);
sheet.setColumnWidth(1, 15 * 300);
sheet.setColumnWidth(2, 23 * 300);
// 创建第一行
HSSFRow row1 = sheet.createRow(0);
// 创建第一行第一列
HSSFCell cellB1 = row1.createCell(0);
// 给第一列赋值
cellB1.setCellValue("产品规格书");
// 设置行高
row1.setHeightInPoints(20);
// 设置样式（居中、颜色、框等等）
cellB1.setCellStyle(styles.get("title"));
// 其他列以此类推
// 当需要合并某一行的一些列时 （起始行，终止行，起始列，终止列）
sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 4));
/**
* 放入图片
*/
URL url = new URL(imageUrl);
// 打开链接
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
// 请求方式
conn.setRequestMethod("GET");
// 请求超时时间
conn.setConnectTimeout(10 * 1000);
BufferedImage bufferImg = null;
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
// InputStream is = this.getClass().getResourceAsStream("/e6ab3c4617327341fdc1dc6eb355c26.jpg");//获取图片。本文放在resources下
InputStream is = conn.getInputStream();
bufferImg = ImageIO.read(is);
// 判断图片类型
String imageFormat = imageUrl.contains(".png") ? "png" : imageUrl.contains(".jpg") ? "jpg" : "jpeg";
ImageIO.write(bufferImg, imageFormat, byteArrayOut);
//画图的顶级管理器，一个sheet只能获取一个
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//anchor主要用于设置图片的属性（暂时不懂前4个参数，，，，起始列，起始行，终止列，终止行）
HSSFClientAnchor anchor = new HSSFClientAnchor(50, 0, 1023, 150, (short) 0, 13, (short) 0, 21);
anchor.setAnchorType(ClientAnchor.AnchorType.byId(3));
//插入图片
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
is.close();
byteArrayOut.close();
// 输出到本地(当前目录)
// String excelName = "Asin产品规格书.xls";
// FileOutputStream out = null;
// try {
// out = new FileOutputStream(excelName);
// workbook.write(out);
// out.flush();
// out.close();
// } catch (Exception e) {
// e.printStackTrace();
// } finally {
// if (out != null)
// try {
// out.close();
// } catch (IOException e) {
// e.printStackTrace();
// }
// out = null;
// }
// 文件流，返给前端
String fileName = new String((productInfo.getProductModel() + "产品规格书").getBytes(), "UTF-8") + ".xls";
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setCharacterEncoding("UTF-8");
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
try {
os.close();
} catch (Exception e) {
e.printStackTrace();
}
样式代码
private Map<String, HSSFCellStyle> addStyle(HSSFWorkbook wb) {
@SuppressWarnings({"unchecked", "rawtypes"})
Map<String, HSSFCellStyle> styles = new HashMap();
//自定义颜色对象
// XSSFColor color = new XSSFColor();
//根据你需要的rgb值获取byte数组
// color.setRGB(intToByteArray(getIntFromColor(198, 224, 180)));
//设置字体
HSSFFont headFont = wb.createFont();
headFont.setFontName("宋体-简");
headFont.setFontHeightInPoints((short) 16);
headFont.setBold(true);
HSSFFont bodyFont = wb.createFont();
bodyFont.setFontName("宋体-简");
bodyFont.setFontHeightInPoints((short) 11);
//标题行样式
HSSFCellStyle style = wb.createCellStyle();
style.setFont(headFont);
/**
*颜色不起作用
**/
// style.setFillForegroundColor((short) 27);
// HSSFPalette palette = wb.getCustomPalette(); //拿到颜色板
// palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 198, (byte) 224, (byte) 180);//定义颜色块
// style.setFillForegroundColor(new XSSFColor(new java.awt.Color(198,224,180),new DefaultIndexedColorMap()).getIndex());
// style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()));
style.setAlignment(HorizontalAlignment.CENTER); // 水平对齐类型 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
style.setBorderBottom(BorderStyle.THIN); //单元格底部边框类型
style.setBorderLeft(BorderStyle.THIN); // 设置用于单元格左边框的边框类型
style.setBorderRight(BorderStyle.THIN); //设置用于单元格右边框的边框类型
style.setBorderTop(BorderStyle.THIN); // 设置用于单元格顶部边框的边框类型
style.setWrapText(false); //自动换行
styles.put("title", style);
//数据头居中样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
styles.put("header_center", style);
//数据行居中样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setAlignment(HorizontalAlignment.CENTER); // 水平对齐类型 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
style.setBorderBottom(BorderStyle.THIN); //单元格底部边框类型
style.setBorderLeft(BorderStyle.THIN); // 设置用于单元格左边框的边框类型
style.setBorderRight(BorderStyle.THIN); //设置用于单元格右边框的边框类型
style.setBorderTop(BorderStyle.THIN); // 设置用于单元格顶部边框的边框类型
style.setWrapText(false); //自动换行
styles.put("data_center", style);
//数据行居中底色样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
styles.put("data_center_color", style);
//数据行居中底色样式2
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
styles.put("data_center_color1", style);
//数据行居左样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
styles.put("data_left", style);
//数据行居右样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
styles.put("data_right", style);
//无边框样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
styles.put("data_noborder", style);
//无底边框样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
styles.put("data_bottom", style);
return styles;
}