import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationHelper;
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.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.xwtech.mnoframework.admin.common.model.HandlerResult;
import com.xwtech.mnoframework.admin.common.model.excel.ExcelCell;
import com.xwtech.mnoframework.admin.common.model.excel.ExcelSheet;
import com.xwtech.mnoframework.admin.common.model.excel.ExcelWorkbook;
import com.xwtech.mnoframework.admin.constants.IResultCode;
import com.xwtech.mnoframework.admin.constants.SystemCodeConstants;
/**
* <Excel导入导出工具类>
* @since 2014-04-23 add method to export complex excel from template file
* @see [相关类/方法]
*/
public class ExcelUtil
{
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
/**
* MAP_KEY_SUC map key值 对应 验证成功数据集
*/
public static final String MAP_KEY_SUC = "MAP_KEY_SUC";
/**
* MAP_KEY_ERR map key值 对应 验证失败数据集
*/
public static final String MAP_KEY_ERR = "MAP_KEY_ERR";
/**
* MAP_KEY_ERR_INFO map key 对应 验证结果失败信息
*/
public static final String MAP_KEY_ERR_INFO = "MAP_KEY_ERR_INFO";
/**
*
* MAP_KEY_1:单元格数据
* */
public static final String MAP_KEY_1 = "MAP_KEY_1";
/**
* MAP_KEY_2:验证结果 true ? false
*/
public static final String MAP_KEY_2 = "MAP_KEY_2";
/**
* MAP_KEY_3:验证失败信息
*/
public static final String MAP_KEY_3 = "MAP_KEY_3";
/**
* 默认的开始读取的行位置为第一行（索引值为0）
*/
private final static int READ_START_POS = 0;
/**
* 默认结束读取的行位置为最后一行（索引值=0，用负数来表示倒数第n行）
*/
private final static int READ_END_POS = 0;
/**
* 默认Excel内容的开始比较列位置为第一列（索引值为0）
*/
private final static int COMPARE_POS = 0;
/**
* 默认多文件合并的时需要做内容比较（相同的内容不重复出现）
*/
private final static boolean NEED_COMPARE = true;
/**
* 默认多文件合并的新文件遇到名称重复时，进行覆盖
*/
private final static boolean NEED_OVERWRITE = true;
/**
* 默认只操作一个sheet
*/
private final static boolean ONLY_ONE_SHEET = true;
/**
* 默认读取第一个sheet中（只有当ONLY_ONE_SHEET = true时有效）
*/
private final static int SELECTED_SHEET = 0;
/**
* 默认从第一个sheet开始读取（索引值为0）
*/
private final static int READ_START_SHEET = 0;
/**
* 默认在最后一个sheet结束读取（索引值=0，用负数来表示倒数第n行）
*/
private final static int READ_END_SHEET = 0;
/**
* 默认打印各种信息
*/
private final static boolean PRINT_MSG = true;
/**
* Excel文件路径
*/
private String EXCELPATH = "data.xlsx";
/**
* 设定开始读取的位置，默认为0
*/
private int STARTREADPOS = READ_START_POS;
/**
* 设定结束读取的位置，默认为0，用负数来表示倒数第n行
*/
private int ENDREADPOS = READ_END_POS;
/**
* 设定开始比较的列位置，默认为0
*/
private int COMPAREPOS = COMPARE_POS;
/**
* 设定汇总的文件是否需要替换，默认为true
*/
private boolean ISOVERWRITE = NEED_OVERWRITE;
/**
* 设定是否需要比较，默认为true(仅当不覆写目标内容是有效，即ISOVERWRITE=false时有效)
*/
private boolean ISNEEDCOMPARE = NEED_COMPARE;
/**
* 设定是否只操作第一个sheet
*/
private boolean ONLYREADONESHEET = ONLY_ONE_SHEET;
/**
* 设定操作的sheet在索引值
*/
private int SELECTEDSHEETIDX = SELECTED_SHEET;
/**
* 设定操作的sheet的名称
*/
private String SELECTEDSHEETNAME = "";
/**
* 设定开始读取的sheet，默认为0
*/
private int STARTSHEETIDX = READ_START_SHEET;
/**
* 设定结束读取的sheet，默认为0，用负数来表示倒数第n行
*/
private int ENDSHEETIDX = READ_END_SHEET;
/**
* 设定是否打印消息
*/
private boolean PRINTMSG = PRINT_MSG;
private Workbook workbook;
/**
* <读取Excel文件>
*
* @param is
* 输入流
* @param templateXml
* Excel读取模板
* @return 数据Map MAP_KEY_SUC：验证成功数据 MAP_KEY_ERR：验证失败数据
* @throws Exception
* @throws java.io.FileNotFoundException
*/
public static Map<String, Object> readExcel(InputStream is, String templateXml, int importSize) throws Exception
{
ExcelWorkbook excelWorkBook = ExcelTemplateUtil.createExcelWorkbook(new FileInputStream(templateXml));
return readExcel(is, excelWorkBook, importSize);
}
public static List<String> readIMEIExcel(InputStream is, int importSize) throws Exception
{
List<String> list = new ArrayList<String>();
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
try
{
// 生成Excel
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(0);
Row row = null;
int rowNum = sheet.getLastRowNum();
if (importSize <= rowNum)
{
throw new Exception("Exccel行数超出读取最大限制[" + importSize + "]，不允许读取!");
}
// 正文内容应该从第二行开始,第一行为表头的标题
for (int rowIndex = 1; rowIndex <= rowNum; rowIndex++)
{
row = sheet.getRow(rowIndex);
if (row == null)
{
continue;
}
Cell cell = row.getCell(0);
if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK
|| "".equals(cell.getStringCellValue().trim()))
{
// 如果列值为空跳过当前行
continue;
}
list.add(cell.getStringCellValue().trim());
}
}
catch (Exception e)
{
logger.error("读取串号文件出错：" + e.getMessage(), e);
throw new Exception("读取串号文件出错：" + e.getMessage(), e);
}
finally
{
if (is != null)
{
try
{
is.close();
}
catch (IOException e)
{
// 关闭流异常忽略
logger.warn("关闭流异常!", e);
}
}
}
return list;
}
/**
* <读取Excel文件>
*
* @param is
* 输入流
* @param excelWorkBook
* Excel读取模板
* @return 数据Map MAP_KEY_SUC：验证成功数据 MAP_KEY_ERR：验证失败数据
* @throws Exception
* @throws java.io.FileNotFoundException
*/
public static Map<String, Object> readExcel(InputStream is, ExcelWorkbook excelWorkBook, int importSize) throws Exception
{
// 读取文件返回结果集 包含成功记录 及失败记录
Map<String, Object> resultMap = new HashMap<String, Object>();
// 存入验证成功记录数
List<Map<String, Object>> listSuc = new ArrayList<Map<String, Object>>();
// 存入验证失败记录数
List<Map<String, Object>> listErr = new ArrayList<Map<String, Object>>();
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
try
{
// 生成Excel
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(0);
Row row = null;
int rowNum = sheet.getLastRowNum();
if (importSize <= rowNum)
{
resultMap.put(MAP_KEY_ERR_INFO, "Exccel行数超出限制，不允许读取!");
return resultMap;
}
// 正文内容应该从第二行开始,第一行为表头的标题
for (int rowIndex = 1; rowIndex <= rowNum; rowIndex++)
{
row = sheet.getRow(rowIndex);
if (row == null)
{
continue;
}
Cell cell = row.getCell(0);
if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
{
// 如果第一列值为空跳过当前行
continue;
}
// Excel中的一行对应一个MAP
Map<String, Object> valueMap = getRowDataByExcelTemplate(rowIndex, row, excelWorkBook);
if (valueMap.get(MAP_KEY_ERR_INFO) == null)
{
listSuc.add(valueMap);
}
else
{
listErr.add(valueMap);
}
}
resultMap.put(MAP_KEY_SUC, listSuc);
resultMap.put(MAP_KEY_ERR, listErr);
return resultMap;
}
catch (Exception e)
{
logger.error("读取Excel文件异常!" + e.getMessage(), e);
throw new Exception("读取Excel文件异常!" + e.getMessage(), e);
}
finally
{
if (is != null)
{
try
{
is.close();
}
catch (IOException e)
{
// 关闭流异常忽略
logger.warn("关闭流异常!", e);
}
}
}
}
/**
* <Excel行数据转换>
*
* @param rowIndex
* 行索引
* @param row
* Excel行数据
* @param excelWorkBook
* 数据模板
* @return Map key 为模板定义，value为值
*/
private static Map<String, Object> getRowDataByExcelTemplate(int rowIndex, Row row, ExcelWorkbook excelWorkBook)
{
int colIndex = 0;
ExcelSheet sheet = excelWorkBook.getSheet();
Map<String, Object> rowMap = new LinkedHashMap<String, Object>();
// 有序行模板
List<ExcelCell> list = sheet.getCellList();
for (ExcelCell excelCell : list)
{
Cell cell = row.getCell(colIndex);
if (cell == null)
{
Object v = 0;
if ("1".equals(excelCell.getType()))
{
if (!StringUtils.isEmpty(excelCell.getDefaultValue()))
{
v = excelCell.getDefaultValue();
}
else
{
v = "";
}
}
else
{
if (!StringUtils.isEmpty(excelCell.getDefaultValue()))
{
v = Integer.valueOf(excelCell.getDefaultValue());
}
}
rowMap.put(excelCell.getName(), v);
}
else
{
Map<String, Object> verifyMap = verifyCell(rowIndex, colIndex, excelCell, cell);
// 获取验证数据
// 验证失败,记录或者拼接错误信息
if ("false".equals(verifyMap.get(MAP_KEY_2)))
{
Object errorInfo = rowMap.get(MAP_KEY_ERR_INFO);
if (errorInfo != null)
{
rowMap.put(MAP_KEY_ERR_INFO, errorInfo.toString() + "<br/>"
+ verifyMap.get(MAP_KEY_3).toString());
}
else
{
rowMap.put(MAP_KEY_ERR_INFO, verifyMap.get(MAP_KEY_3).toString());
}
}
// 单元格数据,无论验证成功或者失败都要返回单元格原始数据
rowMap.put(excelCell.getName(), verifyMap.get(MAP_KEY_1));
}
colIndex++;
}
return rowMap;
}
/**
* <根据模板验证单元格数据>
*
* @param excelCell
* 单元格模板
* @param cell
* Excel单元格
* @return 结果 MAP_KEY_1:单元格数据 MAP_KEY_2:验证结果 true ? false MAP_KEY_3:验证失败信息
*/
private static Map<String, Object> verifyCell(int rowIndex, int colIndex, ExcelCell excelCell, Cell cell)
{
Map<String, Object> verifyMap = new HashMap<String, Object>();
// 没有默认值才取根据模板取Excel中的数据
if (StringUtils.isEmpty(excelCell.getDefaultValue()))
{
// 1：文本2：数值
if ("1".equals(excelCell.getType()))
{
verifyMap.put(MAP_KEY_1, getStringValue(cell));
}
else
{
// 数值类型
// 为了兼容客户将数字设置为文体，此处特殊处理
if (0 == cell.getCellType())
{
verifyMap.put(MAP_KEY_1, cell.getNumericCellValue());
}
else if (1 == cell.getCellType())
{
String value = cell.getStringCellValue().trim();
Pattern p = Pattern.compile("\t|\r|\n");
value = p.matcher(value).replaceAll("");
try
{
verifyMap.put(MAP_KEY_1, Double.valueOf(value));
}
catch (Exception e)
{
verifyMap.put(MAP_KEY_1, value);
verifyMap.put(MAP_KEY_2, "false");
verifyMap.put(MAP_KEY_3, "第[" + (rowIndex + 1) + "]行，第[" + (colIndex + 1) + "]列获取数值出错！");
}
}
else
{
verifyMap.put(MAP_KEY_1, getStringValue(cell));
verifyMap.put(MAP_KEY_2, "false");
verifyMap.put(MAP_KEY_3, "第[" + (rowIndex + 1) + "]行，第[" + (colIndex + 1) + "]列获取数值出错！");
}
}
}
else
{
verifyMap.put(MAP_KEY_1, excelCell.getDefaultValue());
}
// 非空验证
if (excelCell.getNullable() != null)
{
// 验证数据不能为空
Object obj = verifyMap.get(MAP_KEY_1);
if (isEmpty(obj))
{
verifyMap.put(MAP_KEY_2, "false");
verifyMap.put(MAP_KEY_3, "第[" + (rowIndex + 1) + "]行，第[" + (colIndex + 1) + "]列不能为空！");
}
}
// 长度验证
if ("1".equals(excelCell.getType()) && excelCell.getLength() > 0)
{
// 验证数据不能为空
Object obj = verifyMap.get(MAP_KEY_1);
if (!isEmpty(obj))
{
if (obj.toString().length() > excelCell.getLength())
{
verifyMap.put(MAP_KEY_2, "false");
verifyMap.put(MAP_KEY_3, "第[" + (rowIndex + 1) + "]行，第[" + (colIndex + 1) + "]列验证出错，列最大长度限制为："
+ excelCell.getLength() + "，实际值：”" + obj.toString().length());
}
}
}
return verifyMap;
}
/**
* <读取Excel中的数据转换成String类型>
*
* @param cell
* @return
*/
private static String getStringValue(Cell cell)
{
String value = null;
switch (cell.getCellType())
{
case Cell.CELL_TYPE_BLANK:
value = "";
break;
case Cell.CELL_TYPE_NUMERIC:
Double t = cell.getNumericCellValue();
if (t == t.intValue())
{
value = new BigDecimal(t).toString();
}
else
{
value = BigDecimal.valueOf(t).toString();
}
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().trim();
break;
default:
break;
}
value = value == null ? "" : value;
Pattern p = Pattern.compile("\t|\r|\n");
return p.matcher(value).replaceAll("");
}
/**
* <判断输入参数是否为空>
*
* @param obj
* 输入参数
* @return 为null 或 空字符串结果true
*/
private static boolean isEmpty(Object obj)
{
if (obj == null || "".equals(obj.toString()))
{
return true;
}
return false;
}
/**
*
* <功能描述>
* @param response
* @param request
* @param templatePath
* @param fileName
* @param dataList List<List<Map<String, String>>>三层泛型分别代表sheet, 行, 列
* @return
*/
public static HandlerResult writerExcelFromTemplate(HttpServletResponse response, HttpServletRequest request,
String templatePath, String fileName, List<List<List<Object>>> dataList)
{
HandlerResult result = HandlerResult.newInstance();
if (isEmpty(templatePath))
{
return HandlerResult.commonErrorResult("定义的模版文件路径为空, 请联系管理员.");
}
if (response == null)
{
return HandlerResult.commonErrorResult("HttpServletResponse对象为空, 无法导出文件, 请联系管理员.");
}
if (request == null)
{
return HandlerResult.commonErrorResult("HttpServletRequest对象为空, 无法导出文件, 请联系管理员.");
}
if (dataList == null || dataList.size() == 0)
{
return HandlerResult.commonErrorResult("导出结果为空, 无需导出文件, 请更改查询条件.");
}
if (isEmpty(fileName))
{
return HandlerResult.commonErrorResult("输出文件的文件名为空, 请联系管理员.");
}
// 完成检查, 开始读取模版文件
if (!templatePath.contains(File.separator))
{
templatePath = request.getSession().getServletContext().getRealPath("/") + File.separator + "WEB-INF"
+ File.separator + "export" + File.separator + templatePath;
}
InputStream is = null;
File file = null;
file = new File(templatePath);
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
try
{
is = new FileInputStream(file);
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
}
catch (FileNotFoundException e)
{
logger.error("从模版文件中读取流时发生异常, 找不到模版文件, 导出失败.", e);
return HandlerResult.commonErrorResult("从模版文件中读取流时发生异常, 找不到模版文件, 导出失败.");
}
catch (IOException e)
{
logger.error("从模版文件中读取流时发生I/O异常, 导出失败.", e);
return HandlerResult.commonErrorResult("从模版文件中读取流时发生I/O异常, 导出失败.");
}
finally
{
if (is != null)
{
try
{
is.close();
}
catch (IOException e)
{
logger.error("关闭流时发生异常", e);
}
}
}
for (int sheetID = 0; sheetID < dataList.size(); sheetID++)
{
// 支持多个sheet的情况. 具体的对应关系为sheet的序号与所传dataList的序号一一对应
try
{
sheet = wb.getSheetAt(sheetID);
}
catch (Exception e)
{
// 一般出现这个exception都是因为sheet在模版里的数量和datalist给的数量不一致导致
logger.error("从模版文件中读取不到sheet[" + sheetID + "], 导出失败.");
return HandlerResult.commonErrorResult("从模版文件中读取不到sheet[" + sheetID + "], 导出失败.");
}
if (sheet == null)
{
logger.error("从模版文件中读取不到sheet[" + sheetID + "], 导出失败.");
return HandlerResult.commonErrorResult("从模版文件中读取不到sheet[" + sheetID + "], 导出失败.");
}
// 填充表格
// 获取已有的行数, 从该行开始输出
int rownum = sheet.getLastRowNum();
List<List<Object>> sheetData = dataList.get(sheetID);
for (List<Object> rowData : sheetData)
{
// 生成一个新的row
HSSFRow createRow = sheet.createRow(rownum);
rownum++;
// 遍历rowData, 按顺序将其中的数据填充到cell中去
for (int cellIndex = 0; cellIndex < rowData.size(); cellIndex++)
{
HSSFCell createCell = createRow.createCell(cellIndex);
Object cellData = rowData.get(cellIndex);
String cellValue = null;
if (cellData == null)
{
// 设为空白
createCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
cellValue = "";
}
else {
cellValue = cellData.toString();
if (cellData instanceof Integer || cellData instanceof Long || cellData instanceof Short
|| cellData instanceof Double || cellData instanceof Float)
{
createCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
else if (cellData instanceof String)
{
createCell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
else
{
createCell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
}
HSSFRichTextString richText = new HSSFRichTextString(cellValue);
createCell.setCellValue(richText);
}
}
}
// 导出excel
response.setContentType("application/x-download");
try
{
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0)
{
// fireFox浏览器
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
}
else
{
// IE浏览器
fileName = URLEncoder.encode(fileName, "UTF-8");
}
}
catch (UnsupportedEncodingException e1)
{
// 这里仅是文件名乱码, 不需要作中断
logger.error("文件名转码时发生异常. 输出文件名可能为乱码. 无需中断", e1);
}
response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
// 定义输出类型
response.setContentType("application/msexcel");
response.setContentType("UTF-8");
OutputStream os = null;
try
{
os = response.getOutputStream();
}
catch (IOException e1)
{
return HandlerResult.commonErrorResult("从response中获取输出流时发生I/O异常, 导出失败.");
}
try
{
wb.write(os);
}
catch (IOException e)
{
return HandlerResult.commonErrorResult("将生成的excel文件发送至客户端的过程中发生I/O异常, 导出失败.");
}
finally
{
// 因为上面的catch已经捕捉了异常. 所以这里如果仅是因为关闭失败的话不需要中断.
if (os != null)
{
try
{
os.close();
}
catch (IOException e)
{
logger.error("关闭流异常!", e);
}
}
}
result.setResMsg("文件导出成功");
result.setRetCode(IResultCode.SYS_SUCCESS);
result.setSysCode(SystemCodeConstants.OPERATE_SUCCEED);
return result;
}
/**
* <生成Excel文件>
*
* @param dataList
* 数据列表
* @param os
* 输出流
* @param templateXml
* Excel生成模板
* @return
*/
public static boolean writerExcel(List<Map<String, Object>> dataList, OutputStream os, String templateXml, int exportSize)
{
boolean b = true;
try
{
if (dataList != null && dataList.size() > exportSize)
{
logger.error("导出数据大于导出限制数量:" + exportSize);
throw new Exception("导出数据大于导出限制数量：" + exportSize);
}
ExcelWorkbook excelWorkBook = ExcelTemplateUtil.createExcelWorkbook(new FileInputStream(templateXml));
// 声明一个Excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(excelWorkBook.getSheet().getSheetDesc());
int columnIndex = 0;
int rowIndex = 0;
HSSFRow row = null;
if (excelWorkBook.getSheet().isShow())
{
// 设置表头
row = sheet.createRow(rowIndex);
// 所有的行高都是这个高度
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// Excel头部样式
CellStyle cellStyleHead = workbook.createCellStyle();
cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
cellStyleHead.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontHead = workbook.createFont();
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
fontHead.setFontHeightInPoints((short) 9);// 9号字体
fontHead.setFontName("宋体");// 宋体
cellStyleHead.setFont(fontHead);
// 使用模板创建Excel表头
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString richText = new HSSFRichTextString(excelCell.getDesc());
cell.setCellValue(richText);
// 指定头部样式
cell.setCellStyle(cellStyleHead);
if (excelCell.getWidth() > 0)
{
// 指定列宽
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 数据单元格样式
CellStyle bodyStyle = workbook.createCellStyle();
// 边框
bodyStyle.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
// 宋体9号
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 9);
font.setFontName("宋体");
bodyStyle.setFont(font);
// 填充数据
for (Map<String, Object> rowMap : dataList)
{
rowIndex++;// 数据行从第二行开始，索引下标1
// 列索引从0开始
columnIndex = 0;
row = sheet.createRow(rowIndex);
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// 使用模板 填充数据
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
// 全部以文体的方式导出
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (rowMap.get(excelCell.getName()) != null)
{
HSSFRichTextString richText = new HSSFRichTextString(rowMap.get(excelCell.getName()).toString());
cell.setCellValue(richText);
}
else
{
cell.setCellValue(new HSSFRichTextString(excelCell.getNullvalue()));
}
// 指定数据体样式
cell.setCellStyle(bodyStyle);
// 指定列宽
if (excelCell.getWidth() > 0)
{
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 导出excel
workbook.write(os);
}
catch (Exception e)
{
b = false;
logger.error("导出excel失败",e);
}
finally
{
if (os != null)
{
try
{
os.close();
}
catch (IOException e)
{
logger.warn("关闭流异常!", e);
}
}
}
return b;
}
/**
* <生成Excel文件>
*
* @param dataList
* 数据列表
* @param os
* 输出流
* @param templateXml
* Excel生成模板
* @return
*/
public static boolean writerExcel(List<Map<String, Object>> dataList, OutputStream os, String sheetName,List<Map<String,Object>> list, int exportSize)
{
boolean b = true;
try
{
if (dataList != null && dataList.size() > exportSize)
{
logger.error("导出数据大于导出限制数量:" + exportSize);
throw new Exception("导出数据大于导出限制数量：" + exportSize);
}
ExcelWorkbook excelWorkBook = ExcelTemplateUtil.createExcelWorkbook(sheetName,list);
// 声明一个Excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(excelWorkBook.getSheet().getSheetDesc());
int columnIndex = 0;
int rowIndex = 0;
HSSFRow row = null;
if (excelWorkBook.getSheet().isShow())
{
// 设置表头
row = sheet.createRow(rowIndex);
// 所有的行高都是这个高度
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// Excel头部样式
CellStyle cellStyleHead = workbook.createCellStyle();
cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
cellStyleHead.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontHead = workbook.createFont();
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
fontHead.setFontHeightInPoints((short) 9);// 9号字体
fontHead.setFontName("宋体");// 宋体
cellStyleHead.setFont(fontHead);
// 使用模板创建Excel表头
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString richText = new HSSFRichTextString(excelCell.getDesc());
cell.setCellValue(richText);
// 指定头部样式
cell.setCellStyle(cellStyleHead);
if (excelCell.getWidth() > 0)
{
// 指定列宽
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 数据单元格样式
CellStyle bodyStyle = workbook.createCellStyle();
// 边框
bodyStyle.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
// 宋体9号
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 9);
font.setFontName("宋体");
bodyStyle.setFont(font);
// 填充数据
for (Map<String, Object> rowMap : dataList)
{
rowIndex++;// 数据行从第二行开始，索引下标1
// 列索引从0开始
columnIndex = 0;
row = sheet.createRow(rowIndex);
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// 使用模板 填充数据
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
// 全部以文体的方式导出
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (rowMap.get(excelCell.getName()) != null)
{
HSSFRichTextString richText = new HSSFRichTextString(rowMap.get(excelCell.getName()).toString());
cell.setCellValue(richText);
}
else
{
cell.setCellValue(new HSSFRichTextString(excelCell.getNullvalue()));
}
// 指定数据体样式
cell.setCellStyle(bodyStyle);
// 指定列宽
if (excelCell.getWidth() > 0)
{
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 导出excel
workbook.write(os);
}
catch (Exception e)
{
b = false;
}
finally
{
if (os != null)
{
try
{
os.close();
}
catch (IOException e)
{
logger.warn("关闭流异常!", e);
}
}
}
return b;
}
/**
* <生成Excel文件> 包括文件名称对浏览器兼容设置 contentType设置
*
* @param dataList
* 数据列表
* @param os
* 输出流
* @param templateXml
* Excel生成模板
* @param fileName
* 导出excel文件名称
* @return
*/
public static boolean writerExcel1(List<Map<String, Object>> dataList, HttpServletRequest request,
HttpServletResponse response, String templateXml, String fileName, int exportSize)
{
boolean b = true;
OutputStream os = null;
try
{
os = response.getOutputStream();
response.setContentType("application/x-download");
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0)
{
// fireFox浏览器
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
}
else
{
// IE浏览器
fileName = URLEncoder.encode(fileName, "UTF-8");
}
response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
// 定义输出类型
response.setContentType("application/msexcel");
response.setContentType("UTF-8");
if (dataList != null && dataList.size() > exportSize)
{
logger.error("导出数据大于导出限制数量:" + exportSize);
throw new Exception("导出数据大于导出限制数量:" + exportSize);
}
ExcelWorkbook excelWorkBook = ExcelTemplateUtil.createExcelWorkbook(new FileInputStream(templateXml));
// 声明一个Excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(excelWorkBook.getSheet().getSheetDesc());
int columnIndex = 0;
int rowIndex = 0;
HSSFRow row = null;
if (excelWorkBook.getSheet().isShow())
{
// 设置表头
row = sheet.createRow(rowIndex);
// 所有的行高都是这个高度
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// Excel头部样式
CellStyle cellStyleHead = workbook.createCellStyle();
cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
cellStyleHead.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontHead = workbook.createFont();
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
fontHead.setFontHeightInPoints((short) 9);// 9号字体
fontHead.setFontName("宋体");// 宋体
cellStyleHead.setFont(fontHead);
// 使用模板创建Excel表头
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString richText = new HSSFRichTextString(excelCell.getDesc());
cell.setCellValue(richText);
// 指定头部样式
cell.setCellStyle(cellStyleHead);
if (excelCell.getWidth() > 0)
{
// 指定列宽
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 数据单元格样式
CellStyle bodyStyle = workbook.createCellStyle();
// 边框
bodyStyle.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
// 宋体9号
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 9);
font.setFontName("宋体");
bodyStyle.setFont(font);
// 填充数据
for (Map<String, Object> rowMap : dataList)
{
rowIndex++;// 数据行从第二行开始，索引下标1
// 列索引从0开始
columnIndex = 0;
row = sheet.createRow(rowIndex);
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// 使用模板 填充数据
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
// 全部以文体的方式导出
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (rowMap.get(excelCell.getName()) != null)
{
HSSFRichTextString richText = new HSSFRichTextString(rowMap.get(excelCell.getName()).toString());
cell.setCellValue(richText);
}
else
{
cell.setCellValue(new HSSFRichTextString(excelCell.getNullvalue()));
}
// 指定数据体样式
cell.setCellStyle(bodyStyle);
// 指定列宽
if (excelCell.getWidth() > 0)
{
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 导出excel
workbook.write(os);
}
catch (Exception e)
{
b = false;
}
finally
{
if (os != null)
{
try
{
os.close();
}
catch (IOException e)
{
logger.warn("关闭流异常!", e);
}
}
}
return b;
}
public static boolean writerExcelLarge(List<Map<String, Object>> dataList, FileOutputStream os, String templateXml)
{
boolean b = true;
try
{
ExcelWorkbook excelWorkBook = ExcelTemplateUtil.createExcelWorkbook(new FileInputStream(templateXml));
// 声明一个Excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(excelWorkBook.getSheet().getSheetDesc());
int columnIndex = 0;
int rowIndex = 0;
HSSFRow row = null;
if (excelWorkBook.getSheet().isShow())
{
// 设置表头
row = sheet.createRow(rowIndex);
// 所有的行高都是这个高度
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// Excel头部样式
CellStyle cellStyleHead = workbook.createCellStyle();
cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
cellStyleHead.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontHead = workbook.createFont();
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
fontHead.setFontHeightInPoints((short) 9);// 9号字体
fontHead.setFontName("宋体");// 宋体
cellStyleHead.setFont(fontHead);
// 使用模板创建Excel表头
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString richText = new HSSFRichTextString(excelCell.getDesc());
cell.setCellValue(richText);
// 指定头部样式
cell.setCellStyle(cellStyleHead);
if (excelCell.getWidth() > 0)
{
// 指定列宽
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 数据单元格样式
CellStyle bodyStyle = workbook.createCellStyle();
// 边框
bodyStyle.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
// 宋体9号
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 9);
font.setFontName("宋体");
bodyStyle.setFont(font);
// 填充数据
for (Map<String, Object> rowMap : dataList)
{
rowIndex++;// 数据行从第二行开始，索引下标1
// 列索引从0开始
columnIndex = 0;
row = sheet.createRow(rowIndex);
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// 使用模板 填充数据
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
// 全部以文体的方式导出
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (rowMap.get(excelCell.getName()) != null)
{
HSSFRichTextString richText = new HSSFRichTextString(rowMap.get(excelCell.getName()).toString());
cell.setCellValue(richText);
}
else
{
cell.setCellValue(new HSSFRichTextString(excelCell.getNullvalue()));
}
// 指定数据体样式
cell.setCellStyle(bodyStyle);
// 指定列宽
if (excelCell.getWidth() > 0)
{
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 导出excel
workbook.write(os);
}
catch (Exception e)
{
b = false;
}
finally
{
if (os != null)
{
try
{
os.close();
}
catch (IOException e)
{
logger.warn("关闭流异常!", e);
}
}
}
return b;
}
/**
* <生成Excel文件> 包括文件名称对浏览器兼容设置 contentType设置
* 支持合并单元格（可在模版文件增加属性rowMerged：表示当前单元格与其上合并，
* colMerged：表示当前单元格与其左合并）
*
* @param dataList
* 数据列表
* @param os
* 输出流
* @param templateXml
* Excel生成模板
* @param fileName
* 导出excel文件名称
* @return
*/
public static boolean writerExcel2(List<Map<String, Object>> dataList, HttpServletRequest request,
HttpServletResponse response, String templateXml, String fileName, int exportSize, Map<String, Integer> priceRngMap)
{
boolean b = true;
OutputStream os = null;
try
{
os = response.getOutputStream();
response.setContentType("application/x-download");
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0)
{
// fireFox浏览器
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
}
else
{
// IE浏览器
fileName = URLEncoder.encode(fileName, "UTF-8");
}
response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
// 定义输出类型
response.setContentType("application/msexcel");
response.setContentType("UTF-8");
if (dataList != null && dataList.size() > exportSize)
{
logger.error("导出数据大于导出限制数量:" + exportSize);
throw new Exception("导出数据大于导出限制数量:" + exportSize);
}
ExcelWorkbook excelWorkBook = ExcelTemplateUtil.createExcelWorkbookList(new FileInputStream(templateXml));
// 声明一个Excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(excelWorkBook.getSheet().getSheetDesc());
int columnIndex = 0;
int rowIndex = 0;
HSSFRow row = null;
if (excelWorkBook.getSheet().isShow())
{
List<List<Map<String, Object>>> mergedList = new ArrayList<List<Map<String, Object>>>();
for (int i = 0; i < excelWorkBook.getSheetList().size(); i++)
{
columnIndex = 0;
// 设置表头
row = sheet.createRow(rowIndex);
// 所有的行高都是这个高度
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// Excel头部样式
CellStyle cellStyleHead = workbook.createCellStyle();
cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
cellStyleHead.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontHead = workbook.createFont();
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
fontHead.setFontHeightInPoints((short) 9);// 9号字体
fontHead.setFontName("宋体");// 宋体
cellStyleHead.setFont(fontHead);
// 使用模板创建Excel表头
List<Map<String, Object>> rowMerdedList = new ArrayList<Map<String, Object>>();
for (ExcelCell excelCell : excelWorkBook.getSheetList().get(i).getCellList())
{
HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString richText = new HSSFRichTextString(excelCell.getDesc());
cell.setCellValue(richText);
// 指定头部样式
cell.setCellStyle(cellStyleHead);
if (excelCell.getWidth() > 0)
{
// 指定列宽
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
Map<String, Object> map = new HashMap<String, Object>();
if ("1".equals(excelCell.getRowMergerd()))
{
map.put("rowMerged", "1");
}
if ("1".equals(excelCell.getColMergerd()))
{
map.put("colMerged", "1");
}
rowMerdedList.add(map);
}
mergedList.add(rowMerdedList);
rowIndex++;
}
for (int k = 0; k < mergedList.size(); k++)
{
List<Map<String, Object>> list = mergedList.get(k);
for (int l = 0; l < list.size(); l++)
{
Map<String, Object> map = list.get(l);
if ("1".equals(map.get("rowMerged")))
{
sheet.addMergedRegion(new CellRangeAddress(k - 1, k, l, l));
}
if ("1".equals(map.get("colMerged")))
{
sheet.addMergedRegion(new CellRangeAddress(k, k, l - 1, l));
}
}
}
}
rowIndex--;
// 数据单元格样式
CellStyle bodyStyle = workbook.createCellStyle();
// 边框
bodyStyle.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
// 宋体9号
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 9);
font.setFontName("宋体");
bodyStyle.setFont(font);
// 合并单元格逻辑处理
mergedCell(priceRngMap, sheet);
// 填充数据
for (Map<String, Object> rowMap : dataList)
{
rowIndex++;// 数据行从第二行开始，索引下标1
// 列索引从0开始
columnIndex = 0;
row = sheet.createRow(rowIndex);
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// 使用模板 填充数据
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
// 全部以文体的方式导出
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (rowMap.get(excelCell.getName()) != null)
{
HSSFRichTextString richText = new HSSFRichTextString(rowMap.get(excelCell.getName()).toString());
cell.setCellValue(richText);
}
else
{
cell.setCellValue(new HSSFRichTextString(excelCell.getNullvalue()));
}
// 指定数据体样式
cell.setCellStyle(bodyStyle);
// 指定列宽
if (excelCell.getWidth() > 0)
{
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 导出excel
workbook.write(os);
}
catch (Exception e)
{
b = false;
}
finally
{
if (os != null)
{
try
{
os.close();
}
catch (IOException e)
{
logger.warn("关闭流异常!", e);
}
}
}
return b;
}
/**
* 合并单元格逻辑处理
* @param priceRngMap
* @param sheet
*/
private static void mergedCell(Map<String, Integer> priceRngMap,
HSSFSheet sheet) {
// 0~499起始行，结束行
int oneRowStart = 1;
int oneRowEnd = priceRngMap.get("0~499");
// 500~999起始行，结束行
int secondRowStart = oneRowStart + oneRowEnd;
int secondRowEnd = oneRowEnd + priceRngMap.get("500~999");
// 1000~2999起始行，结束行
int thirdRowStart = oneRowStart + secondRowEnd;
int thirdRowEnd = secondRowEnd + priceRngMap.get("1000~2999");
// 3000~4999起始行，结束行
int fourthRowStart = oneRowStart + thirdRowEnd;
int fourthRowEnd = thirdRowEnd + priceRngMap.get("3000~4999");
// 5000~9999起始行，结束行
int fifthRowStart = oneRowStart + fourthRowEnd;
int fifthRowEnd = fourthRowEnd + priceRngMap.get("5000~9999");
// 10000及以上起始行，结束行
int sixRowStart = oneRowStart + fifthRowEnd;
int sixRowEnd = fifthRowEnd + priceRngMap.get("10000及以上");
if (priceRngMap.get("0~499") != 0)
{
sheet.addMergedRegion(new CellRangeAddress(oneRowStart,oneRowEnd,0,0));
sheet.addMergedRegion(new CellRangeAddress(1,priceRngMap.get("0~499"),1,1));
}
if (priceRngMap.get("500~999") != 0)
{
sheet.addMergedRegion(new CellRangeAddress(secondRowStart,secondRowEnd,0,0));
sheet.addMergedRegion(new CellRangeAddress(secondRowStart,secondRowEnd,1,1));
}
if (priceRngMap.get("1000~2999") != 0)
{
sheet.addMergedRegion(new CellRangeAddress(thirdRowStart,thirdRowEnd,0,0));
sheet.addMergedRegion(new CellRangeAddress(thirdRowStart,thirdRowEnd,1,1));
}
if (priceRngMap.get("3000~4999") != 0)
{
sheet.addMergedRegion(new CellRangeAddress(fourthRowStart,fourthRowEnd,0,0));
sheet.addMergedRegion(new CellRangeAddress(fourthRowStart,fourthRowEnd,1,1));
}
if (priceRngMap.get("5000~9999") != 0)
{
sheet.addMergedRegion(new CellRangeAddress(fifthRowStart,fifthRowEnd,0,0));
sheet.addMergedRegion(new CellRangeAddress(fifthRowStart,fifthRowEnd,1,1));
}
if (priceRngMap.get("10000及以上") != 0)
{
sheet.addMergedRegion(new CellRangeAddress(sixRowStart,sixRowEnd,0,0));
sheet.addMergedRegion(new CellRangeAddress(sixRowStart,sixRowEnd,1,1));
}
}
/**
* <生成Excel文件> 包括文件名称对浏览器兼容设置 contentType设置
* 支持合并单元格（可在模版文件增加属性rowMerged：表示当前单元格与其上合并，
* colMerged：表示当前单元格与其左合并）
*
* @param dataList
* 数据列表
* @param os
* 输出流
* @param templateXml
* Excel生成模板
* @param fileName
* 导出excel文件名称
* @return
*/
public static boolean writerExcelForMerge(List<Map<String, Object>> dataList, HttpServletRequest request,
HttpServletResponse response, String templateXml, String fileName, int exportSize,Map<String, List> mapCount)
{
boolean b = true;
OutputStream os = null;
try
{
os = response.getOutputStream();
response.setContentType("application/x-download");
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0)
{
// fireFox浏览器
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
}
else
{
// IE浏览器
fileName = URLEncoder.encode(fileName, "UTF-8");
}
response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
// 定义输出类型
response.setContentType("application/msexcel");
response.setContentType("UTF-8");
if (dataList != null && dataList.size() > exportSize)
{
logger.error("导出数据大于导出限制数量:" + exportSize);
throw new Exception("导出数据大于导出限制数量:" + exportSize);
}
ExcelWorkbook excelWorkBook = ExcelTemplateUtil.createExcelWorkbookList(new FileInputStream(templateXml));
// 声明一个Excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(excelWorkBook.getSheet().getSheetDesc());
int columnIndex = 0;
int rowIndex = 0;
HSSFRow row = null;
if (excelWorkBook.getSheet().isShow())
{
List<List<Map<String, Object>>> mergedList = new ArrayList<List<Map<String, Object>>>();
for (int i = 0; i < excelWorkBook.getSheetList().size(); i++)
{
columnIndex = 0;
// 设置表头
row = sheet.createRow(rowIndex);
// 所有的行高都是这个高度
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// Excel头部样式
CellStyle cellStyleHead = workbook.createCellStyle();
cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
cellStyleHead.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
cellStyleHead.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont fontHead = workbook.createFont();
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
fontHead.setFontHeightInPoints((short) 9);// 9号字体
fontHead.setFontName("宋体");// 宋体
cellStyleHead.setFont(fontHead);
// 使用模板创建Excel表头
List<Map<String, Object>> rowMerdedList = new ArrayList<Map<String, Object>>();
for (ExcelCell excelCell : excelWorkBook.getSheetList().get(i).getCellList())
{
HSSFCell cell = row.createCell(columnIndex);
HSSFRichTextString richText = new HSSFRichTextString(excelCell.getDesc());
cell.setCellValue(richText);
// 指定头部样式
cell.setCellStyle(cellStyleHead);
if (excelCell.getWidth() > 0)
{
// 指定列宽
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
Map<String, Object> map = new HashMap<String, Object>();
if ("1".equals(excelCell.getRowMergerd()))
{
map.put("rowMerged", "1");
}
if ("1".equals(excelCell.getColMergerd()))
{
map.put("colMerged", "1");
}
rowMerdedList.add(map);
}
mergedList.add(rowMerdedList);
rowIndex++;
}
for (int k = 0; k < mergedList.size(); k++)
{
List<Map<String, Object>> list = mergedList.get(k);
for (int l = 0; l < list.size(); l++)
{
Map<String, Object> map = list.get(l);
if ("1".equals(map.get("rowMerged")))
{
sheet.addMergedRegion(new CellRangeAddress(k - 1, k, l, l));
}
if ("1".equals(map.get("colMerged")))
{
sheet.addMergedRegion(new CellRangeAddress(k, k, l - 1, l));
}
}
}
}
rowIndex--;
// 数据单元格样式
CellStyle bodyStyle = workbook.createCellStyle();
// 边框
bodyStyle.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);
bodyStyle.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);
// 宋体9号
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 9);
font.setFontName("宋体");
bodyStyle.setFont(font);
bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 合并单元格逻辑处理
mergeCell(mapCount, sheet);
// 填充数据
for (Map<String, Object> rowMap : dataList)
{
rowIndex++;// 数据行从第二行开始，索引下标1
// 列索引从0开始
columnIndex = 0;
row = sheet.createRow(rowIndex);
if (excelWorkBook.getSheet().getHeight() > 0)
{
row.setHeight((short) excelWorkBook.getSheet().getHeight());
}
// 使用模板 填充数据
for (ExcelCell excelCell : excelWorkBook.getSheet().getCellList())
{
// 全部以文体的方式导出
HSSFCell cell = row.createCell(columnIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (rowMap.get(excelCell.getName()) != null)
{
HSSFRichTextString richText = new HSSFRichTextString(rowMap.get(excelCell.getName()).toString());
cell.setCellValue(richText);
}
else
{
cell.setCellValue(new HSSFRichTextString(excelCell.getNullvalue()));
}
// 指定数据体样式
cell.setCellStyle(bodyStyle);
// 指定列宽
if (excelCell.getWidth() > 0)
{
sheet.setColumnWidth(columnIndex, excelCell.getWidth());
}
columnIndex++;
}
}
// 导出excel
workbook.write(os);
}
catch (Exception e)
{
b = false;
}
finally
{
if (os != null)
{
try
{
os.close();
}
catch (IOException e)
{
logger.warn("关闭流异常!", e);
}
}
}
return b;
}
/**
* 合并单元格逻辑处理
* @param priceRngMap
* @param sheet
*/
private static void mergeCell(Map<String, List> mapCount,
HSSFSheet sheet) {
int oneRowStart = 0;
int oneRowEnd = 1;
if (mapCount.get("count1")!=null) {
List listCount1 = mapCount.get("count1");
for (int i = 0; i < listCount1.size(); i++) {
oneRowEnd = Integer.valueOf(listCount1.get(i).toString());
sheet.addMergedRegion(new CellRangeAddress(oneRowStart+1,oneRowEnd,0,0));
oneRowStart = oneRowEnd;
}
}
int secRowStart = 0;
int secRowEnd = 1;
if (mapCount.get("count2")!=null) {
List listCount2 = mapCount.get("count2");
for (int i = 0; i < listCount2.size(); i++) {
secRowEnd = Integer.valueOf(listCount2.get(i).toString());
sheet.addMergedRegion(new CellRangeAddress(secRowStart+1,secRowEnd,1,1));
secRowStart = secRowEnd;
}
}
}
从这里开始为复制文件
//%%%%%%%%-------常量部分 开始----------%%%%%%%%%
private ExcelUtil() {
}
public ExcelUtil(String EXCELPATH) {
this.setEXCELPATH(EXCELPATH);
this.initWorkbook();
}
public ExcelUtil(InputStream inputStream, String extName) {
try {
if (extName.equals("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else {
workbook = new XSSFWorkbook(inputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
}
void initWorkbook() {
initWorkbook(this.EXCELPATH);
}
void initWorkbook(String EXCELPATH) {
//获取扩展名
String ext = EXCELPATH.substring(EXCELPATH.lastIndexOf(".") + 1);
try {
File file = new File(EXCELPATH);
if (file.exists()) {
if (ext.equals("xls")) {
workbook = new HSSFWorkbook(new FileInputStream(file));
} else {
workbook = new XSSFWorkbook(new FileInputStream(file));
}
} else {
if (ext.equals("xls")) {
workbook = new HSSFWorkbook();
} else {
workbook = new XSSFWorkbook();
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 还原设定（其实是重新new一个新的对象并返回）
*
* @return ExcelUtil 返回类型
*/
public ExcelUtil RestoreSettings() {
ExcelUtil instance = new ExcelUtil(this.EXCELPATH);
return instance;
}
/**
* 导出Excel
*
* @param rowDataList 参数
* @param sheetName 参数
* @return Sheet 返回类型
*/
public Sheet writeToSheet(List<List<String>> rowDataList, String sheetName) {
Sheet sheet = sheetName != null && !sheetName.equals("") ?
workbook.createSheet(sheetName) : workbook.createSheet();
if (rowDataList == null || rowDataList.size() == 0) {
out("无数据");
return sheet;
}
int size = rowDataList.size();
for (int i = 0; i < size; i++) {
Row row = sheet.createRow(i);
List<String> cellData = rowDataList.get(i);
if (cellData == null) {
continue;
}
int cells = cellData.size();
for (int j = 0; j < cells; j++) {
Cell cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(cellData.get(j));
}
}
return sheet;
}
/**
* @param rowDataList 参数
* @return List 列集合
*/
public List<Row> map(List<String[]> rowDataList) {
if (rowDataList == null || rowDataList.size() == 0) {
out("无数据");
return null;
}
Sheet sheet = workbook.createSheet();
int size = rowDataList.size();
List<Row> rows = new ArrayList<Row>();
for (int i = 0; i < size; i++) {
Row row = sheet.createRow(i);
String[] cellData = rowDataList.get(i);
if (cellData == null) {
continue;
}
int cells = cellData.length;
for (int j = 0; j < cells; j++) {
Cell cell = row.createCell(j, Cell.CELL_TYPE_STRING);
cell.setCellValue(cellData[j]);
}
rows.add(row);
}
workbook.removeSheetAt(workbook.getSheetIndex(sheet));
return rows;
}
/**
* 自动根据文件扩展名，调用对应的写入方法
*
* @param rowList 参数
* @throws IOException 异常
*/
public void writeExcel(List<Row> rowList) throws IOException {
writeExcel(rowList, EXCELPATH);
}
/**
* 修改Excel（97-03版，xls格式）
*
* @param rowList 参数
* @param src_xlsPath 参数
* @param dist_xlsPath 参数
* @throws IOException 异常
*/
public void writeExcel(List<Row> rowList, String src_xlsPath, String dist_xlsPath) throws IOException {
// 判断文件路径是否为空
if (dist_xlsPath == null || dist_xlsPath.equals("")) {
out("文件路径不能为空");
throw new IOException("文件路径不能为空");
}
// 判断文件路径是否为空
if (src_xlsPath == null || src_xlsPath.equals("")) {
out("文件路径不能为空");
throw new IOException("文件路径不能为空");
}
// 判断列表是否有数据，如果没有数据，则返回
if (rowList == null || rowList.size() == 0) {
out("文档为空");
return;
}
// 判断文件是否存在
File file = new File(dist_xlsPath);
if (file.exists() && ISOVERWRITE) {
// 如果复写，则删除后
file.delete();
this.setEXCELPATH(src_xlsPath);
} else {
this.setEXCELPATH(dist_xlsPath);
}
this.initWorkbook();
// 将的内容写到Excel中
writeExcel(rowList, dist_xlsPath);
}
/**
* 获取列索引 以 cellValue 开始 第一列
* @param row 标题列
* @param cellValue 检索值
* @return int 返回值
*/
public static int getCellIndex(Row row,String cellValue) {
if(row != null && cellValue != null && !cellValue.equals("")){
int cellNum = row.getLastCellNum();
for(int i =0;i<cellNum;i++){
String value = getCellValue(row.getCell(i));
if(value != null && !value.equals("")){
if(value.startsWith(cellValue)){
return i;
}
}
}
}
return -1;
}
/**
* 读取单元格的值
*
* @param cell 参数
* @return String 返回值
*/
public static String getCellValue(Cell cell) {
Object result = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
DecimalFormat df = new DecimalFormat("0");
//应用DecimalFormat类对科学计数法格局的数字进行格局化
result = df.format(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR:
result = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
break;
}
}
return result.toString();
}
/**
* 通用读取Excel
*
* @return List 返回类型
*/
public List<Row> readExcel() {
List<Row> rowList = new ArrayList<Row>();
int sheetCount = 1;//需要操作的sheet数量
Sheet sheet = null;
if (ONLYREADONESHEET) { //只操作一个sheet
// 获取设定操作的sheet(如果设定了名称，按名称查，否则按索引值查)
sheet = SELECTEDSHEETNAME.equals("") ? workbook.getSheetAt(SELECTEDSHEETIDX) : workbook.getSheet(SELECTEDSHEETNAME);
} else { //操作多个sheet
sheetCount = workbook.getNumberOfSheets();//获取可以操作的总数量
}
// 获取sheet数目
for (int t = STARTSHEETIDX; t < sheetCount + ENDSHEETIDX; t++) {
// 获取设定操作的sheet
if (!ONLYREADONESHEET) {
sheet = workbook.getSheetAt(t);
}
//获取最后行号
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum > 0) { //如果>0，表示有数据
out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：");
}
Row row = null;
// 循环读取
for (int i = STARTREADPOS; i <= lastRowNum + ENDREADPOS; i++) {
row = sheet.getRow(i);
if (row != null) {
rowList.add(row);
out("第" + (i + 1) + "行：", false);
// 获取每一单元格的值
for (int j = 0; j < row.getLastCellNum(); j++) {
String value = getCellValue(row.getCell(j));
if (!value.equals("")) {
out(value + " | ", false);
}
}
out("");
}
}
}
return rowList;
}
/**
* 修改Excel，并另存为
*
* @param rowList 参数
* @param outPath 参数
*/
private void writeExcel(List<Row> rowList, String outPath) {
if (workbook == null) {
out("操作文档不能为空！");
return;
}
Sheet sheet = workbook.getSheetAt(0);// 修改第一个sheet中的值
// 如果每次重写，那么则从开始读取的位置写，否则果获取源文件最新的行。
int lastRowNum = ISOVERWRITE ? STARTREADPOS : sheet.getLastRowNum() + 1;
int t = 0;//记录最新添加的行数
out("要添加的数据总条数为：" + rowList.size());
for (Row row : rowList) {
if (row == null) continue;
// 判断是否已经存在该数据
int pos = findInExcel(sheet, row);
Row r = null;// 如果数据行已经存在，则获取后重写，否则自动创建新行。
if (pos >= 0) {
sheet.removeRow(sheet.getRow(pos));
r = sheet.createRow(pos);
} else {
r = sheet.createRow(lastRowNum + t++);
}
//用于设定单元格样式
CellStyle newstyle = workbook.createCellStyle();
//循环为新行创建单元格
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
Cell cell = r.createCell(i);// 获取数据类型
cell.setCellValue(getCellValue(row.getCell(i)));// 复制单元格的值到新的单元格
// cell.setCellStyle(row.getCell(i).getCellStyle());//出错
if (row.getCell(i) == null) continue;
copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // 获取原来的单元格样式
cell.setCellStyle(newstyle);// 设置样式
// sheet.autoSizeColumn(i);//自动跳转列宽度
}
}
out("其中检测到重复条数为:" + (rowList.size() - t) + " ，追加条数为：" + t);
// 统一设定合并单元格
setMergedRegion(sheet);
try {
// 重新将数据写入Excel中
FileOutputStream outputStream = new FileOutputStream(outPath);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
workbook.close();
} catch (Exception e) {
out("写入Excel时发生错误！ ");
e.printStackTrace();
}
}
/**
* 查找某行数据是否在Excel表中存在，返回行数。
*
* @param sheet 参数
* @param row 参数
* @return int 行数
*/
private int findInExcel(Sheet sheet, Row row) {
int pos = -1;
try {
// 如果覆写目标文件，或者不需要比较，则直接返回
if (ISOVERWRITE || !ISNEEDCOMPARE) {
return pos;
}
for (int i = STARTREADPOS; i <= sheet.getLastRowNum() + ENDREADPOS; i++) {
Row r = sheet.getRow(i);
if (r != null && row != null) {
String v1 = getCellValue(r.getCell(COMPAREPOS));
String v2 = getCellValue(row.getCell(COMPAREPOS));
if (v1.equals(v2)) {
pos = i;
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return pos;
}
/**
* 复制一个单元格样式到目的单元格样式
*
* @param fromStyle 参数
* @param toStyle 参数
*/
public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle) {
toStyle.setAlignment(fromStyle.getAlignment());
// 边框和边框颜色
toStyle.setBorderBottom(fromStyle.getBorderBottom());
toStyle.setBorderLeft(fromStyle.getBorderLeft());
toStyle.setBorderRight(fromStyle.getBorderRight());
toStyle.setBorderTop(fromStyle.getBorderTop());
toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
// 背景和前景
toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
// 数据格式
toStyle.setDataFormat(fromStyle.getDataFormat());
toStyle.setFillPattern(fromStyle.getFillPattern());
// toStyle.setFont(fromStyle.getFont(null));
toStyle.setHidden(fromStyle.getHidden());
toStyle.setIndention(fromStyle.getIndention());// 首行缩进
toStyle.setLocked(fromStyle.getLocked());
toStyle.setRotation(fromStyle.getRotation());// 旋转
toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
toStyle.setWrapText(fromStyle.getWrapText());
}
/**
* 获取合并单元格的值
*
* @param sheet 参数
*/
public void setMergedRegion(Sheet sheet) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
// 获取合并单元格位置
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstRow = ca.getFirstRow();
if (STARTREADPOS - 1 > firstRow) {// 如果第一个合并单元格格式在正式数据的上面，则跳过。
continue;
}
int lastRow = ca.getLastRow();
int mergeRows = lastRow - firstRow;// 合并的行数
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
// 根据合并的单元格位置和大小，调整所有的数据行格式，
for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) {
// 设定合并单元格
sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn));
j = j + mergeRows;// 跳过已合并的行
}
}
}
public Workbook getWorkbook() {
if (workbook == null) {
this.initWorkbook();
}
return workbook;
}
public Sheet getSheet(String sheetName) {
return workbook.getSheet(sheetName);
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 模板sheet页（需要设置下拉框的sheet）
* @param textList 下拉框显示的内容
* @param firstRow 添加下拉框对应开始行
* @param endRow 添加下拉框对应结束行
* @param firstCol 添加下拉框对应开始列
* @param endCol 添加下拉框对应结束列
* @return HSSFSheet 设置好的sheet.
*/
public Sheet setValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 加载下拉列表内容
// 数据有效性对象
DataValidation dataValidation = null;
if (this.EXCELPATH.endsWith("xlsx")) {
DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
dataValidation = dvHelper.createValidation(new XSSFDataValidationConstraint(textList), regions);
//data_validation_list.setShowErrorBox(true);
} else {
dataValidation = new HSSFDataValidation(regions, DVConstraint.createExplicitListConstraint(textList));
}
sheet.addValidationData(dataValidation);
return sheet;
}
public void export() {
try {
// 重新将数据写入Excel中
FileOutputStream outputStream = new FileOutputStream(this.EXCELPATH);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
workbook.close();
} catch (Exception e) {
out("写入Excel时发生错误！ ");
e.printStackTrace();
}
}
/**
* 打印消息，
*
* @param msg 消息内容
* @param msg 换行
*/
private void out(String msg) {
if (PRINTMSG) {
out(msg, true);
}
}
/**
* 打印消息，
*
* @param msg 消息内容
* @param tr 换行
*/
private void out(String msg, boolean tr) {
if (PRINTMSG) {
System.out.print(msg + (tr ? "\n" : ""));
}
}
/**
* @return the eXCELPATH
*/
public String getEXCELPATH() {
return EXCELPATH;
}
/**
* @param eXCELPATH the eXCELPATH to set
*/
public void setEXCELPATH(String excelpath) {
EXCELPATH = excelpath;
}
/**
* @return the iSNEEDCOMPARE
*/
public boolean isISNEEDCOMPARE() {
return ISNEEDCOMPARE;
}
/**
* @param iSNEEDCOMPARE the iSNEEDCOMPARE to set
*/
public void setISNEEDCOMPARE(boolean isneedcompare) {
ISNEEDCOMPARE = isneedcompare;
}
public int getCOMPAREPOS() {
return COMPAREPOS;
}
public void setCOMPAREPOS(int comparepos) {
this.COMPAREPOS = comparepos;
}
public int getSTARTREADPOS() {
return STARTREADPOS;
}
public void setSTARTREADPOS(int startreadpos) {
this.STARTREADPOS = startreadpos;
}
public int getENDREADPOS() {
return ENDREADPOS;
}
public void setENDREADPOS(int endreadpos) {
this.ENDREADPOS = endreadpos;
}
public boolean ISOVERWRITE() {
return ISOVERWRITE;
}
public void setOverWrite(boolean isoverwrite) {
this.ISOVERWRITE = isoverwrite;
}
public boolean isONLYREADONESHEET() {
return ONLYREADONESHEET;
}
public void setONLYREADONESHEET(boolean onlyreadonesheet) {
this.ONLYREADONESHEET = onlyreadonesheet;
}
public int getSELECTEDSHEETIDX() {
return SELECTEDSHEETIDX;
}
public void setSELECTEDSHEETIDX(int selectedsheetidx) {
this.SELECTEDSHEETIDX = selectedsheetidx;
}
public String getSELECTEDSHEETNAME() {
return SELECTEDSHEETNAME;
}
public void setSELECTEDSHEETNAME(String selectedsheetname) {
this.SELECTEDSHEETNAME = selectedsheetname;
}
public int getSTARTSHEETIDX() {
return STARTSHEETIDX;
}
public void setSTARTSHEETIDX(int startsheetidx) {
this.STARTSHEETIDX = startsheetidx;
}
public int getENDSHEETIDX() {
return ENDSHEETIDX;
}
public void setENDSHEETIDX(int endsheetidx) {
this.ENDSHEETIDX = endsheetidx;
}
public boolean isPRINTMSG() {
return PRINTMSG;
}
public void setPRINTMSG(boolean printmsg) {
this.PRINTMSG = printmsg;
}
}Excel导入导出工具类
最新推荐文章于 2024-08-18 23:51:42 发布
扫一扫
1639
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
