package org.jeecgframework.web.cgreport.service.impl.excel;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.jeecgframework.web.cgreport.service.excel.CgReportExcelServiceI;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service(value="cgReportExcelService")
@Transactional
public class CgReportExcelServiceImpl extends CommonServiceImpl implements
CgReportExcelServiceI {
@SuppressWarnings("unchecked")
public HSSFWorkbook exportExcel(String title, Collection<?> titleSet,
Collection<?> dataSet) {
// 声明一个工作薄
HSSFWorkbook workbook = null;
try {
// 首先检查数据看是否是正确的
if (titleSet == null || titleSet.size() == 0) {
throw new Exception("读取表头失败！");
}
if (title == null) {
title = "";
}
// 声明一个工作薄
workbook = new HSSFWorkbook();
// 生成一个表格
Sheet sheet = workbook.createSheet(title);
int rindex = 0;
int cindex = 0;
// 产生表格标题行
Row row = sheet.createRow(rindex);
row.setHeight((short) 450);
CellStyle titleStyle = getTitleStyle(workbook);
// Iterator itTitle = titleSet.iterator();
List<Map> titleList = (List<Map>) titleSet;
Iterator itData = dataSet.iterator();
//遍历标题行
for(Map titleM : titleList){
String titleContent = (String) titleM.get("field_txt");
Cell cell = row.createCell(cindex);
RichTextString text = new HSSFRichTextString(titleContent);
cell.setCellValue(text);
cell.setCellStyle(titleStyle);
cindex++;
}
HSSFCellStyle bodyStyle = getOneStyle(workbook);
//遍历内容
while (itData.hasNext()) {
cindex = 0;
rindex++;
row = sheet.createRow(rindex);
Map dataM = (Map) itData.next();//获取每一行的内容
for(Map titleM : titleList){
String field = (String) titleM.get("field_name");
String content = dataM.get(field)==null?"":dataM.get(field).toString();
Cell cell = row.createCell(cindex);
RichTextString text = new HSSFRichTextString(content);
cell.setCellStyle(bodyStyle);
cell.setCellValue(text);
cindex++;
}
}
for(int i=0; i<titleList.size(); i++){
sheet.autoSizeColumn(i);
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
/**
* exce表头单元格样式处理
* @param workbook
* @return
*/
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
// 产生Excel表头
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 底边框
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 顶边框
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 填充的背景颜色
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
return titleStyle;
}
//生成多少行的带有比边框的空行
public static void setBlankRows(int rows,int columns,HSSFWorkbook workbook){
//得到第一页
Sheet sheet = workbook.getSheetAt(0);
//样式
CellStyle cellStyle = getOneStyle(workbook);
for (int i = 1; i <=rows; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < columns; j++) {
row.createCell(j).setCellStyle(cellStyle);
}
}
}
public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook) {
// 产生Excel表头
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderLeft((short) 1); // 左边框
style.setBorderRight((short) 1); // 右边框
style.setBorderBottom((short) 1);
style.setBorderTop((short) 1);
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填充的背景颜色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
return style;
}
public static HSSFCellStyle getOneStyle(HSSFWorkbook workbook) {
// 产生Excel表头
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderLeft((short) 1); // 左边框
style.setBorderRight((short) 1); // 右边框
style.setBorderBottom((short) 1);
style.setBorderTop((short) 1);
return style;
}
}