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);
}
}