POI导出Excel代码实例（设置字体，边框，合并单元格，其它功能以后探索到继续更新）

最新推荐文章于 2023-08-06 18:35:56 发布
小白Alan
最新推荐文章于 2023-08-06 18:35:56 发布
阅读量1k
点赞数
分类专栏： java编码及问题文章标签： poi 实例常用设置
本文链接：https://blog.csdn.net/lihaiyong92/article/details/62049120
版权
java编码及问题专栏收录该内容
31 篇文章 0 订阅
订阅专栏
package com.fbi.common.service.production;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import com.fbi.common.model.ProductUnitExport;
import com.fbi.common.utils.StringKit;

@Service
public class ProductInfoExportService {
	public static final Logger logger = Logger.getLogger(ProductInfoExportService.class);

//表头样式
	private static CellStyle space = null;
//表头样式
	private static CellStyle headerCenter = null;
//导入表头样式
	private static CellStyle headerCenterTypeIn = null;
//字段解释表头样式
	private static CellStyle headerCenterExpIn = null;
// 单元格数据右对齐
	private static CellStyle textRight = null;
//单元格数据居中
	private static CellStyle textCenter = null;
//单元格数据居左
	private static CellStyle textLeft = null;
//解释sheet单元格数据居左
	private static CellStyle textExpLeft = null;
//左对齐自动换行
	private static CellStyle textLeftWrap = null;
// 单元格数据居中
	private static CellStyle textCenterAndRight = null;
// 格式化浮点数数据
	private static CellStyle textDataFormat = null;

	/**
	 *       *
	 * 
	 * <pre>
	 *     * 功能描述：导出产品批量导入模版     * 使用示范：     * @param      * @return     * @throws  
	 *   * 
	 */
	public Workbook exportProductTypeInTmplate(List<Map> prodInfo, List<ProductUnitExport> unitAll, String fileName) {

		Workbook wb = this.createWorkbook(fileName);
		initStyle(wb);
		setInfoSheetForTypeIn(wb, prodInfo);
		setUnitSheetForTypeIn(wb, unitAll);

//图片为此方法导出的实际效果

		setExplainSheetForTypeIn(wb);
		return wb;
	}

	/**
	 *       *
	 * 
	 * <pre>
	 *     * 功能描述：导出产品信息     * 使用示范：     * @param      * @return     * @throws
	 */
	public Workbook exportProductDetail(List<Map> prodInfo, List<ProductUnitExport> unitAll, String fileName) {

		Workbook wb = this.createWorkbook(fileName);
		initStyle(wb);
		setInfoSheet(wb, prodInfo);
		setUnitSheet(wb, unitAll);
		return wb;
	}

	/**
	 *     * 功能描述：填充产品单位sheet
	 */
	private void setUnitSheet(Workbook wb, List<ProductUnitExport> unitAll) {
		Sheet sheet = wb.createSheet("产品单位");
//构造表头
		List<String> list = new ArrayList<String>();
		list.add("序号");
		list.add("产品代码");
		list.add("产品名称");
		list.add("转换为基本单位的分母");
		list.add("可选单位");
		list.add("可选单位汉语解释");
		list.add("转换为基本单位的分子");
		list.add("基本单位");
		list.add("平行计量单位类型");
		list.add("最小起订量");
		list.add("最小起订增量");
		list.add("来源");

		initHeader(sheet, list, headerCenter);
		initUnitBorderSize(sheet);
//给一个初始化的开始行标(合并单元格取消，所以注掉)
// int nextIndex=1;
		for (int i = 0; i < unitAll.size(); i++) {
			ProductUnitExport units = unitAll.get(i);
			Row row = sheet.createRow(i + 1);

// 设置行高
			int colIndex = 0;
			row.setHeightInPoints(20);
			creatCellCloneStyle(row, colIndex, i + 1, textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getProdCode() == null ? "" : units.getProdCode(), textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getProdName() == null ? "" : units.getProdName(), textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getxUnitValue() == null ? "" : units.getxUnitValue(), textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getxUnit() == null ? "" : units.getxUnit(), textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getxUnitExp() == null ? "" : units.getxUnitExp(), textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getyUnitValue() == null ? "" : units.getyUnitValue(), textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getyUnit() == null ? "" : units.getyUnit(), textLeft);
			if (Integer.parseInt(units.getUnitType()) == 2) {
				if (units.getSource() != null && "SAP".equals(units.getSource().toUpperCase())) {
					creatCellCloneStyle(row, ++colIndex, "A", textLeft);
				} else {
					creatCellCloneStyle(row, ++colIndex, "M", textLeft);
				}
			} else {
				creatCellCloneStyle(row, ++colIndex, "", textLeft);
			}
			creatCellCloneStyle(row, ++colIndex, units.getMinNum() == null ? "" : units.getMinNum(), textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getMinNumAdd() == null ? "" : units.getMinNumAdd(), textLeft);
			creatCellCloneStyle(row, ++colIndex, units.getSource() == null ? "" : units.getSource(), textLeft);
		}
	}

