1.初始化excel及调用
//1.定义个一个workBook
public void exportOrder(HttpServletRequest request, HttpServletResponse response, OutputStream output) {
String packIds = request.getParameter("packIds");
HSSFWorkbook wb = null;
Date date = new Date();
String filename = DateUtil.date2Str(date, "yyyyMMddHHmmss");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
HSSFSheet sheet1 = null;
HSSFCell tempCell = null;
HSSFRow tempRow = null;
HSSFCellStyle cellStyle = null;
HSSFCellStyle cellStyle2 = null;
try {
wb = new HSSFWorkbook();
//单元格风格
cellStyle = wb.createCellStyle();
cellStyle2 = wb.createCellStyle();
//字体
HSSFFont font = wb.createFont();
HSSFFont font2 = wb.createFont();
font.setFontHeightInPoints((short) 20);
font.setFontName("宋体");
font2.setFontName("宋体");
//将字体加入风格
cellStyle.setFont(font);
cellStyle2.setFont(font2);
//设置单元格居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//创建表单并为表单命名
sheet1 = wb.createSheet("订单信息表");
//设置自适应列宽
for (int i = 0; i < 17; i++) {
sheet1.setColumnWidth(i, 25 * 256);
}
sheet1 = setExcelHead(sheet1, tempRow, tempCell, cellStyle, cellStyle2);
if (packIds != null) {
String[] packId = packIds.split(",");
//定义起始行
int rowIndex = 2;
for (String pack : packId) {
OrderPackEntity orderPackEntity = storeOrderService.get(OrderPackEntity.class, pack);
String orderId = orderPackEntity.getOrderId();
OrderEntity orderEntity = orderService.get(OrderEntity.class, orderId);
String recievePhone = orderEntity.getRecievePhone();
List<MailAddEntity> mailList = mailAddService.getListByPhone(recievePhone, "1");
MailAddEntity mailAddEntity = mailList.get(0);
List<OrderDetailEntity> orderDetailList = orderDetailService.getListByPackId(orderPackEntity.getId());
for (OrderDetailEntity orderDetailEntity : orderDetailList) {
sheet1 = setRowData(orderEntity, mailAddEntity, orderDetailEntity, orderPackEntity, cellStyle2, sheet1, rowIndex);
rowIndex++;
}
}
//返回页面
wb.write(output);
}
} catch (Exception e) {
e.printStackTrace();
}
}
2.设置excel公共的表头模板
/* 设置导出订单excel的表头
*
* @param sheet
* @param tempRow
* @param tempCell
* @param cellStyle
* @param cellStyle2
*/
public HSSFSheet setExcelHead (HSSFSheet sheet, HSSFRow tempRow, HSSFCell tempCell, HSSFCellStyle
cellStyle, HSSFCellStyle cellStyle2){
//创建行
tempRow = sheet.createRow(0);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
//创建某行对应的单元格
tempCell = tempRow.createCell(0);
//为单元格附值
tempCell.setCellValue("序号");
//设置单元格样式
tempCell.setCellStyle(cellStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
//创建某行对应的单元格
tempCell = tempRow.createCell(1);
//为单元格附值
tempCell.setCellValue("订单号");
//设置单元格样式
tempCell.setCellStyle(cellStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
//创建某行对应的单元格
tempCell = tempRow.createCell(2);
//为单元格附值
tempCell.setCellValue("快递单号");
//设置单元格样式
tempCell.setCellStyle(cellStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
//创建某行对应的单元格
tempCell = tempRow.createCell(3);
//为单元格附值
tempCell.setCellValue("会员名");
//设置单元格样式
tempCell.setCellStyle(cellStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 5));
//创建某行对应的单元格
tempCell = tempRow.createCell(4);
//为单元格附值
tempCell.setCellValue("寄件人信息");
//设置单元格样式
tempCell.setCellStyle(cellStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 12));
//创建某行对应的单元格
tempCell = tempRow.createCell(6);
//为单元格附值
tempCell.setCellValue("收件人信息");
//设置单元格样式
tempCell.setCellStyle(cellStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 13, 16));
//创建某行对应的单元格
tempCell = tempRow.createCell(13);
//为单元格附值
tempCell.setCellValue("货物信息");
tempCell.setCellStyle(cellStyle);
//创建第二行
tempRow = sheet.createRow(1);
tempCell = tempRow.createCell(4);
tempCell.setCellValue("寄件人");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(5);
tempCell.setCellValue("寄件人电话");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(6);
tempCell.setCellValue("收件人");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(7);
tempCell.setCellValue("收件人电话");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(8);
tempCell.setCellValue("身份证");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(9);
tempCell.setCellValue("省");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(10);
tempCell.setCellValue("市");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(11);
tempCell.setCellValue("区");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(12);
tempCell.setCellValue("地址");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(13);
tempCell.setCellValue("商品品牌");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(14);
tempCell.setCellValue("商品名称");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(15);
tempCell.setCellValue("商品规格型号");
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(16);
tempCell.setCellValue("数量");
tempCell.setCellStyle(cellStyle2);
return sheet;
}
3.数据插入的公共模板
//插入数据到行模板
//rowIndex是插入数据的行数
public HSSFSheet setRowData (OrderEntity orderEntity, MailAddEntity addEntity, OrderDetailEntity
orderDetailEntity, OrderPackEntity orderPackEntity, HSSFCellStyle cellStyle2, HSSFSheet sheet, Integer rowIndex){
HSSFRow tempRow = sheet.createRow(rowIndex);
HSSFCell tempCell;
tempCell = tempRow.createCell(0);
tempCell.setCellValue(rowIndex - 1);
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(1);
tempCell.setCellValue(orderEntity.getOrderNo());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(2);
tempCell.setCellValue(orderPackEntity.getLogistNo());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(3);
tempCell.setCellValue(orderEntity.getCreateBy());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(4);
tempCell.setCellValue(orderEntity.getSendName());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(5);
tempCell.setCellValue(orderEntity.getSendPhone());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(6);
tempCell.setCellValue(orderEntity.getRecieveName());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(7);
tempCell.setCellValue(orderEntity.getRecievePhone());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(8);
tempCell.setCellValue(orderEntity.getReceiveIdCard());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(9);
tempCell.setCellValue(addEntity.getProvince());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(10);
tempCell.setCellValue(addEntity.getCity());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(11);
tempCell.setCellValue(addEntity.getArea());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(12);
tempCell.setCellValue(addEntity.getAddress());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(13);
tempCell.setCellValue(orderDetailEntity.getPaBrand());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(14);
tempCell.setCellValue(orderDetailEntity.getPaPm());
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(15);
if (orderDetailEntity.getPaSpec() != null) {
tempCell.setCellValue(orderDetailEntity.getPaSpec());
} else {
tempCell.setCellValue("");
}
tempCell.setCellStyle(cellStyle2);
tempCell = tempRow.createCell(16);
tempCell.setCellValue(orderDetailEntity.getPaCount().toString());
tempCell.setCellStyle(cellStyle2);
return sheet;
}
4.插入图片到excel里
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//图片是本地的一个文件
Image src = Toolkit.getDefaultToolkit().getImage(PathUtil.getClasspath() + face);
BufferedImage bufferImg = BufferedImageBuilder.toBufferedImage(src);
//图是一个URL链接
BufferedImage bufferImg = ImageIO.read(new URL(face));
if (bufferImg != null) {
ImageIO.write(bufferImg, "jpg", byteArrayOut);
//anchor主要用于设置图片的属性
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, i + 2, (short) 5, i + 3);
//插入图片
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
5.关于HSSFClientAnchor的参数说明
HSSFClientAnchor anchor = new HSSFClientAnchor(100, 200, 300, , (short) 4, i + 2, (short) 5, i + 3);
dx1：起始单元格的x偏移量，如例子中的100表示直线起始位置距E(i+2)单元格左侧的距离；
dy1：起始单元格的y偏移量，如例子中的200表示直线起始位置距E(i+2)单元格上侧的距离；
dx2：终止单元格的x偏移量，如例子中的300表示直线起始位置距F(i+3)单元格左侧的距离；
dy2：终止单元格的y偏移量，如例子中的400表示直线起始位置距F(i+3)单元格上侧的距离；
col1：起始单元格列序号，从0开始计算；
row1：起始单元格行序号，从0开始计算，如例子中col1=4,row1=i+2就表示起始单元格为E(i+2)；
col2：终止单元格列序号，从0开始计算；
row2：终止单元格行序号，从0开始计算，如例子中col2=5,row2=i + 3就表示起始单元格为F(i+3)；
网友评论