poi写入数据(合并单元格,格式化数据,插入公式)

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个缺           一 不可。

  • 2
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值