	/**
	 * 填充产品信息sheet
	 */
	private void setInfoSheet(Workbook wb, List<Map> prodInfo) {
		Sheet sheet = wb.createSheet("产品信息");
// 构造表头
		List<String> list = new ArrayList<String>();
		list.add("序号");
		list.add("产品名称");
		list.add("产品代码");
		list.add("产品条码");
		list.add("物料组1代码");
		list.add("物料组1");
		list.add("物料组2代码");
		list.add("物料组2");
		list.add("物料组3代码");
		list.add("物料组3");
		list.add("物料组4代码");
		list.add("物料组4");
		list.add("中文简称");
		list.add("英文简称");
		list.add("产品属性");
		list.add("品项");
		list.add("产品形态");
		list.add("产品等级-体型");
		list.add("产品等级-重量");
		list.add("对应产品");
		list.add("跨级替换产品");
		list.add("部位模型");
		list.add("产品效益分级");
		list.add("生产顺序");
		list.add("生产工位");
		list.add("生产级别分组");
		list.add("生产周期");
		list.add("可竞标");
		list.add("库龄类型");
		list.add("标准零售价");
		list.add("税率");
		list.add("重点产品");
		list.add("产品状态");
		list.add("备注");
		list.add("工厂");
		list.add("统计级");
		list.add("考核级");

		initHeader(sheet, list, headerCenter);
		initInfoBorderSize(sheet);
//给一个初始化的开始行标(合并单元格取消，所以注掉)
// int nextIndex=1;
		for (int i = 0; i < prodInfo.size(); i++) {
			Map map = prodInfo.get(i);
			Row row = sheet.createRow(i + 1);
//获取需要合并的行数
// short index = 0;
// if(map.get("replaceProds")!=null||map.get("prdpartmodel")!=null||map.get("prdprodustations")!=null){
// int repSize=map.get("replaceProds")==null? 0:((List)map.get("replaceProds")).size();
// int modSize=map.get("prdpartmodel")==null? 0:((List)map.get("prdpartmodel")).size();
// int statSize=map.get("prdprodustations")==null? 0:((List)map.get("prdprodustations")).size();
// index=(short) (repSize>modSize? (repSize>statSize? repSize:statSize):(modSize>statSize? modSize:statSize));
// //下一次填充，从第（上次行标+合并行数）行开始,合并单元格
// for (int j = 1; j < index; j++) {
// sheet.createRow(nextIndex+j);
// //避免合并单元格后单元格格式丢失，先给每个单元格赋个空值。
// this.creatCellBeforeIndex(sheet.getRow(nextIndex+j), list.size(), textRight);
// }
// for (int j = 0; j < 24; j++) {
// CellRangeAddress cra1 = new CellRangeAddress(nextIndex, nextIndex+index-1, j, j);
// sheet.addMergedRegion(cra1);
// }
// CellRangeAddress cra2 = new CellRangeAddress(nextIndex, nextIndex+index-1, 27, 27);
// CellRangeAddress cra3 = new CellRangeAddress(nextIndex, nextIndex+index-1, 28, 28);
// sheet.addMergedRegion(cra2);
// sheet.addMergedRegion(cra3);
// for (int j = 30; j < 39; j++) {
// CellRangeAddress cra4 = new CellRangeAddress(nextIndex, nextIndex+index-1, j, j);
// sheet.addMergedRegion(cra4);
// }
// }
// 设置行高
			int colIndex = 0;
			row.setHeightInPoints(20);
			creatCell(row, colIndex, i + 1, textLeft);
			creatCell(row, ++colIndex, map.get("prodname"), textLeft);
			creatCell(row, ++colIndex, map.get("prodcode"), textLeft);
			creatCell(row, ++colIndex, map.get("simplecode"), textLeft);
			creatCell(row, ++colIndex,
					map.get("classcode1") == null ? "" : (StringKit.trimForTemp(map.get("classcode1") + "", "#")),
					textLeft);
			creatCell(row, ++colIndex, map.get("classname1"), textLeft);
			creatCell(row, ++colIndex,
					map.get("classcode2") == null ? "" : (StringKit.trimForTemp(map.get("classcode2") + "", "#")),
					textLeft);
			creatCell(row, ++colIndex, map.get("classname2"), textLeft);
			creatCell(row, ++colIndex,
					map.get("classcode3") == null ? "" : (StringKit.trimForTemp(map.get("classcode3") + "", "#")),
					textLeft);
			creatCell(row, ++colIndex, map.get("classname3"), textLeft);
			creatCell(row, ++colIndex,
					map.get("classcode4") == null ? "" : (StringKit.trimForTemp(map.get("classcode4") + "", "#")),
					textLeft);
			creatCell(row, ++colIndex, map.get("classname4"), textLeft);
			creatCell(row, ++colIndex, map.get("prodspcodecn"), textLeft);
			creatCell(row, ++colIndex, map.get("prodspcode"), textLeft);
			creatCell(row, ++colIndex, map.get("productnatrue"), textLeft);
			creatCell(row, ++colIndex, map.get("item") == null ? "" : map.get("item"), textLeft);
			creatCell(row, ++colIndex, map.get("shapitem"), textLeft);
			creatCell(row, ++colIndex, map.get("prdshape"), textLeft);
			creatCell(row, ++colIndex, map.get("prdweight"), textLeft);
			creatCell(row, ++colIndex, map.get("paraprod"), textLeft);
			creatCell(row, ++colIndex, map.get("replaceProds") == null ? "" : map.get("replaceProds"), textLeft);
			creatCell(row, ++colIndex, map.get("prdpartmodel") == null ? "" : map.get("prdpartmodel"), textLeft);
			creatCell(row, ++colIndex, map.get("prdefflvl"), textLeft);
			creatCell(row, ++colIndex, map.get("prdproduceorder"), textLeft);
			creatCell(row, ++colIndex, map.get("prdprodustations") == null ? "" : map.get("prdprodustations"),
					textLeft);
			creatCell(row, ++colIndex, map.get("prdprodulvlgrp"), textLeft);
			creatCell(row, ++colIndex, map.get("prdproducycle"), textLeft);
			creatCell(row, ++colIndex, map.get("bidding"), textLeft);
			creatCell(row, ++colIndex, map.get("storagelifetype"), textLeft);
			creatCell(row, ++colIndex,
					"-1.00".equals(map.get("standardsellprice") + "") ? "" : map.get("standardsellprice"), textLeft);
			creatCell(row, ++colIndex, map.get("taxrate"), textLeft);
			creatCell(row, ++colIndex, map.get("prdt_is_imp"), textLeft);
			creatCell(row, ++colIndex, map.get("status"), textLeft);
			creatCell(row, ++colIndex, map.get("remark"), textLeftWrap);
			creatCell(row, ++colIndex, map.get("prodFactory") == null ? "" : map.get("prodFactory"), textLeft);
			creatCell(row, ++colIndex, map.get("statisticslvl") == null ? "" : map.get("statisticslvl"), textLeft);
			creatCell(row, ++colIndex, map.get("assesslvl") == null ? "" : map.get("assesslvl"), textLeft);

		}
	}

