package com.boco.nbd.wios.export;
import com.boco.nbd.cams.core.constant.CamsConstant;
import com.deepoove.poi.XWPFTemplate;
import com.deepoove.poi.config.Configure;
import com.deepoove.poi.policy.HackLoopTableRenderPolicy;
import com.ihidea.core.util.DateUtilsEx;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.core.io.ClassPathResource;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* poi工具类
*
* @author xgl
* @version [v1.6, 2019年4月2日]
*/
public class PoiUtil {
public static HSSFWorkbook initExcel(String[][] titles, String fileName) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle ztStyle = wb.createCellStyle();
ztStyle.setAlignment(HorizontalAlignment.CENTER);
ztStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font ztFont = wb.createFont();
ztFont.setColor(Font.COLOR_NORMAL);
ztFont.setFontHeightInPoints((short)14);
ztFont.setBold(true);
ztStyle.setFont(ztFont);
HSSFSheet sheet = wb.createSheet();
sheet.setDefaultColumnWidth(20);
for (int j = 0; j < titles.length; j++) {
HSSFRow headRow = sheet.createRow(j);
for (int k = 0; k < titles[j].length; k++) {
HSSFCell headRowCell = headRow.createCell(k);
headRowCell.setCellStyle(ztStyle);
headRowCell.setCellValue(titles[j][k]);
}
}
if (fileName.startsWith("评价结果")) {
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 0);
CellRangeAddress region1 = new CellRangeAddress(0, 1, 1, 1);
CellRangeAddress region2 = new CellRangeAddress(0, 1, 2, 2);
CellRangeAddress region3 = new CellRangeAddress(0, 1, 3, 3);
CellRangeAddress region4 = new CellRangeAddress(0, 1, 4, 4);
CellRangeAddress region5 = new CellRangeAddress(0, 0, 5, 8);
CellRangeAddress region9 = new CellRangeAddress(0, 1, 9, 9);
sheet.addMergedRegion(region);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region3);
sheet.addMergedRegion(region4);
sheet.addMergedRegion(region5);
// sheet.addMergedRegion(region6);
// sheet.addMergedRegion(region7);
// sheet.addMergedRegion(region8);
sheet.addMergedRegion(region9);
}
return wb;
}
public static void downloadExcelToWebSite(HSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {
// response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-disposition",
"attachment; filename=" + new String(
(URLEncoder.encode(fileName, CamsConstant.UTF_8) + "-" + DateUtilsEx.formatToString(new Date(), "yyyyMMddHHmmss") + ".xls").getBytes(),
"ISO-8859-1"));
OutputStream os = null;
try {
os = response.getOutputStream();
wb.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (wb != null) {
// wb.dispose();
wb.close();
}
if (os != null) {
os.close();
}
}
}
public static void exportExcelToWebsite(HttpServletResponse response, String fileName, String[][] titles, String[] fields,
List<?> dataList) throws Exception {
// 初始化EXCEL
HSSFWorkbook wb = initExcel(titles, fileName);
exportExcel(response, fileName, titles, fields, dataList, wb);
}
public static void exportExcel(HttpServletResponse response, String fileName, String[][] titles, String[] fields, List<?> dataList,
HSSFWorkbook wb) throws Exception {
if (dataList != null) {
int sheetCount = wb.getNumberOfSheets();
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
for (int i = 0; i < sheetCount; i++) {
HSSFSheet eachSheet = wb.getSheetAt(i);
for (int row = 0; row < dataList.size(); row++) {
Object rowData = dataList.get(row);
HSSFRow eachDataRow = eachSheet.createRow(row + titles.length);
for (int col = 0; col < titles[0].length; col++) {
Object valueObj = null;
if (rowData instanceof Map<?, ?>) {
// map类型
valueObj = ((Map<?, ?>)rowData).get(fields[col]);
} else {
// 实体类
PropertyDescriptor pd = new PropertyDescriptor(fields[col], rowData.getClass());
valueObj = pd.getReadMethod().invoke(rowData);
}
// if (valueObj != null && valueObj.toString().startsWith("https://")) {
// ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
// URL url = new URL(valueObj.toString());
// RenderedImage bufferImg = ImageIO.read(url);
// ImageIO.write(bufferImg, "jpg", byteArrayOut);
//
// HSSFPatriarch patriarch = eachSheet.createDrawingPatriarch();
// // anchor主要用于设置图片的属性
// HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)col, row + 1, (short)(col +
// 1), (row + 2));
// anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
// // 插入图片
// patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(),
// HSSFWorkbook.PICTURE_TYPE_JPEG));
// } else {
HSSFCell cell = eachDataRow.createCell(col);
cell.setCellStyle(cellStyle);
cell.setCellValue(valueObj == null ? "" : valueObj.toString());
// }
}
}
}
}
downloadExcelToWebSite(wb, response, fileName);
}
// @SuppressWarnings("unchecked")
// public static void dynamicExportExcelToWebsite(HttpServletResponse response, String fileName, Map<String,
// List<Object>> exportMap)
// throws Exception {
// List<Object> fieldsObjList = exportMap.get("fields");
// List<Object> dataObjList = exportMap.get("data");
// List<String> fields = new ArrayList<>();
// List<String> titles = new ArrayList<>();
// for (int i = 0; i < fieldsObjList.size(); i++) {
// Object obj = fieldsObjList.get(i);
// if (obj != null) {
// Map<String, String> map = (Map<String, String>)obj;
// fields.add(map.get("key"));
// titles.add(map.get("name"));
// }
// }
// String[] titlesArr = new String[titles.size()];
// String[] fieldsArr = new String[fields.size()];
// exportExcelToWebsite(response, fileName, titles.toArray(titlesArr), fields.toArray(fieldsArr), dataObjList);
// }
public static void exportOrderDocx(HttpServletResponse response, Map<String, Object> dataMap, String fileName,
String templateName) throws Exception {
HackLoopTableRenderPolicy policy = new HackLoopTableRenderPolicy();
Configure config = Configure.newBuilder().bind("picFiles", policy).build();
ClassPathResource resource = new ClassPathResource("static/templates/" + templateName + ".docx");
InputStream inputStream = resource.getInputStream();
response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1"));
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
XWPFTemplate template = XWPFTemplate.compile(inputStream, config).render(dataMap);
OutputStream out = response.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(out);
template.write(bos);
template.close();
bos.flush();
bos.close();
out.flush();
out.close();
}
}