package com.xxx.yjfhltjb.controller.yjfhltjb;
import java.io.OutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
/**
* excel导出工具类 poi 3.17 以前的版本
*/
public class ExportExcelUtil {
/**
* @param request
* @param response
* @param wb
* @param fileName 自定义导出的文件取名（导出后文件名叫什么）
* @throws Exception
* 调用后浏览器自动生成excel
*/
public static void exportExcel(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook wb, String fileName) throws Exception {
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GB2312"), "8859_1"));
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
/**
*
* @param title 标题
*
* @param headers 表头
* 使用方法： String[] headers = {"序号","单位","机组","容量","开始","结束","工期","类型","发电类型"};
*
* @param values 表中元素
* 使用方法：
* String[][] values = new String[query.size()][headers.length];
* for (int i = 0; i < query.size(); i++) {
* Jzjxjh e = query.get(i);
* values[i][0]=e.getXh()+"";
* values[i][1]=e.getDw();
* values[i][2]=e.getJz();
* values[i][3]=e.getRl()+"";
* values[i][4]=sdf.format(e.getKs());
* }
*
* @param columnWidth 每一列的宽度
* 使用方法： 转入null 或者 int[] columnWidth={18,18,18,18,20,20,18,18,18};
*
* @return 返回 HSSFWorkbook wb
*/
public static HSSFWorkbook getHSSFWorkbook(String title, String headers[], String[][] values,Integer[] columnWidth) throws Exception {
// 创建一个HSSFWorkbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(title);
// 创建标题合并行
sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) headers.length - 1));
HSSFCellStyle style = getTitleStyle(wb);
// 设置标题字体
Font titleFont = getTitleFont(wb);
//设置粗体
// titleFont.setBold(true);
style.setFont(titleFont);
// 设置表内容样式
// 创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style1 = getContextStyle(wb);
// 产生标题行
HSSFRow hssfRow = sheet.createRow(0);
HSSFCell cell = hssfRow.createCell(0);
cell.setCellValue(title);
cell.setCellStyle(style);
// 产生表头
HSSFCellStyle hssfCellStyle = getHeadStyle(wb);
HSSFRow row1 = sheet.createRow(1);
for (int i = 0; i < headers.length; i++) {
HSSFCell hssfCell = row1.createCell(i);
hssfCell.setCellValue(headers[i]);
hssfCell.setCellStyle(hssfCellStyle);
}
//自适应列宽度(实际效果并不理想)
// sheet.autoSizeColumn(1);
//设置表头宽度
if(columnWidth!=null&&columnWidth.length>0){
for(int i =0;i<columnWidth.length;i++){
sheet.setColumnWidth(i, columnWidth[i]*256);
}
}
// 创建内容
for (int i = 0; i < values.length; i++) {
row1 = sheet.createRow(i + 2);
for (int j = 0; j < values[i].length; j++) {
// 将内容按顺序赋给对应列对象
HSSFCell hssfCell = row1.createCell(j);
hssfCell.setCellValue(values[i][j]);
hssfCell.setCellStyle(style1);
}
}
return wb;
}
/**
* @param wb
* @return 设置表内容样式 创建单元格，并设置值表头 设置表头居中
*/
private static HSSFCellStyle getContextStyle(HSSFWorkbook wb) throws Exception {
HSSFCellStyle style1 = wb.createCellStyle();
// style1.setBorderBottom(BorderStyle.THIN);
// style1.setBorderLeft(BorderStyle.THIN);
// style1.setBorderRight(BorderStyle.THIN);
// style1.setBorderTop(BorderStyle.THIN);
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
return style1;
}
/**
* @param wb
* @return 设置标题字体
*/
private static Font getTitleFont(HSSFWorkbook wb) throws Exception {
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 14);
return titleFont;
}
/**
* @param wb
* @return 设置标题样式
*/
private static HSSFCellStyle getTitleStyle(HSSFWorkbook wb) throws Exception {
// 设置标题样式
HSSFCellStyle style = wb.createCellStyle();
// XSSFCellStyle cellStyle = wb.createCellStyle();
// style.setAlignment(HorizontalAlignment.CENTER); // 设置居中样式
// style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直
return style;
}
/**
* @param wb
* @return 设置值表头样式 设置表头居中
*/
private static HSSFCellStyle getHeadStyle(HSSFWorkbook wb) throws Exception {
// 设置值表头样式 设置表头居中
HSSFCellStyle style1 = wb.createCellStyle();
// hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置居中样式
// hssfCellStyle.setBorderBottom(BorderStyle.THIN);
// hssfCellStyle.setBorderLeft(BorderStyle.THIN);
// hssfCellStyle.setBorderRight(BorderStyle.THIN);
// hssfCellStyle.setBorderTop(BorderStyle.THIN);
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
return style1;
}
/**
* @param wb
* @param sheet
* @param starRow 从哪行开始插入
* @param rows 插入多少行
* void
* 原方法 void org.apache.poi.hssf.usermodel.HSSFSheet.shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight)
*/
public static void excelInsertRow(HSSFWorkbook wb, HSSFSheet sheet, int starRow, int rows) {
sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), rows, true, false);
starRow = starRow - 1;
for (int i = 0; i < rows; i++) {
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
short m;
starRow = starRow + 1;
sourceRow = sheet.getRow(starRow);
targetRow = sheet.createRow(starRow + 1);
targetRow.setHeight(sourceRow.getHeight());
for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
sourceCell = sourceRow.getCell(m);
targetCell = targetRow.createCell(m);
targetCell.setCellStyle(sourceCell.getCellStyle());
targetCell.setCellType(sourceCell.getCellType());
}
}
}
}
添加一个方法，可以传入cell返回一个string类型值
/**
* @param cell
* @return //获取单元格各类型值，返回字符串类型 String
*
*/
public String getCellValueByCell(Cell cell) {
// 判断是否为null或空串
if (cell == null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
int cellTypeEnum = cell.getCellType();
switch (cellTypeEnum) {
case Cell.CELL_TYPE_NUMERIC: // 数字
short format = cell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf;
// System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
sdf = new SimpleDateFormat("yyyy/MM/dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
cellValue = sdf.format(date);
} else {// 日期
sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
}
try {
cellValue = sdf.format(cell.getDateCellValue());// 日期
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
} finally {
sdf = null;
}
} else {
BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue());
cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula();
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "ERROR VALUE";
break;
default:
cellValue = "UNKNOW VALUE";
break;
}
return cellValue;
}