	/**
	 * 功能描述：初始化样式 使用示范： @param @return @throws
	 */
	private void initStyle(Workbook wb) {

//空格式
		space = wb.createCellStyle();
		space.setBorderTop(CellStyle.BORDER_NONE);
		space.setBorderBottom(CellStyle.BORDER_NONE);
		space.setBorderLeft(CellStyle.BORDER_NONE);
		space.setBorderRight(CellStyle.BORDER_NONE);

//表头
		headerCenter = wb.createCellStyle();
		Font headerFont = wb.createFont();
		headerFont.setFontName("黑体");
		headerFont.setFontHeightInPoints((short) 12);
		headerCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headerCenter.setAlignment(CellStyle.ALIGN_CENTER);
		headerCenter.setBorderBottom(CellStyle.BORDER_THICK);
		headerCenter.setBorderLeft(CellStyle.BORDER_THIN);
		headerCenter.setBorderRight(CellStyle.BORDER_THIN);
		headerCenter.setBorderTop(CellStyle.BORDER_THIN);
		headerCenter.setFillForegroundColor(IndexedColors.PALE_BLUE.index);
		headerCenter.setFillPattern((short) 1);
		headerCenter.setFont(headerFont);

//产品信息批量导入表头
		headerCenterTypeIn = wb.createCellStyle();
		Font headerTypeInFont = wb.createFont();
		headerFont.setFontName("黑体");
		headerFont.setFontHeightInPoints((short) 12);
		headerCenterTypeIn.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headerCenterTypeIn.setAlignment(CellStyle.ALIGN_CENTER);
		headerCenterTypeIn.setBorderBottom(CellStyle.BORDER_THICK);
		headerCenterTypeIn.setBorderLeft(CellStyle.BORDER_THIN);
		headerCenterTypeIn.setBorderRight(CellStyle.BORDER_THIN);
		headerCenterTypeIn.setBorderTop(CellStyle.BORDER_THIN);
		headerCenterTypeIn.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.index);
		headerCenterTypeIn.setFillPattern((short) 1);
		headerCenterTypeIn.setFont(headerTypeInFont);

//产品信息批量导入表头
		headerCenterExpIn = wb.createCellStyle();
		Font headerExpFont = wb.createFont();
		headerFont.setFontName("黑体");
		headerFont.setFontHeightInPoints((short) 12);
		headerCenterExpIn.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headerCenterExpIn.setAlignment(CellStyle.ALIGN_CENTER);
		headerCenterExpIn.setBorderBottom(CellStyle.BORDER_THICK);
		headerCenterExpIn.setBorderLeft(CellStyle.BORDER_THIN);
		headerCenterExpIn.setBorderRight(CellStyle.BORDER_THIN);
		headerCenterExpIn.setBorderTop(CellStyle.BORDER_THIN);
		headerCenterExpIn.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.index);
		headerCenterExpIn.setFillPattern((short) 1);
		headerCenterExpIn.setFont(headerExpFont);

