生成excel poi设置背景颜色 字体 合并单元格 。。。。
包
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
代码
public static void writer(String path, String fileName,String fileType,List<object> list,String titleRow[] ,String count[]) throws Exception {
Workbook wbk = null;
String excelPath = path+File.separator+fileName+"."+fileType;
File file = new File(excelPath);
Sheet sheet =null;
//1.创建工作文档对象
if (!file.exists()) {
if (fileType.equals("xls")) {
wbk = new HSSFWorkbook();
} else if(fileType.equals("xlsx")) {
wbk = new XSSFWorkbook();
} else {
throw new Exception("文件格式不正确");
}
//2.创建sheet对象
sheet = (Sheet) wbk.createSheet("sheet1");
OutputStream outputStream = new FileOutputStream(excelPath);
wbk.write(outputStream);
outputStream.flush();
outputStream.close();
} else {
if (fileType.equals("xls")) {
wbk = new HSSFWorkbook();
} else if(fileType.equals("xlsx")) {
wbk = new XSSFWorkbook();
} else {
throw new Exception("文件格式不正确");
}
}
//创建sheet对象
if (sheet==null) {
sheet = (Sheet) wbk.createSheet("sheet1");
}
//3.创建样式对象
CellStyle style = wbk.createCellStyle(); // 样式对象(表头)
CellStyle style1 = wbk.createCellStyle(); // 样式对象(正文)
sheet.setDisplayGridlines(false);//去除网格线
//表头样式
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //填充单元格
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);//设置单元格的背景颜色为淡蓝色
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
style.setAlignment(CellStyle.ALIGN_CENTER);// 水平
style.setWrapText(true);// 指定当单元格内容显示不下时自动换行
Font font = wbk.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 280);
style.setFont(font);
//正文样式
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
style1.setAlignment(CellStyle.ALIGN_CENTER);// 水平
style1.setWrapText(true);// 指定当单元格内容显示不下时自动换行
Font font1 = wbk.createFont();
font1.setFontName("新宋体");
font1.setFontHeight((short) 280);
style1.setFont(font1);
//4.添加表头
Row row = sheet.createRow(1);
Cell cell = row.createCell(1); //创建详情 表头
Cell cell1 = row.createCell(8); //创建统计 表头
cell.setCellValue("网点签到数据详情");
cell1.setCellValue("统计报表");
row.setHeight((short) 600);
//5.单元格合并 (四个参数分别是:起始行号,终止行号, 起始列号,终止列号)
sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));//详情
cell.setCellStyle(style); // 添加样式
sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 9));//统计
cell1.setCellStyle(style); // 添加样式
sheet.autoSizeColumn(5200);
//6.添加附表头
row = sheet.createRow(2); //创建第二行
for(int i = 0;i < titleRow.length;i++){//详情
cell = row.createCell(i+1);
cell.setCellValue(titleRow[i]);
cell.setCellStyle(style);
sheet.setColumnWidth(i+1, 20 * 256);
}
for(int i = 0;i < count.length;i++){//统计
cell = row.createCell(i+8);
cell.setCellValue(count[i]);
cell.setCellStyle(style);
sheet.setColumnWidth(i+8, 20 * 256);
}
row.setHeight((short) 540);
//7.循环写入行数据 (数据详情)
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i+3);
row.createCell(1).setCellValue(( list.get(i)).getTags());
row.getCell(1).setCellStyle(style1);
row.createCell(2).setCellValue(( list.get(i)).getTitle());
row.getCell(2).setCellStyle(style1);
row.createCell(3).setCellValue(( list.get(i)).getAddress());
row.getCell(3).setCellStyle(style1);
row.createCell(4).setCellValue(( list.get(i)).getEmpName());
row.getCell(4).setCellStyle(style1);
row.createCell(5).setCellValue(( list.get(i)).getCreateTime());
row.getCell(5).setCellStyle(style1);
if(i < 5) {//循环写入行数据 (统计报表)
row.createCell(8).setCellValue(( list.get(i)).getTags());
row.getCell(8).setCellStyle(style1);
row.createCell(9).setCellValue(( list.get(i)).getTitle());
row.getCell(9).setCellStyle(style1);
}
row.setHeight((short) 500);
}
//8.创建文件流
OutputStream stream = new FileOutputStream(excelPath);
//9.写入数据
wbk.write(stream);
//10.关闭文件流
stream.close();
}
最后附上 效果图