/*** 根据poi导出excel,并根据页面自动创建表头信息
*@parammergedRegion 表头格式信息
*@paramcolumnNames 字段信息
*@paramfileName 报表名
*@return
*/
public staticXSSFWorkbook createWorkBook(String mergedRegion, String columnNames, String fileName) {//创建新的Excel 工作簿
XSSFWorkbook workbook = newXSSFWorkbook();//设置字体
XSSFFont font =workbook.createFont();
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 14);//设置样式
XSSFCellStyle cellStyle =workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
XSSFCellStyle cellLeftStyle=workbook.createCellStyle();
cellLeftStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellLeftStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
XSSFCellStyle cellRightStyle=workbook.createCellStyle();
cellRightStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellRightStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
XSSFCellStyle cs=workbook.createCellStyle();
XSSFFont f=workbook.createFont();
f.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
f.setFontHeightInPoints((short) 10);
cs.setFont(f);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setWrapText(true);//获取数据行的列数
int length = columnNames.split("\\^\\_\\^").length;//第一行//在索引0的位置创建行(最顶端的行)
XSSFSheet sheet =workbook.createSheet(fileName);
XSSFRow row= sheet.createRow(0);
CellRangeAddress cellRangeAddress= new CellRangeAddress(0, 0, 0, length - 1);
sheet.addMergedRegion(cellRangeAddress);//在索引0的位置创建单元格(左上端)
XSSFCell cell = row.createCell(0);//定义单元格为字符串类型
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);//在单元格中输入一些内容
cell.setCellValue(fileName + "报表");//第二行
row = sheet.createRow(1);
cellRangeAddress= new CellRangeAddress(1, 1, 0, length - 1);
sheet.addMergedRegion(cellRangeAddress);
cell= row.createCell(0);
cell.setCellStyle(cellRightStyle);
cell.setCellValue("制表时间:"+ new SimpleDateFormat("yyyy-MM-dd HH:mm").format(newDate()));//处理表头,第三行开始
String[] mergedRegions = mergedRegion.split(";");//创建一个虚拟表头,并使用false标识这个单元格没有被占用
List> headerLists = new ArrayList>();for (int i = 0; i <= mergedRegions.length + 1; i++) {
Map headerMap = new HashMap();for (int j = 0; j < length; j++) {
headerMap.put(j,false);if (i == 0) {
sheet.setColumnWidth(j, (short) (16 * 256));
}
}
headerLists.add(headerMap);
}for (int i = 0; i < mergedRegions.length; i++) {
String mergedRegionss=mergedRegions[i];int x = 2 +i;int y = 2 +i;int m = 0;int n = 0;
row= sheet.createRow(2 +i);
row.setHeight((short) (2 * 256));
String[] _mergedRegionss= mergedRegionss.split(":");for (int j = 0; j < _mergedRegionss.length; j++) {
String mergedRegionsss=_mergedRegionss[j];
String[] _mergedRegionsss= mergedRegionsss.split(",");//获取最小行中未被占用的单元格
List cellNum = new ArrayList();for (int mm = 0; mm < headerLists.size(); mm++) {
Map headerMap =headerLists.get(mm);for(Integer key : headerLists.get(mm).keySet()) {if (!headerMap.get(key)) {
cellNum.add(key);
}
}if (cellNum.size() > 0) {break;
}
}
Collections.sort(cellNum);
m= cellNum.get(0);int _y = y + (Integer.parseInt(_mergedRegionsss[1])) - 1;if (Integer.parseInt(_mergedRegionsss[1]) == 0) {
_y=y;
}
n= n + (Integer.parseInt(_mergedRegionsss[2]));if (Integer.parseInt(_mergedRegionsss[2]) == 0) {
n=m;
}//String cra = x + ", " + _y + ", " + m + ", " + n;
for (int t = x - 2; t <= _y - 2; t++) {for (int k = m; k <= n; k++) {
headerLists.get(t).put(k,true);
}
}
cellRangeAddress= newCellRangeAddress(x, _y, m, n);
sheet.addMergedRegion(cellRangeAddress);
cell=row.createCell(m);
cell.setCellStyle(cs);
cell.setCellValue(_mergedRegionsss[0]);
m= n + 1;
}
}returnworkbook;
}