// 居中
		textCenter = wb.createCellStyle();
		textCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		textCenter.setAlignment(CellStyle.ALIGN_CENTER);
		textCenter.setBorderBottom(CellStyle.BORDER_THIN);
		textCenter.setBorderLeft(CellStyle.BORDER_THIN);
		textCenter.setBorderRight(CellStyle.BORDER_THIN);
		textCenter.setBorderTop(CellStyle.BORDER_THIN);

// 右对齐
		textRight = wb.createCellStyle();
		textRight.setAlignment(CellStyle.ALIGN_RIGHT);
		textRight.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		textRight.setBorderBottom(CellStyle.BORDER_THIN);
		textRight.setBorderLeft(CellStyle.BORDER_THIN);
		textRight.setBorderRight(CellStyle.BORDER_THIN);
		textRight.setBorderTop(CellStyle.BORDER_THIN);

// 左对齐(普通)
		textLeft = wb.createCellStyle();
		textLeft.setAlignment(CellStyle.ALIGN_LEFT);
		textLeft.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		textLeft.setBorderBottom(CellStyle.BORDER_THIN);
		textLeft.setBorderLeft(CellStyle.BORDER_THIN);
		textLeft.setBorderRight(CellStyle.BORDER_THIN);
		textLeft.setBorderTop(CellStyle.BORDER_THIN);

// 左对齐(Exp)
		Font telFont = wb.createFont();
		telFont.setFontName("微软雅黑");
		telFont.setFontHeightInPoints((short) 9);
		textExpLeft = wb.createCellStyle();
		textExpLeft.setAlignment(CellStyle.ALIGN_LEFT);
		textExpLeft.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		textExpLeft.setBorderBottom(CellStyle.BORDER_THIN);
		textExpLeft.setBorderLeft(CellStyle.BORDER_THIN);
		textExpLeft.setBorderRight(CellStyle.BORDER_THIN);
		textExpLeft.setBorderTop(CellStyle.BORDER_THIN);

//导出内容强制换行\t\n，必须设置单元格自动换行为true才能生效

		textExpLeft.setWrapText(true);
		textExpLeft.setFont(telFont);

