package zj.excel.util;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
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 zj.check.util.CheckUtil;
import zj.date.util.DateUtil;
import zj.excel.bean.ExcelI;
import zj.excel.bean.ExcelTemplate;
import zj.excel.bean.FormulaTemplate;
import zj.excel.bean.WriteTemplateExcelI;
import zj.io.util.FileUtil;
import zj.java.bean.ZjIterator;
import zj.java.util.JavaUtil;
import zj.reflect.util.FieldUtil;
import zj.regex.util.RegexUtil;
import zj.type.TypeUtil;
/**
* 概况 ：Excel/xls/xlsx<br>
*
* @version 1.00 （2011.12.02）
* @author SHNKCS 张军 {@link <a target=_blank href="http://www.shanghaijiadun.com">上海加盾信息科技有限公司</a> <a target=_blank href="http://www.zhangjunbk.com">张军个人网站</a> <a target=_blank href="http://user.qzone.qq.com/360901061/">张军QQ空间</a>} <br>
* * <br>
* --------------------------读取excel数据------------------------------<br>
*
* <pre>
* try {
* Excel readExcel = new Excel();
* readExcel.setFilePath("E:/document/zj-utils/excel/写入excel" + "1" + ".xls");
* // readExcel.setSheetValue(new String[] { "Sheet1" });
* DatasKeySheets datasKeySheets = null;
* // RE re = new RE();
* // datasKeySheets = ExcelUtil.readExcel(readExcel,re);
* datasKeySheets = ExcelUtil.readExcel(readExcel);
* List<SheetDatas> sheetDatas = datasKeySheets.getSheetDatas();
* for (SheetDatas datas : sheetDatas) {
* // =======================
* System.out.println("sheet:" + datas.getSheetIndex() + "\t\t" + datas.getSheetName() + "\t\t" + datas.getSheet());
* // 循环sheet
* // 获取某个sheet的行列数据
* List<List<SheetData>> datasLst = datas.getRowsDataLst();
* if (datasLst != null) {
* // 循环某个sheet的行数据
* for (List<SheetData> dataLst : datasLst) {
* // 循环某个sheet的列数据
* for (SheetData data : dataLst) {
* if (data != null) {
* System.out.print(data.getValue() + "\t\t");
* }
* }
* System.out.println();
* }
* }
* }
* } catch (Exception e) {
* e.printStackTrace();
* }
* </pre>
*
* <pre>
* <br>------------------------------导出excel数据------------------------------<br>
* try {
* // 设置数据
* for (int ii = 0; ii < 10; ii++) {
* List<SheetDatas> sheetDatas = new ArrayList<SheetDatas>();
* for (int k = 0; k < 10; k++) {
* // 所有行列数据对象
* SheetDatas sheetData = new SheetDatas();
* sheetData.setSheetName("sheet名." + k);
* sheetDatas.add(sheetData);
* // 所有行数据
* List<List<SheetData>> rowsDataLst = new ArrayList<List<SheetData>>();
* // 设置所有行数据
* sheetData.setRowsDataLst(rowsDataLst);
* // 所有列数据
* List<SheetData> columnsDataLst = null;
* // 所有单元格数据
* SheetData data = null;
* // 实例化所有行列数据
* for (int i = 0; i < 10000; i++) {
* // 设置第i行数据
* columnsDataLst = new ArrayList<SheetData>();
* rowsDataLst.add(columnsDataLst);
* // 添加第j行数据
* for (int j = 0; j < 10; j++) {
* data = new SheetData();
* if (j == 5) {
* data.setValue(i * j);
* } else {
* data.setValue("行" + i + "列" + j);
* }
* columnsDataLst.add(data);
* }
* }
* }
* // 导出excel设置
* Excel excel = new Excel();
* excel.setFilePath("E:/document/zj-utils/excel/写入excel" + ii + ".xls");
* ExcelUtil.writeExcel(sheetDatas, excel);
* }
* } catch (Exception e) {
* e.printStackTrace();
* }
* </pre>
*/
public final class ExcelWriteUtil implements Serializable {
private static final long serialVersionUID = 1L;
private static final Logger logger = Logger.getLogger(ExcelWriteUtil.class.getName());
private ExcelWriteUtil() {
}
/**
* 读取excel
*
* @param excel
* excel对象
* @return 所有行列数据对象
* @throws Exception
*/
public static final void writeTemplateExcel(final ExcelTemplate excel) throws Exception {
writeTemplateExcel(excel, null);
}
/**
* 读取excel
*
* @param excel
* excel对象
* @return 所有行列数据对象
* @throws Exception
*/
public static final synchronized void threadWriteTemplateExcel(final ExcelTemplate excel) throws Exception {
threadWriteTemplateExcel(excel, null);
}
/**
* 读取excel
*
* @param excel
* excel对象
* @param excelI
* excelI接口
* @return 所有行列数据对象
* @throws Exception
*/
public static final void writeTemplateExcel(final ExcelTemplate excel, final ExcelI excelI) throws Exception {
final long start = System.currentTimeMillis();
Set<Sheet> sheets = ExcelReadUtil.readSheets(excel);
writeTemplateSheet(excel, excelI, start, sheets);
}
/**
* 读取excel
*
* @param excel
* excel对象
* @param excelI
* excelI接口
* @return 所有行列数据对象
* @throws Exception
*/
public static final synchronized void threadWriteTemplateExcel(final ExcelTemplate excel, final ExcelI excelI) throws Exception {
final long start = System.currentTimeMillis();
Set<Sheet> sheets = ExcelReadUtil.readSheets(excel);
threadWriteSheet(excel, excelI, start, sheets);
}
/**
* 非线程写入
*
* @param excel
* @param excelI
* @param start
* @param wb
* @param sheets
* @throws Exception
*/
private static void writeTemplateSheet(final ExcelTemplate excel, final ExcelI excelI, final long start, Set<Sheet> sheets) throws Exception {
if (sheets.size() > 0) {
// boolean writeFile = false;
// for (Sheet tempSheet : sheets) {
// boolean tempWriteFile = writeTemplateSheet(tempSheet, excel, excelI);
// if (!writeFile) {
// writeFile = tempWriteFile;
// }
// }
// if (writeFile) {
// writeFile(excel);
// } else {
// logger.debug("未找到模板内容与传入内容表达式关系");
// }
for (Sheet tempSheet : sheets) {
// 强制执行excel公式
tempSheet.setForceFormulaRecalculation(true);
writeTemplateSheet(tempSheet, excel, excelI);
try {
List<Map<ConstantForEnum.MergedRegionKey, Integer>> mergedRegions = excel.mergerRegionsMap.get(tempSheet.getSheetName());
if (mergedRegions != null) {
for (Map<ConstantForEnum.MergedRegionKey, Integer> mergedRegionMap : mergedRegions) {
Integer firstR = -1;
Integer lastR = -1;
Integer firstC = -1;
Integer lastC = -1;
String errorArea = "区域行[" + firstR + "-" + lastR + "]列[" + firstC + "-" + lastC + "]";
try {
firstR = mergedRegionMap.get(ConstantForEnum.MergedRegionKey.FIRSTR);
lastR = mergedRegionMap.get(ConstantForEnum.MergedRegionKey.LASTR);
firstC = mergedRegionMap.get(ConstantForEnum.MergedRegionKey.FIRSTC);
lastC = mergedRegionMap.get(ConstantForEnum.MergedRegionKey.LASTC);
errorArea = "区域行[" + firstR + "-" + lastR + "]列[" + firstC + "-" + lastC + "]";
if (firstR == null || lastR == null || firstC == null || lastC == null || firstR < 0 || lastR < 0 || firstC < 0 || lastC < 0) {
// 合并下个区域
logger.warn("合并" + errorArea + "参数设置有误:" + mergedRegionMap.entrySet());
continue;
}
// 循环区域开始合并
ExcelUtil.mergerRegion(tempSheet, firstR, lastR, firstC, lastC);
} catch (Exception e) {
logger.error("合并" + errorArea + "出错,继续合并下个区域单元格", e);
}
}
}
} catch (Exception e1) {
logger.error("合并单元格时出错", e1);
}
// 改变sheet样式
// // 生成一个字体
// for (int i = 2; i < 6; i ++){
// try {
// Font font = excel.getWb().createFont();
// font.setColor(HSSFColor.RED.index);// HSSFColor.VIOLET.index //字体颜色
// // 把字体应用到当前的样式
// CellStyle nStyle = excel.getWb().createCellStyle();
// Cell cell = tempSheet.getRow(i).getCell(2);
// CellStyle oStyle = cell.getCellStyle();
// // nStyle.cloneStyleFrom(oStyle);
// // nStyle.setFont(font);
// // cell.setCellStyle(nStyle);
// oStyle.setFont(font);
// cell.setCellStyle(oStyle);
// } catch (Exception e) {
// e.printStackTrace();
// }
// }
// 生成一个字体
try {
if (excelI instanceof WriteTemplateExcelI) {
((WriteTemplateExcelI) excelI).afterService(tempSheet, excel.jsonParamsMap.get(tempSheet.getSheetName()));
}
} catch (Exception e) {
logger.error("回调后处理时出错", e);
}
}
if (excel.isAutoWriteExcel()) {
writeTemplateFile(excel);
}
}
final long end = System.currentTimeMillis();
final long x = (end - start) / 1000;
final long y = (end - start) % 1000;
logger.warn("根据excel模板导出执行时间:" + (end - start) + "毫秒;" + x + "." + (y < 100 ? "0" + y : y) + "秒");
}
/**
* 线程写入
*
* @param excel
* @param excelI
* @param start
* @param wb
* @param sheets
*/
private static void threadWriteSheet(final ExcelTemplate excel, final ExcelI excelI, final long start, Set<Sheet> sheets) {
if (sheets.size() > 0) {
final Workbook wb = excel.getWb();
List<Sheet> removeSheets = new ArrayList<Sheet>();
// 删除无用的sheet
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
// 获得sheet工作簿Sheet
Sheet sheet = wb.getSheetAt(sheetIndex);
if (!sheets.contains(sheet)) {
removeSheets.add(sheet);
}
}
for (Sheet removeSheet : removeSheets) {
wb.removeSheetAt(wb.getSheetIndex(removeSheet));
}
final ExecutorService ee = Executors.newCachedThreadPool();
final CountDownLatch latch = new CountDownLatch(sheets.size());
for (final Sheet tempSheet : sheets) {
ee.execute(new Runnable() {
@Override
public void run() {
try {
writeTemplateSheet(tempSheet, excel, excelI);
} catch (Exception e) {
e.printStackTrace();
}
// try {
// TimeUnit.SECONDS.sleep(new Random().nextInt(1));
// } catch (InterruptedException e1) {
// e1.printStackTrace();
// }
latch.countDown();
// boolean tempWriteFile = writeTemplateSheet(tempSheet, excel, excelI);
// if (!writeFile) {
// writeFile = tempWriteFile;
// }
}
});
}
ee.execute(new Runnable() {
@Override
public void run() {
try {
latch.await();
} catch (InterruptedException e1) {
e1.printStackTrace();
}
try {
if (excel.isAutoWriteExcel()) {
writeTemplateFile(excel);
}
} catch (Exception e) {
e.printStackTrace();
}
long end = System.currentTimeMillis();
long x = (end - start) / 1000;
long y = (end - start) % 1000;
logger.warn("根据excel模板导出执行时间:" + (end - start) + "毫秒;" + x + "." + (y < 100 ? "0" + y : y) + "秒");
}
});
ee.shutdown();
}
}
/**
* 读取sheet行列集合
*
* @param sheet
* @param excel
* @param excelI
* @throws Exception
*/
@SuppressWarnings("unchecked")
public static final synchronized boolean writeTemplateSheet(final Sheet sheet, final ExcelTemplate excel, final ExcelI excelI) throws Exception {
// 设置写文件状态:true[写],false[不写]
boolean writeFile = false;
Set<String> nameCollKeys = new HashSet<String>();
WriteTemplateExcelI wTemplateExcelI = null;
if (excelI != null) {
wTemplateExcelI = (WriteTemplateExcelI) excelI;
}
int endRowIndex = wTemplateExcelI == null ? ExcelI.END_ROW_INDEX : wTemplateExcelI.getEndRowIndex();
int endColumnIndex = wTemplateExcelI == null ? ExcelI.END_COLUMN_INDEX : wTemplateExcelI.getEndColumnIndex();
// 获得sheet总行数
// 循环sheet中的所有行数据
for (int rowIndex = wTemplateExcelI == null ? ExcelI.START_ROW_INDEX : wTemplateExcelI.getStartRowIndex(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
// 是否结束
if (endRowIndex != -1 && rowIndex > endRowIndex)
break;
if (wTemplateExcelI != null && wTemplateExcelI.filterRow(sheet, rowIndex)) {
// ("sheetName:" + sheet.getSheetName() + ",第" + rowIndex + "行数据已被过虑");
continue;
}
// 行数据
// 获得行对象
Row row = sheet.getRow(rowIndex);
if (null != row) {
// 获得本行中单元格的个数
// 遍历列cell
for (int columnIndex = wTemplateExcelI == null ? 0 : wTemplateExcelI.getStartColumnIndex(); columnIndex < row.getLastCellNum(); columnIndex++) {
// 是否结束
if (endColumnIndex != -1 && columnIndex > endColumnIndex)
break;
Cell cell = row.getCell(columnIndex);
// 获得指定单元格中的数据
Object value = ExcelReadUtil.readCellString(cell);
// 如果未实现,则取默认值
value = wTemplateExcelI == null ? value : wTemplateExcelI.readValue(sheet, rowIndex, columnIndex, value);
if (value instanceof String) {
// 只获取String的字符串
String svalue = "";
// 取得表达式串
Map<String, String> valueMap = RegexUtil.fillString(svalue = JavaUtil.objToStr(value));
// 取得所有占位符名
String names = valueMap.get(RegexUtil.FillString.KEY_PLACEHOLDER_NAMES);
if (CheckUtil.isNotNull(names)) {
// 取得占位符
String places = valueMap.get(RegexUtil.FillString.KEY_PLACEHOLDER);
// 如果不是单纯的表达式,例如:其它字符+表达式,则可能是日期格式,数字,或其它格式,需要格式化
boolean isOne = svalue.equals(places);
// 多个占位符数组
String[] nameAry = names.split(RegexUtil.FillString.KEY_PLACEHOLDER_NAMES_SPLIT);
if (nameAry.length > 0) {
// 循环所有占位符名
for (String name : nameAry) {
if ("".equals(name)) {
continue;
}
// 记录索引
int index = -1;
if ((index = name.indexOf(".")) == -1) {
// 根据占位符取得占位符值
Object ovalue = excel.getNameValueMap().get(name);
if (ovalue instanceof FormulaTemplate) {
// 如果是公式
FormulaTemplate ft = (FormulaTemplate) ovalue;
// 改变单元格值
ExcelUtil.setCellValue(sheet, row, cell, rowIndex, columnIndex, ovalue, null);
// 赋值改变后的值
svalue = ft.value;
// 设置写文件状态
writeFile = true;
} else if (ovalue instanceof Collection || ovalue instanceof Object[]) {
// 复制列
// 如果值是集合对象
ZjIterator baseColl = ZjIterator.newIterator(ovalue);
int tempRowCount = baseColl.size();
if (tempRowCount > 0) {
boolean isRowColData = false;
Object firstObj = baseColl.next();
baseColl.reset();
isRowColData = firstObj instanceof Collection || firstObj instanceof Object[];
if (isRowColData) {
int tempRowIdx = rowIndex;
// 处理行列数据
// 添加新行时拷贝原有值(默认)
ExcelUtil.insertRows(excel, sheet, tempRowIdx, tempRowCount - 1, excel.isCopyDefaultValue());
// 循环赋值
for (Object objCol : baseColl) {
// 如果值是集合对象
ZjIterator collValue = ZjIterator.newIterator(objCol);
// 如果值是集合对象
Row rowTemp = sheet.getRow(tempRowIdx);
if (rowTemp == null) {
// 如果不存在,则实例化
rowTemp = sheet.createRow(tempRowIdx);
}
int tempColIdx = columnIndex;
for (Object cellValue : collValue) {
// 获取当前单元格式列
Cell cellTemp = rowTemp.getCell(tempColIdx);
if (cellTemp == null) {
// 如果不存在,则实例化
cellTemp = rowTemp.createCell(tempColIdx);
}
// 复制单元格
ExcelUtil.copyCell(excel, cell, cellTemp, false, true, true);
// 设置值
ExcelUtil.setCellValue(sheet, rowTemp, cellTemp, tempRowIdx, tempColIdx, cellValue, null);
tempColIdx++;
}
tempRowIdx++;
}
} else {
int tempRowIdx = rowIndex;
int tempColIdx = columnIndex;
for (Object objCol : baseColl) {
// 获取当前单元格式列
Cell cellTemp = row.getCell(tempColIdx);
if (cellTemp == null) {
// 如果不存在,则实例化
cellTemp = row.createCell(tempColIdx);
}
// 复制单元格
ExcelUtil.copyCell(excel, cell, cellTemp, false, true, true);
// 设置值
ExcelUtil.setCellValue(sheet, row, cellTemp, tempRowIdx, tempColIdx, objCol, null);
tempColIdx++;
}
}
// 设置写文件状态
writeFile = true;
}
} else {
String placeValue = JavaUtil.objToStr(ovalue);
Object newObjValue = null;
if (!excel.getNameValueMap().containsKey(name) && CheckUtil.isNull(placeValue)) {
// 通过name取值
if (wTemplateExcelI == null) {
// 如果没有实现,则继续下个单元格
continue;
}
try {
newObjValue = wTemplateExcelI.readTemplateValue(sheet, rowIndex, columnIndex, name);
} catch (Exception e) {
// 如果抛出异常,则继续下个单元格
continue;
}
} else {
// 实例替换占位符值的集体
Map<String, String> sNameValueMapTemp = new HashMap<String, String>();
// 设置占位符名对应的占位符值
sNameValueMapTemp.put(name, placeValue);
// 获取替换结果
Map<String, String> resultNameValueMap = RegexUtil.fillString(svalue, sNameValueMapTemp);
// 获取替换后的字符串
String newValue = resultNameValueMap.get(RegexUtil.FillString.KEY_NEW_VALUE);
if (placeValue.equals(newValue)) {
newObjValue = ovalue;
} else {
newObjValue = newValue;
}
// 赋值改变后的值
svalue = newValue;
}
// 改变单元格值
ExcelUtil.setCellValue(sheet, row, cell, rowIndex, columnIndex, newObjValue, null);
// 设置写文件状态
writeFile = true;
}
} else {
int firstC = 0;
int lastC = 0;
int firstR = 0;
int lastR = 0;
int flr = 0;
// 取得占位符前缀
String prefixName = name.substring(0, index);
// 取得属性名
String propertyName = name.substring(index + 1);
// 获取集合引用对象
String nameKey = excel.getCollectionKey(prefixName);
// 取得nameKey对应的集合数据
Object nameObj = excel.getNameValueMap().get(nameKey);
if (nameObj != null) {
// 是否是合并单元格
boolean isMerged = false;
// 设置临时行变量
int rowIndexTemp = rowIndex;
// 获取合并的单元格
CellRangeAddress ca = ExcelReadUtil.readMergedRegion(sheet, rowIndex, columnIndex);
if (ca != null) {
// 获得合并单元格的起始行, 结束行, 起始列, 结束列, 行数
firstC = ca.getFirstColumn();
lastC = ca.getLastColumn();
firstR = ca.getFirstRow();
lastR = ca.getLastRow();
isMerged = true;
flr = lastR - firstR;
}
if (nameObj instanceof Collection) {
// 如果值是集合对象
Collection<Object> coll = (Collection<Object>) nameObj;
if (coll != null && coll.size() > 0) {
// 设置此属性是否是插入行
String sAutoInsertRows = excel.getPropertyKey(prefixName, ExcelTemplate.PROPERTY_AUTO_INSERT_ROWS);
boolean autoInsertRows = true;
if (CheckUtil.isNotNull(sAutoInsertRows)) {
Object oAutoInsertRows = excel.getNameValueMap().get(sAutoInsertRows);
if (oAutoInsertRows != null) {
autoInsertRows = TypeUtil.Primitive.booleanValue(oAutoInsertRows);
}
}
if (autoInsertRows) {
String nameCollKey = nameKey + ":" + rowIndex;
// 判断只插入一次新行（循环所有的行数据）
if (!nameCollKeys.contains(nameCollKey)) {
// 添加新行时拷贝原有值(默认)
ExcelUtil.insertRows(excel, sheet, rowIndex, coll.size() - 1, excel.isCopyDefaultValue());
nameCollKeys.add(nameCollKey);
}
}
String tempSvalue = svalue;
int collIndex = 0;
int collSize = coll.size();
// 如果存在
int firstIndex = 1;
for (Object obj : coll) {
// 循环所有行数据
// 获取当前单元格式行
Row rowTemp = sheet.getRow(rowIndexTemp);
if (rowTemp == null) {
// 如果不存在,则实例化
rowTemp = sheet.createRow(rowIndexTemp);
}
// 获取当前单元格式列
Cell cellTemp = rowTemp.getCell(columnIndex);
if (cellTemp == null) {
// 如果不存在,则实例化
cellTemp = rowTemp.createCell(columnIndex);
}
// 复制单元格
ExcelUtil.copyCell(excel, cell, cellTemp, false, true, true);
// 取得属性值
Object fieldObjValue = null;
try {
if (obj instanceof Map) {
Map<String, Object> objMap = (Map<String, Object>) obj;
fieldObjValue = objMap.get(propertyName);
} else {
fieldObjValue = FieldUtil.get(obj, propertyName, true);
}
} catch (Exception e) {
fieldObjValue = "";
logger.error("获取对象值异常,默认空", e);
}
if (isOne) {
} else {
// 非String
String convertStr = "";
try {
if (fieldObjValue instanceof String) {
convertStr = JavaUtil.objToStr(fieldObjValue);
} else if (fieldObjValue instanceof Date || fieldObjValue instanceof Calendar || fieldObjValue instanceof Timestamp) {
convertStr = DateUtil.dateParse(fieldObjValue, "yyyy-MM-dd HH:mm:ss");
// 日期格式化成字符串
} else if (fieldObjValue instanceof Boolean) {
convertStr = JavaUtil.objToStr(fieldObjValue);
} else if (fieldObjValue instanceof Number) {
// 数字
convertStr = JavaUtil.objToStr(fieldObjValue);
} else {
convertStr = JavaUtil.objToStr(fieldObjValue);
}
} catch (Exception e) {
convertStr = "";
logger.error("转换值出错,默认空", e);
}
// 如果值是String类型
// 实例替换占位符值的集体
Map<String, String> sNameValueMapTemp = new HashMap<String, String>();
// 设置占位符名对应的占位符值
sNameValueMapTemp.put(name, convertStr);
// 获取替换结果
Map<String, String> resultNameValueMap = RegexUtil.fillString(tempSvalue, sNameValueMapTemp);
// 获取替换后的字符串
fieldObjValue = resultNameValueMap.get(RegexUtil.FillString.KEY_NEW_VALUE);
if (firstIndex == 1) {
firstIndex++;
// 赋值改变后的值
svalue = JavaUtil.objToStr(fieldObjValue);
}
}
// 是否根据目标单元格类型转换值
// if (excel.isAutoConvertValue()) {
// fieldObjValue = ExcelUtil.convertValueByDestCell(cellTemp, fieldObjValue);
// }
ExcelUtil.setCellValue(sheet, rowTemp, cellTemp, rowIndexTemp, columnIndex, fieldObjValue, null);
if (isMerged) {
// XXX 复制合并单元格(需要改)
for (int r = firstR; r <= lastR; r++) {
// 获取当前单元格式行
Row rowTempStyle = sheet.getRow(r);
if (rowTempStyle == null) {
// 如果不存在,则实例化
rowTempStyle = sheet.createRow(r);
}
for (int c = firstC; c <= lastC; c++) {
// 获取当前单元格式列
Cell cellTempStyle = rowTempStyle.getCell(c);
if (cellTempStyle == null) {
// 如果不存在,则实例化
cellTempStyle = rowTempStyle.createCell(c);
}
ExcelUtil.copyCell(excel, cell, cellTempStyle, false, true, true);
// ExcelUtil.copyCellStyle(cell.getCellStyle(), cellTempStyle.getCellStyle());
}
}
}
if (collIndex < collSize - 1) {
// 如果最后一条数据,则无需合并单元格
if (isMerged) {
// 行循环
// 继续下一行
firstR = lastR + 1;
lastR = firstR + flr;
// 合并单元格
ExcelUtil.mergerRegion(sheet, firstR, lastR, firstC, lastC);
rowIndexTemp = firstR;
} else {
// 继续下一行
rowIndexTemp++;
}
}
collIndex++;
}
// 设置写文件状态
writeFile = true;
} else {
ExcelUtil.setCellValue(sheet, row, cell, rowIndex, columnIndex, null, null);
// 设置写文件状态
writeFile = true;
}
}
}
}
}
}
}
}
}
}
}
return writeFile;
}
/**
* 写文件
*/
public static final void writeTemplateFile(final ExcelTemplate excel) throws Exception {
Workbook wb = excel.getWb();
try {
List<Sheet> removeSheets = new ArrayList<Sheet>();
// for (String name : excel.addedSheets) {
// System.out.println(name);
// }
// System.out.println("==================");
// for (String name : excel.templateSheets) {
// System.out.println(name);
// }
// System.out.println("---------------------");
// 删除无用的sheet
if (excel.isRemoveOtherSheets()) {
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
// 获得sheet工作簿Sheet
Sheet sheet = wb.getSheetAt(sheetIndex);
String name = sheet.getSheetName();
// 除了模板和新增sheet,其它全部移除
if (!(excel.addedSheets.contains(name) || excel.templateSheets.contains(name))) {
removeSheets.add(sheet);
}
}
for (Sheet removeSheet : removeSheets) {
// System.out.println(removeSheet.getSheetName());
// 删除其它sheet及模板sheet
wb.removeSheetAt(wb.getSheetIndex(removeSheet));
}
}
if (excel.isRemoveTemplatesSheets()) {
// 移除模板sheet
for (String name : excel.templateSheets) {
Sheet removeSheet = wb.getSheet(name);
// System.out.println(removeSheet.getSheetName());
wb.removeSheetAt(wb.getSheetIndex(removeSheet));
}
}
} catch (Exception e) {
e.printStackTrace();
}
BufferedOutputStream bos = null;
try {
String[] paths = FileUtil.getFileNameExtension(excel.getReFilePath());
File file = null;
if ("false".equals(paths[3])) {
// 文件夹
file = new File(paths[0]);
if (!file.exists()) {
file.mkdirs();
}
}
// 文件
file = new File(paths[0] + paths[1] + paths[2]);
bos = new BufferedOutputStream(new FileOutputStream(file));
// 设置默认活动的sheet为第一个
// wb.setSelectedTab(0);
wb.setActiveSheet(0);
wb.write(bos);
bos.flush();
// logger.debug("写入模板文件成功");
} finally {
if (bos != null) {
bos.close();
}
if (wb != null) {
wb.close();
wb = null;
}
}
}
}
本文为张军原创文章,转载无需和我联系,但请注明来自张军的军军小站,个人博客http://www.zhangjunbk.com
