package cn.netconcepts.vivo_hour_contrast.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import cn.netconcepts.vivo_hour_contrast.model.VivoBean;
public class POIUtils {
/**
* 向文件写入数据
* @param path
* @param type
* @param date1
* @param date2
* @param hour
* @param list1
* @param list2
* @return
*/
public static String writeData(String path,String type,String date1,String date2,String hour,
List<VivoBean> list1,List<VivoBean> list2) throws Exception{
File file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
// 2007
Workbook wb = new SXSSFWorkbook(100);
Sheet sheet = wb.createSheet("vivo "+hour+"点数据对比");
// 搜索提词
List<String> title1 = new ArrayList<>();
title1.add("");
title1.add(date1+" "+hour+"点");
title1.add("");
title1.add("");
title1.add("");
title1.add(date2+" "+hour+"点");
// 广告位
List<String> title1_ = new ArrayList<>();
title1_.add(date1+" "+hour+"点");
title1_.add("");
title1_.add("");
title1_.add("");
title1_.add(date2+" "+hour+"点");
// 搜索提词
List<String> title2 = new ArrayList<>();
title2.add("广告创意");
title2.add("广告位");
title2.add("昨日下载");
title2.add("昨日消耗");
title2.add("昨日下载单价");
title2.add("今日下载");
title2.add("今日消耗");
title2.add("今日下载单价");
title2.add("环比");
// 广告位
List<String> title2_ = new ArrayList<>();
title2_.add("广告位");
title2_.add("昨日下载");
title2_.add("昨日消耗");
title2_.add("昨日下载单价");
title2_.add("今日下载");
title2_.add("今日消耗");
title2_.add("今日下载单价");
title2_.add("环比");
// 设置表头
Row Row0 = sheet.createRow(0);
Row Row1 = sheet.createRow(1);
CellStyle titleStyle = titleStyle(wb);
if(type.equals("word")){
for (int i = 0; i < title1.size(); i++){
sheet.setColumnWidth(i, 13 * 230);
Cell cell = Row0.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue(title1.get(i));
}
sheet.addMergedRegion(new CellRangeAddress(0,0,1,4));
sheet.addMergedRegion(new CellRangeAddress(0,0,5,8));
for (int i = 0; i < title2.size(); i++){
sheet.setColumnWidth(i, 13 * 230);
Cell cell = Row1.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue(title2.get(i));
}
}else{
for (int i = 0; i < title1_.size(); i++){
sheet.setColumnWidth(i, 13 * 230);
Cell cell = Row0.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue(title1_.get(i));
}
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
sheet.addMergedRegion(new CellRangeAddress(0,0,4,7));
for (int i = 0; i < title2_.size(); i++){
sheet.setColumnWidth(i, 13 * 230);
Cell cell = Row1.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue(title2_.get(i));
}
}
sheet.setForceFormulaRecalculation(true);
CellStyle borderStyle = borderStyle(wb);
DataFormat format = wb.createDataFormat();
CellStyle costCellStyle = costCellStyle(wb,format);
CellStyle decimalCellStyle = decimalCellStyle(wb,format);
if(type.equals("word")){
// 写入数据
for(int i=0;i<list1.size();i++){
Row row = sheet.createRow(i + 2);
for(int j=0;j<list2.size();j++){
int a = 0;
// 计划名称和广告名称必须匹配
if(list1.get(i).getPlaceName().equals(list2.get(j).getPlaceName())
&& list1.get(i).getAdName().equals(list2.get(j).getAdName())){
for(int c=0;c<title2.size();c++){
sheet.setColumnWidth(i, 13 * 230);
Cell cell = row.createCell(c);
cell.setCellStyle(borderStyle);
if(c==0){
cell.setCellValue(list1.get(i).getAdName());
}else if(c==1){
cell.setCellValue(list1.get(i).getAdPlace());
}else if(c==2){
cell.setCellValue(Integer.valueOf(list2.get(j).getDown()));
}else if(c==3){
cell.setCellValue(Double.valueOf(list2.get(j).getCost()));
cell.setCellStyle(costCellStyle);
}else if(c==4){
cell.setCellFormula("=(D"+(i+3)+"/C"+(i+3)+")");
}else if(c==5){
cell.setCellValue(Integer.valueOf(list1.get(i).getDown()));
}else if(c==6){
cell.setCellValue(Double.valueOf(list1.get(i).getCost()));
cell.setCellStyle(costCellStyle);
}else if(c==7){
cell.setCellFormula("=(G"+(i+3)+"/F"+(i+3)+")");
}else if(c==8){
cell.setCellFormula("=(G"+(i+3)+"-D"+(i+3)+")/D"+(i+3));
cell.setCellStyle(decimalCellStyle);
}else{}
}
break;
}else{
a++;
if(a==list2.size()){
for(int c=0;c<title2.size();c++){
sheet.setColumnWidth(i, 13 * 230);
Cell cell = row.createCell(c);
cell.setCellStyle(borderStyle);
if(c==0){
cell.setCellValue(list1.get(i).getAdName());
}else if(c==1){
cell.setCellValue(list1.get(i).getAdPlace());
}else if(c==2){
cell.setCellValue(0);
}else if(c==3){
cell.setCellValue(0.0);
cell.setCellStyle(costCellStyle);
}else if(c==4){
cell.setCellValue(0);
}else if(c==5){
cell.setCellValue(Integer.valueOf(list1.get(i).getDown()));
}else if(c==6){
cell.setCellValue(Double.valueOf(list1.get(i).getCost()));
cell.setCellStyle(costCellStyle);
}else if(c==7){
cell.setCellFormula("=(G"+(i+3)+"/F"+(i+3)+")");
}else if(c==8){
cell.setCellValue(0);
cell.setCellStyle(decimalCellStyle);
}else{}
}
}
}
}
}
}else{
// 写入数据
for(int i=0;i<list1.size();i++){
Row row = sheet.createRow(i + 2);
for(int j=0;j<list2.size();j++){
int a = 0;
// 计划名称和广告名称必须匹配
if(list1.get(i).getPlaceName().equals(list2.get(j).getPlaceName())){
for(int c=0;c<title2_.size();c++){
sheet.setColumnWidth(i, 13 * 230);
Cell cell = row.createCell(c);
cell.setCellStyle(borderStyle);
if(c==0){
cell.setCellValue(list1.get(i).getAdPlace());
}else if(c==1){
cell.setCellValue(Integer.valueOf(list2.get(j).getDown()));
}else if(c==2){
cell.setCellValue(Double.valueOf(list2.get(j).getCost()));
cell.setCellStyle(costCellStyle);
}else if(c==3){
cell.setCellFormula("=(C"+(i+3)+"/B"+(i+3)+")");
cell.setCellStyle(costCellStyle);
}else if(c==4){
cell.setCellValue(Integer.valueOf(list1.get(i).getDown()));
}else if(c==5){
cell.setCellValue(Double.valueOf(list1.get(i).getCost()));
cell.setCellStyle(costCellStyle);
}else if(c==6){
cell.setCellFormula("=(F"+(i+3)+"/E"+(i+3)+")");
cell.setCellStyle(costCellStyle);
}else if(c==7){
cell.setCellFormula("=(F"+(i+3)+"-C"+(i+3)+")/C"+(i+3));
cell.setCellStyle(decimalCellStyle);
}else{}
}
break;
}else{
a++;
if(a==list2.size()){
for(int c=0;c<title2_.size();c++){
sheet.setColumnWidth(i, 13 * 230);
Cell cell = row.createCell(c);
cell.setCellStyle(borderStyle);
if(c==0){
cell.setCellValue(list1.get(i).getAdPlace());
}else if(c==1){
cell.setCellValue(0);
}else if(c==2){
cell.setCellValue(0.0);
cell.setCellStyle(costCellStyle);
}else if(c==3){
cell.setCellValue(0);
cell.setCellStyle(costCellStyle);
}else if(c==4){
cell.setCellValue(Integer.valueOf(list1.get(i).getDown()));
}else if(c==5){
cell.setCellValue(Double.valueOf(list1.get(i).getCost()));
cell.setCellStyle(costCellStyle);
}else if(c==6){
cell.setCellFormula("=(F"+(i+3)+"/E"+(i+3)+")");
cell.setCellStyle(costCellStyle);
}else if(c==7){
cell.setCellValue(0);
cell.setCellStyle(decimalCellStyle);
}else{}
}
}
}
}
}
}
FileOutputStream os = new FileOutputStream(path);
wb.write(os);
os.flush();
os.close();
return null;
}
/**
* 表头样式
* @param wb
* @return
*/
private static CellStyle titleStyle(Workbook wb){
Font fontTitle = wb.createFont();
fontTitle.setFontName("微软雅黑");
fontTitle.setColor(HSSFColor.WHITE.index);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE
.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFont(fontTitle);
cellStyle.setFillForegroundColor((short)30);//设置单元格背景颜色
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
return cellStyle;
}
/**
* 边框样式
* @param wb
* @return
*/
private static CellStyle borderStyle(Workbook wb){
Font fontTitle = wb.createFont();
fontTitle.setFontName("微软雅黑");
fontTitle.setFontHeightInPoints((short) 10);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFont(fontTitle);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
return cellStyle;
}
/**
* 消费样式
* @param wb
* @param format
* @return
*/
private static CellStyle costCellStyle(Workbook wb,DataFormat format){
Font fontTitle = wb.createFont();
fontTitle.setFontName("微软雅黑");
fontTitle.setFontHeightInPoints((short) 10);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFont(fontTitle);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cellStyle.setDataFormat(format.getFormat("¥#,##0.00"));
return cellStyle;
}
/**
* 小数样式
* @param wb
* @param format
* @return
*/
private static CellStyle decimalCellStyle(Workbook wb,DataFormat format){
Font fontTitle = wb.createFont();
fontTitle.setFontName("微软雅黑");
fontTitle.setFontHeightInPoints((short) 10);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFont(fontTitle);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cellStyle.setDataFormat(format.getFormat("0.00"));
return cellStyle;
}
public static void main(String[] args) {
try {
//POIUtils.getCurrentData("E:\\导出结果 (2).xls","word");
List<VivoBean> list1 = new ArrayList<VivoBean>();
VivoBean v1 = new VivoBean();
v1.setAdPlace("搜索提词");
v1.setPlaceName("关键词-核心作业词");
v1.setAdName("作业");
v1.setCost(48.0);
v1.setDown(3);
list1.add(v1);
VivoBean v3 = new VivoBean();
v3.setAdPlace("搜索提词44");
v3.setPlaceName("关键词-核心作业词22");
v3.setAdName("作业22");
v3.setCost(58.0);
v3.setDown(7);
list1.add(v3);
List<VivoBean> list2 = new ArrayList<VivoBean>();
VivoBean v2 = new VivoBean();
v2.setAdPlace("搜索提词");
v2.setPlaceName("关键词-核心作业词");
v2.setAdName("作业");
v2.setCost(58.0);
v2.setDown(4);
list2.add(v2);
POIUtils.writeData("E:\\bbcc.xlsx", "word", "2019-10-22", "2019-10-23", "8", list1, list2);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
1、这里主要涉及到合并单元格,通过sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));实现
2、涉及到格式化数据,通过DataFormat format = wb.createDataFormat();实现(具体看我内部的消费样式,小数样式)
3、插入公式通过cell.setCellFormula("=(D"+(i+3)+"/C"+(i+3)+")");和sheet.setForceFormulaRecalculation(true);实现,这2个缺 一 不可。