// 左对齐(自动换行)
		textLeftWrap = wb.createCellStyle();
		textLeftWrap.setAlignment(CellStyle.ALIGN_JUSTIFY);
		textLeftWrap.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		textLeftWrap.setBorderBottom(CellStyle.BORDER_THIN);
		textLeftWrap.setBorderLeft(CellStyle.BORDER_THIN);
		textLeftWrap.setBorderRight(CellStyle.BORDER_THIN);
		textLeftWrap.setBorderTop(CellStyle.BORDER_THIN);

// 垂直居中和右对齐
		textCenterAndRight = wb.createCellStyle();
		textCenterAndRight.setAlignment(CellStyle.ALIGN_RIGHT);
		textCenterAndRight.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		textCenterAndRight.setBorderBottom(CellStyle.BORDER_THIN);
		textCenterAndRight.setBorderLeft(CellStyle.BORDER_THIN);
		textCenterAndRight.setBorderRight(CellStyle.BORDER_THIN);
		textCenterAndRight.setBorderTop(CellStyle.BORDER_THIN);

// 样式中附带格式化浮点数
		DataFormat dataFormat = wb.createDataFormat();
		textDataFormat = wb.createCellStyle();
		textDataFormat.setAlignment(CellStyle.ALIGN_RIGHT);
		textDataFormat.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		textDataFormat.setBorderBottom(CellStyle.BORDER_THIN);
		textDataFormat.setBorderLeft(CellStyle.BORDER_THIN);
		textDataFormat.setBorderRight(CellStyle.BORDER_THIN);
		textDataFormat.setBorderTop(CellStyle.BORDER_THIN);
		textDataFormat.setDataFormat(dataFormat.getFormat("0.###"));

	}

	/**
	 * <pre>
	 * 功能描述：初始化边框 使用示范： @param @return @throws
	 */
	private void initBorderStyle(CellStyle style) {
		style.setBorderBottom(CellStyle.BORDER_MEDIUM);
		style.setBorderLeft(CellStyle.BORDER_DASHED);
		style.setBorderRight(CellStyle.BORDER_DOUBLE);
		style.setBorderTop(CellStyle.BORDER_HAIR);
	}

	/**
	 * <pre>
	 * 功能描述：初始化表格单元格宽度 使用示范：2017-03-14的宽度大概是2500像素  @param @return @throws
	 */
	private void initUnitBorderSize(Sheet sheet) {
		int i = 0;
		sheet.setColumnWidth(i, 1500);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 6500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 5500);
		sheet.setColumnWidth(++i, 6500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 3000);
	}

	/**
	 * <pre>
	 * 功能描述：初始化单位单元格宽度 使用示范：2017-03-14的宽度大概是2500像素  @param @return @throws
	 */
	private void initUnitBorderSizeTypeIn(Sheet sheet) {
		int i = 0;
		sheet.setColumnWidth(i, 2000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 6500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 6500);
		sheet.setColumnWidth(++i, 5500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 4500);
	}

	/**
	 * <pre>
	 * 功能描述：初始化说明单元格宽度 使用示范：2017-03-14的宽度大概是2500像素  @param @return @throws
	 */
	private void initExpBorderSizeTypeIn(Sheet sheet) {
		sheet.setColumnWidth(0, 3000);
		sheet.setColumnWidth(1, 5500);
		sheet.setColumnWidth(2, 40000);

	}

	/**
	 * <pre>
	 * 功能描述：初始化信息单元格宽度 使用示范：2017-03-14的宽度大概是2500像素 @param @return @throws
	 */
	private void initInfoBorderSize(Sheet sheet) {
		int i = 0;
		sheet.setColumnWidth(i, 1500);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4500);
// sheet.setColumnWidth(20, 4500);
		sheet.setColumnWidth(++i, 4500);
