import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hslf.model.Sheet;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
public class ExportExcelUtil {
private HSSFWorkbook wb;
private HSSFCellStyle styleTitle;
private HSSFCellStyle styleLeft;
private HSSFCellStyle styleCenter;
private HSSFCellStyle styleRight;
private HSSFFont fontTitle;
private HSSFFont fontData;
public ExportExcelUtil() {
this.init();
}
public byte[] getExcelByte(List<Map<String, Object>> dataList, List<Map<String, Object>> colConfig) {
// 第一步, 初始化
this.init();
// 在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("sheet1");
// 设置单元格居中
// 加粗居中样式
// 设置表头
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < colConfig.size(); i++) {
int length =oConvertUtils.getInt(String.valueOf(colConfig.get(i).get("width"))) ;
//sheet.setColumnWidth(i, (short) (50 * length));
HSSFCell cell = row.createCell(i);
cell.setCellValue(colConfig.get(i).get("title").toString());
cell.setCellStyle(styleTitle);
}
// 设置内容
for (int i = 0; i < dataList.size(); i++) {
HSSFRow row1 = sheet.createRow(i + 1);
for (int j = 0; j < colConfig.size(); j++) {
HSSFCell cell = row1.createCell(j);
Object object= dataList.get(i).get(colConfig.get(j).get("field"));
cell.setCellValue(object==null?"":object.toString() );
cell.setCellStyle(this.getDataHSSFCellStyle(colConfig.get(j)));
}
}
//设置自动列宽
for (int i = 0; i < colConfig.size(); i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i,sheet.getColumnWidth(i)*12/10);
}
return toByteArray();
}
private void init(){
wb = new HSSFWorkbook();
//
fontTitle = wb.createFont();
fontTitle.setFontName("宋体");
fontTitle.setFontHeightInPoints((short)11);
fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
//
styleTitle = wb.createCellStyle();
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleTitle.setFont(fontTitle);
styleTitle.setFillForegroundColor( HSSFColor.SKY_BLUE.index);
styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//
fontData = wb.createFont();
fontData.setFontName("宋体");
fontData.setFontHeightInPoints((short)11);
//
styleLeft = wb.createCellStyle();
styleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);
styleLeft.setFont(fontData);
styleLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//
styleCenter = wb.createCellStyle();
styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleCenter.setFont(fontData);
styleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//
styleRight = wb.createCellStyle();
styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
styleRight.setFont(fontData);
styleRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleRight.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleRight.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);
}
private HSSFCellStyle getDataHSSFCellStyle(Map<String, Object> parms){
String align= parms.get("align").toString();
HSSFCellStyle hssfCellStyle=null;
switch (align) {
case "left":
hssfCellStyle=this.styleLeft;
break;
case "center":
hssfCellStyle=this.styleCenter;
break;
case "right":
hssfCellStyle=this.styleRight;
break;
default:
hssfCellStyle=this.styleLeft;
break;
}
return hssfCellStyle;
}
public HSSFWorkbook getWb() {
return wb;
}
public void setWb(HSSFWorkbook wb) {
this.wb = wb;
}
public HSSFCellStyle getStyleTitle() {
return styleTitle;
}
public void setStyleTitle(HSSFCellStyle styleTitle) {
this.styleTitle = styleTitle;
}
public HSSFCellStyle getStyleLeft() {
return styleLeft;
}
public void setStyleLeft(HSSFCellStyle styleLeft) {
this.styleLeft = styleLeft;
}
public HSSFCellStyle getStyleCenter() {
return styleCenter;
}
public void setStyleCenter(HSSFCellStyle styleCenter) {
this.styleCenter = styleCenter;
}
public HSSFCellStyle getStyleRight() {
return styleRight;
}
public void setStyleRight(HSSFCellStyle styleRight) {
this.styleRight = styleRight;
}
public HSSFFont getFontTitle() {
return fontTitle;
}
public void setFontTitle(HSSFFont fontTitle) {
this.fontTitle = fontTitle;
}
public HSSFFont getFontData() {
return fontData;
}
public void setFontData(HSSFFont fontData) {
this.fontData = fontData;
}
public byte[] toByteArray(){
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
}
return os.toByteArray();
}
}