// sheet.setColumnWidth(22, 4500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3000);
		sheet.setColumnWidth(++i, 3000);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 3000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 3500);
	}

	/**
	 * <pre>
	 * 功能描述：初始化产品批量导入表格单元格宽度 使用示范：2017-03-14的宽度大概是2500像素  @param @return @throws
	 */
	private void initInfoBorderSizeTypeIn(Sheet sheet) {
		int i = 0;
		sheet.setColumnWidth(i, 3500);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 5000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 4500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3500);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 4000);
		sheet.setColumnWidth(++i, 3000);
		sheet.setColumnWidth(++i, 3000);
	}

	/**
	 * <pre>
	 * 功能描述：初始化表头 使用示范： @param @return @throws
	 */
	private void initHeader(Sheet sheet, List<String> list, CellStyle style) {
		Row row = sheet.createRow(0);
		row.setHeightInPoints(30);
		for (int i = 0; i < list.size(); i++) {
			creatCell(row, i, list.get(i), style);
		}
	}

	/**
	 * <pre>
	 * 功能描述：创建单元格并赋值 使用示范： @param @return @throws
	 */
	public void creatCell(Row row, int index, Object value, CellStyle style) {
		Cell cell = row.createCell(index);
		cell.setCellValue(value.toString());
		cell.setCellStyle(style);
	}

	/**
	 * <pre>
	 * 功能描述：创建单元格并赋值 (sheet之间无法共用style，所以得用另外的格式设置方式) 使用示范： @param @return @throws
	 */
	public void creatCellCloneStyle(Row row, int index, Object value, CellStyle style) {
		Cell cell = row.createCell(index);
		cell.setCellValue(value.toString());
		cell.getCellStyle().cloneStyleFrom(style);
	}

	/**
	 * 根据文件名称创建Excel工作簿  
	 * 
	 * @param fileName文件名 @return 
	 */
	private Workbook createWorkbook(String fileName) {
		if (fileName.endsWith(".xls")) {
// 对于03~07版本的excel
			return new HSSFWorkbook();
		} else if (fileName.endsWith(".xlsx")) {
// 对于03~07版本以后版本的excel
			return new XSSFWorkbook();
		} else {
			return null;
		}
	}

//下标以前全部置空
	private void creatCellBeforeIndex(Row row, int index, CellStyle sty) {
		for (int i = 0; i < index; i++) {
			creatCell(row, i, "", sty);
		}
	}

//输出到指定文件夹
	private void fileOutToFile(Workbook wb, String fileName) {
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\" + fileName);
		} catch (FileNotFoundException e) {
			logger.info("新建文件出错" + e.getMessage());
		}
		try {
			wb.write(fos);
		} catch (IOException e) {
			logger.info("输出错误" + e.getMessage());
		}
		try {
			fos.close();
		} catch (IOException e) {
			logger.info("关闭fos流错误：" + e.getMessage());
		}
	}

	/**
	 * 填充批量导入产品模版的sheet0
	 */
	private void setInfoSheetForTypeIn(Workbook wb, List<Map> prodInfo) {
		Sheet sheet = wb.createSheet("产品信息");
// 构造表头
		initInfoBorderSizeTypeIn(sheet);
		List<String> list = new ArrayList<String>();
		list.add("序号");
		list.add("产品名称*");
		list.add("重量*");
		list.add("体型*");
		list.add("产品条码*");
		list.add("产品代码*");
		list.add("中文简称");
		list.add("英文简称");
		list.add("产品形态*");
		list.add("对应产品代码");
		list.add("跨级替换产品代码");
		list.add("产品属性*");
		list.add("品项*");
		list.add("部位模型");
		list.add("产品效益分级*");
		list.add("生产顺序*");
		list.add("生产工位*");
		list.add("生产级别分组*");
		list.add("生产周期*");
		list.add("可竞标*");
		list.add("库龄类型*");
		list.add("标准零售价*");
		list.add("税率");
		list.add("重点产品*");
		list.add("统计级");
		list.add("考核级");
		list.add("产品状态*");

		initHeader(sheet, list, headerCenterTypeIn);

	}

	/**
	 *      * 功能描述：填充产品批量导入模版的产品单位sheet1
	 */
	private void setUnitSheetForTypeIn(Workbook wb, List<ProductUnitExport> unitAll) {
		Sheet sheet = wb.createSheet("产品单位");
//构造表头
		List<String> list = new ArrayList<String>();
		list.add("序号");
		list.add("产品代码*");
		list.add("产品名称");
		list.add("转换为基本单位的分母");
		list.add("可选单位");
		list.add("可选单位汉语解释");
		list.add("转换为基本单位的分子");
		list.add("基本单位");
		list.add("平行计量单位类型");
		list.add("最小订货数量");
		list.add("最小订货增量");

		initHeader(sheet, list, headerCenterTypeIn);
		initUnitBorderSizeTypeIn(sheet);
		for (int i = 0; i < unitAll.size(); i++) {
			ProductUnitExport units = unitAll.get(i);
			Row row = sheet.createRow(i + 1);

// 设置行高
			row.setHeightInPoints(20);
			creatCellCloneStyle(row, 0, i + 1, textLeft);
			creatCellCloneStyle(row, 1, units.getProdCode() == null ? "" : units.getProdCode(), textLeft);
			creatCellCloneStyle(row, 2, units.getProdName() == null ? "" : units.getProdName(), textLeft);
			creatCellCloneStyle(row, 3, units.getxUnitValue() == null ? "" : units.getxUnitValue(), textLeft);
			creatCellCloneStyle(row, 4, units.getxUnit() == null ? "" : units.getxUnit(), textLeft);
			creatCell(row, 5, units.getxUnitExp() == null ? "" : units.getxUnitExp(), textLeft);
			creatCellCloneStyle(row, 6, units.getyUnitValue() == null ? "" : units.getyUnitValue(), textLeft);
			creatCellCloneStyle(row, 7, units.getyUnit() == null ? "" : units.getyUnit(), textLeft);
			if (Integer.parseInt(units.getUnitType()) == 2) {
				if (units.getSource() != null && "SAP".equals(units.getSource().toUpperCase())) {
					creatCellCloneStyle(row, 8, "A", textLeft);
				} else {
					creatCellCloneStyle(row, 8, "M", textLeft);
				}
			} else {
				creatCellCloneStyle(row, 8, "", textLeft);
			}
			creatCellCloneStyle(row, 9, units.getMinNum() == null ? "" : units.getMinNum(), textLeft);
			creatCellCloneStyle(row, 10, units.getMinNumAdd() == null ? "" : units.getMinNumAdd(), textLeft);
//客户取消这两列，暂时注掉
// creatCell(row, 10, units.getSource()==null? "":units.getSource(), textLeft);
		}
	}

	/**
	 *      * 功能描述：填充产品批量导入模版的说明sheet2
	 */
	private void setExplainSheetForTypeIn(Workbook wb) {
		Sheet sheet = wb.createSheet("字段解释");
		initExpBorderSizeTypeIn(sheet);
//表格全部有边框，先把边框给取消
		for (int i = 0; i < 100; i++) {
			sheet.createRow(i);
			for (int j = 0; j < 50; j++) {
				if (sheet.getRow(i) != null) {
					sheet.getRow(i).setHeightInPoints(20);
					sheet.getRow(i).createCell(j).setCellStyle(space);
					;
				}
			}
		}
		int i = 0;

		creatCell(sheet.getRow(++i), 1, "字段名", headerCenterExpIn);
		creatCell(sheet.getRow(i), 2, "数据导入时校验", headerCenterExpIn);

		creatCell(sheet.getRow(++i), 1, "序号", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "产品名称*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填项，数字、字母、文字及“（）”和“.”，长度的小于等于30字", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "重量*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，可选“A-E、无”", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "体型*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，可选“T、1-6、无”", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "产品条码*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填项，12位整数\r\n（只可以填写系统中已经存在的产品条码（因为导入只是对系统中已有的产品进行更新，非增加产品）", textExpLeft);
		creatCell(sheet.getRow(++i), 1, "", textExpLeft);
		creatCell(sheet.getRow(i), 2, "", textExpLeft);
		CellRangeAddress ca11 = new CellRangeAddress(i - 1, i, 1, 1);
		CellRangeAddress ca12 = new CellRangeAddress(i - 1, i, 2, 2);
		sheet.addMergedRegion(ca11);
		sheet.addMergedRegion(ca12);

		creatCell(sheet.getRow(++i), 1, "产品代码*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填项，数字、字母及“.”，长度小于20字\r\n（只可以填写系统中已经存在的产品代码（因为导入只是对系统中已有的产品进行更新，非增加产品）",
				textExpLeft);
		creatCell(sheet.getRow(++i), 1, "", textExpLeft);
		creatCell(sheet.getRow(i), 2, "", textExpLeft);
		CellRangeAddress ca21 = new CellRangeAddress(i - 1, i, 1, 1);
		CellRangeAddress ca22 = new CellRangeAddress(i - 1, i, 2, 2);
		sheet.addMergedRegion(ca21);
		sheet.addMergedRegion(ca22);

		creatCell(sheet.getRow(++i), 1, "中文简称", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填，数字、字母、文字及“（）”和“.”，长度的小于等于30字", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "英文简称", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填，字母，长度小于等于15", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "产品形态*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，“鲜品”或“冻品”", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "对应产品代码", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填，注意：所提供数据中存在该产品代码", textExpLeft);
		creatCell(sheet.getRow(++i), 1, "", textExpLeft);
		creatCell(sheet.getRow(i), 2, "如原产品的“产品形态”为“鲜品”，此“对应产品代码”字段不可输入；（如输入则不生效）", textExpLeft);
		creatCell(sheet.getRow(++i), 1, "", textExpLeft);
		creatCell(sheet.getRow(i), 2, "如原产品的“产品形态”为“冻品”，此“对应产品代码”字段为必填项；", textExpLeft);
		CellRangeAddress ca31 = new CellRangeAddress(i - 2, i, 1, 1);
		sheet.addMergedRegion(ca31);

		creatCell(sheet.getRow(++i), 1, "跨级替换产品代码", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填，填写系统已有的产品代码，多个用英文“,”隔开", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "产品属性*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，“全部”或“出厂商品”或“零售商品”", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "品项*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，可选“片肉|四分体”、“副产品”、“前段”、“中段”、“后段”、“肉”、“骨”、“膘皮”、“精加工”、“小包装”;", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "部位模型", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填，可录入多个，使用英文“；”隔开,“一号部位”、“二号部位”、“三号部位”、“四号部位”、“五花部位”;", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "产品效益分级*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，1-8，数字整数", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "生产顺序*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，1-20，数字整数", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "生产工位*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，可录入多个，使用英文“；”隔开,格式为“生产车间”+“生产工位号”", textExpLeft);
		creatCell(sheet.getRow(++i), 1, "", textExpLeft);
		creatCell(sheet.getRow(i), 2, "*生产车间，数字整数，1-20；", textExpLeft);
		creatCell(sheet.getRow(++i), 1, "", textExpLeft);
		creatCell(sheet.getRow(i), 2, "*生产工位号，数字整数，1-100；", textExpLeft);
		CellRangeAddress ca41 = new CellRangeAddress(i - 2, i, 1, 1);
		sheet.addMergedRegion(ca41);

		creatCell(sheet.getRow(++i), 1, "生产级别分组*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，格式为“生产线”+“生产分类”，生产线和生产分类选项来自系统", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "转换为基本单位的分母", textExpLeft);
		creatCell(sheet.getRow(i), 2, "不可输入仅显示", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "可选单位", textExpLeft);
		creatCell(sheet.getRow(i), 2, "不可输入仅显示", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "可选单位汉语解释", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填项；从SAP中得到的单位允许被修改和填写该值；长度限制为8位；", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "转换为基本单位的分子", textExpLeft);
		creatCell(sheet.getRow(i), 2, "不可输入仅显示", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "基本单位", textExpLeft);
		creatCell(sheet.getRow(i), 2, "不可输入仅显示", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "平行计量单位类型", textExpLeft);
		creatCell(sheet.getRow(i), 2, "不可输入仅显示", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "最小订货数量", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填，支持整数，范围1-9999", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "最小订货增量", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填，支持整数，范围1-9999", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "生产周期*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，整数，0-8，多个发货周期，用逗号分隔", textExpLeft);
		creatCell(sheet.getRow(++i), 1, "", textExpLeft);
		creatCell(sheet.getRow(i), 2, "注意：鲜品可支持多个，冻品仅支持一个", textExpLeft);
		CellRangeAddress ca51 = new CellRangeAddress(i - 1, i, 1, 1);
		sheet.addMergedRegion(ca51);

		creatCell(sheet.getRow(++i), 1, "可竞标*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，“是”或“否”", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "库龄类型*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，库龄类型，即保质期。系统中的库龄类型名称", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "标准零售价*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，数字，保留2位小数", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "税率", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填，数字，保留3位小数", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "重点产品*", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，“是”或“否”", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "产品状态", textExpLeft);
		creatCell(sheet.getRow(i), 2, "必填，填“正常”，或“停止销售”", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "统计级", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填项；(统计级来自产品信息导入，预留字段作为业务部门统计使用)", textExpLeft);

		creatCell(sheet.getRow(++i), 1, "考核级", textExpLeft);
		creatCell(sheet.getRow(i), 2, "非必填项；(考核级来自产品信息导入，预留字段作为业务部门统计使用)", textExpLeft);

	}

}
小白Alan
关注
0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
POI导出Excel代码实例（设置字体，边框，合并单元格，其它功能以后探索到继续更新）

package com.fbi.common.service.production;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.List;impor...
复制链接

扫一扫
专栏目录