工具类
/**
*
* @Title: exportExcel
* @Description: 导出Excel
* @param response
* @param fileName 导出Excel 名称
* @param data list集合
* @param titleNames 表格列名 value 数组
* @param collNames 工作表名称 value 数组
* @return void 返回类型
* @throws
*/
public static void exportExcel(
HttpServletResponse response,
String fileName,
List<Object> data,
String[] titleNames,
String[] collNames){
Map<String, List<Object>> data1 = new HashMap<String, List<Object>>();
Map<String, String[]> titleNames1 = new HashMap<String, String[]>();
Map<String, String[]> collNames1 = new HashMap<String, String[]>();
data1.put(fileName, data);
titleNames1.put(fileName, titleNames);
collNames1.put(fileName, collNames);
exportExcel(response, fileName, data1, titleNames1, collNames1);
}
/**
*
* @Title: exportExcel
* @Description: 导出Excel
* @param response
* @param fileName 导出Excel 名称
* @param data 数据 key 工作表名称 value list集合
* @param titleNames 表格列名 key 工作表名称 value 数组
* @param collNames list集合实体名称 key 工作表名称 value 数组
* @return void 返回类型
* @throws
*/
public static void exportExcel(
HttpServletResponse response,
String fileName,
Map<String, List<Object>> data,
Map<String, String[]> titleNames,
Map<String, String[]> collNames){
OutputStream out = null;
try {
HSSFWorkbook workbook = new HSSFWorkbook();
Set<String> keySet = data.keySet();
Iterator<String> keys = keySet.iterator();
while(keys.hasNext()){
String key = keys.next();
List<Object> arrayList = data.get(key);
HSSFSheet sheet = workbook.createSheet(key);
String[] titles = titleNames.get(key);
int colNum = titles.length;
int rowNO = 0;
//1. titleCaption
createExcelRow(workbook, sheet, rowNO, -1, colNum, fileName); // , 250, "bold", "center"
//2.
rowNO++;
createExcelRow(workbook, sheet, rowNO, 200, colNum,
"制 表 日 期: " + new java.text.SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date()), 180, "normal", "right");
//3.columnTitleHeader
rowNO++;
createColumnHeader(workbook,sheet, rowNO, 300, titles);
//4.数据行 循环创建中间的单元格的各项的值
rowNO++;
String colls[] = collNames.get(key);
String[][] strings=new String[arrayList.size()][titles.length];
for (int i=0;i<arrayList.size();i++) {
Object t = arrayList.get(i);
@SuppressWarnings("unchecked")
Class<Object> classT = (Class<Object>) arrayList.get(i).getClass();
for(int j=0;j<titles.length;j++){
System.out.println("get"+toUpperCaseFirstOne(colls[j]));
if(colls[j].contains(".")){
String str[] = colls[j].split(".");
String packageUrl = classT.getDeclaredField(str[0]).getType().getName();
Class<?> packageObj = Class.forName(packageUrl);
Object object = packageObj.newInstance();
Method method = packageObj.getMethod("get"+toUpperCaseFirstOne(str[1]));
Object value = method.invoke(object, new Object[0]);
//if(!ObjectUtils.isEmpty(value))
strings[i][j]= value.toString();
// else
// strings[i][j]= "-";
}else{
Method method = classT.getMethod("get"+toUpperCaseFirstOne(colls[j]));
Object object = method.invoke(t, new Object[0]);
//if(!ObjectUtils.isEmpty(object))
strings[i][j]= object.toString();
// else
// strings[i][j]= "-";
}
}
}
sheet = createColumnData(workbook, sheet, rowNO, strings, arrayList.size()+3);
createSummaryRow(workbook, sheet, colNum, "行数合计：" + strings.length, 180, "normal", "right");
}
fileName = fileName+new SimpleDateFormat("yyyyMMdd").format(new Date());
out = response.getOutputStream();
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"), "ISO8859-1" )+".xls");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//获取单元格的值
public static String getCellValue(Cell cell) {
String value = "";
//if(!ObjectUtils.isEmpty(cell)){
// 以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue() + "";
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
value = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
value = "";
break;
default:
value = "";
break;
}
//}
return value;
}
/**
* 创建通用的Excel空白行信息
*
* @param workbook
* 如果为空 则没有样式
* @param sheet
* (创建sheet)
* @param rowNO
* 报表的单行行号(创建第几行)
* @param rowHeight
* 报表的单行行高
* @param colNum
* 报表的总列数 (合并)
*/
public void createExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowNO, int rowHeight, int colNum) {
createExcelRow(workbook, sheet, rowNO, -1, colNum, null, -1, null, null);
}
/**
* 创建通用的Excel带标题行信息
*
* @param workbook
* 如果为空 则没有样式
* @param sheet
* (创建sheet)
* @param rowNO
* 报表的单行行号(创建第几行)
* @param rowHeight
* 报表的单行行高
* @param colNum
* 报表的总列数 (合并)
* @param fontCaption
* 报表行中显示的字符
*/
public static void createExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowNO, int rowHeight, int colNum,
String fontCaption) {
createExcelRow(workbook, sheet, rowNO, -1, colNum, fontCaption, -1, null, null);
}
/**
* 创建通用的Excel行信息
*
* @param workbook
* 如果为空 则没有样式
* @param sheet
* (创建sheet)
* @param rowNO
* 报表的单行行号(创建第几行)
* @param rowHeight
* 报表的单行行高
* @param colNum
* 报表的总列数 (合并)
* @param fontCaption
* 报表行中显示的字符
* @param fontSize
* 字体的大小 (字体大小 默认 200)
* @param fontWeight
* 报表表头显示的字符
* @param align
* 字体水平位置 (center中间 right右 left左)
* @param colNum
* 报表的列数
*/
public static void createExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowNO, int rowHeight, int colNum,
String fontCaption, int fontSize, String fontWeight, String align) {
if (colNum < 0) {
colNum = 100;
}
HSSFRow row = sheet.createRow(rowNO); // 创建第一行
row.setHeight((short) (rowHeight < 1 ? 300 : rowHeight)); // 设置行高
HSSFCell cell = row.createCell(0);// 设置第一行
cell.setCellType(HSSFCell.ENCODING_UTF_16); // 定义单元格为字符串类型
cell.setCellValue(new HSSFRichTextString(fontCaption));
sheet.addMergedRegion(new Region(rowNO, (short) 0, rowNO, (short) (colNum - 1))); // 指定合并区域
HSSFCellStyle cellStyle = createCellFontStyle(workbook, fontSize, fontWeight, align); // 设定样式
if (cellStyle != null) {
cell.setCellStyle(cellStyle);
}
}
/**
* 设置字体样式 (字体为宋体 ，上下居中对齐，可设置左右对齐，字体粗细，字体大小 )
*
* @param workbook
* 如果为空 则没有样式
* @param fontSize
* 字体大小 默认 200
* @param fontWeight
* 字体粗细 ( 值为bold 为加粗)
* @param align
* 字体水平位置 (center中间 right右 left左)
*/
public static HSSFCellStyle createCellFontStyle(HSSFWorkbook workbook, int fontSize, String fontWeight, String align) {
if (workbook == null) {
return null;
}
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
if (align != null && align.equalsIgnoreCase("left")) {
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 指定单元格居中对齐
}
if (align != null && align.equalsIgnoreCase("right")) {
cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 指定单元格居中对齐
}
if (align != null && align.equalsIgnoreCase("center")) {
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
}
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 单元格字体
HSSFFont font = workbook.createFont();
if (fontWeight != null && fontWeight.equalsIgnoreCase("normal")) {
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
} else {
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
font.setFontName("宋体");
font.setFontHeight((short) (fontSize < 1 ? 200 : fontSize));
cellStyle.setFont(font);
// 设置字体
// HSSFFont font = workbook.createFont();
// font.setFontHeightInPoints((short) 20); //字体高度
// font.setColor(HSSFFont.COLOR_RED); //字体颜色
// font.setFontName("黑体"); //字体
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
// font.setItalic(true); //是否使用斜体
// font.setStrikeout(true); //是否使用划线
// // 添加单元格注释
// // 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器.
// HSSFPatriarch patr = sheet.createDrawingPatriarch();
// // 定义注释的大小和位置,详见文档
// HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0,
// 0, 0, (short)4, 2, (short) 6, 5));
// // 设置注释内容
// comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// // 设置注释作者. 当鼠标移动到单元格上是可以在状态栏中看到该内容.
// comment.setAuthor("Xuys.");
return cellStyle;
}
/**
* 设置报表列头
*
* @param workbook
*
* @param sheet
* (创建sheet)
* @param rowNO
* 报表的单行行号(创建第几行)
* @param rowHeight
* 报表的单行行高
* @param columnHeader
* 报表行中显示的字符
*/
public static void createColumnHeader(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, int rowHeight, String[] columnHeader) {
createColumnHeader(workbook,sheet, rowNO, rowHeight, columnHeader, -1, null, null);
}
/**
* 设置报表列头
*
* @param workbook
*
* @param sheet
* (创建sheet)
* @param rowNO
* 报表的单行行号(创建第几行)
* @param rowHeight
* 报表的单行行高
* @param columnHeader
* 报表行中显示的字符
* @param fontSize
* 字体的大小 (字体大小 默认 200)
*/
public static void createColumnHeader(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, int rowHeight, String[] columnHeader, int fontSize) {
createColumnHeader(workbook,sheet, rowNO, rowHeight, columnHeader, fontSize, null, null);
}
/**
* 设置报表列头
*
* @param workbook
*
* @param sheet
* (创建sheet)
* @param rowNO
* 报表的单行行号(创建第几行)
* @param rowHeight
* 报表的单行行高
* @param columnHeader
* 报表行中显示的字符
* @param fontSize
* 字体的大小 (字体大小 默认 200)
* @param fontWeight
* 报表表头显示的字符
* @param align
* 字体水平位置 (center中间 right右 left左)
*/
public static void createColumnHeader(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, int rowHeight, String[] columnHeader, int fontSize,
String fontWeight, String align) {
if (columnHeader == null || columnHeader.length < 1) {
return;
}
HSSFRow row = sheet.createRow(rowNO);
row.setHeight((short) rowHeight);
HSSFCellStyle cellStyle = createCellFontStyle(workbook, fontSize, fontWeight, align);
if (cellStyle != null) {
// 设置单元格背景色
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
}
HSSFCell cell = null;
for (int i = 0; i < columnHeader.length; i++) {
sheet.setColumnWidth(i, 20 * 256); // 设置列宽，20个字符宽度。宽度参数为1/256，故乘以256
cell = row.createCell(i);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
if (cellStyle != null) {
cell.setCellStyle(cellStyle);
}
cell.setCellValue(new HSSFRichTextString(columnHeader[i]));
}
}
/**
* 创建数据行
* @param workbook
*
* @param sheet
* (创建sheet)
* @param rowNO
* 报表的单行行号(创建第几行)
* @param columnData
* 报表行中显示的数据
* @param maxValue
* Excel显示的最大上限
*/
public static HSSFSheet createColumnData(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, String[][] columnData, int maxValue) {
maxValue = (maxValue < 1 || maxValue > 65535) ? 65535 : maxValue;
int currRowNO = rowNO;
for (int numNO = currRowNO; numNO < columnData.length + currRowNO; numNO++) {
if (numNO % maxValue == 0) {
sheet = workbook.createSheet();
rowNO = 0;
}
createColumnDataDesc(workbook, sheet, numNO, rowNO, currRowNO, -1, columnData);
rowNO++;
}
return sheet;
}
/**
* 创建数据行
*
* @param sheet
* (创建sheet)
* @param numNO
* 序列号
* @param rowNO
* 报表的单行行号(创建第几行)
* @param currRowNO
* 初始行号
* @param rowHeight
* 报表的单行行高
* @param columnData
* 报表行中显示的数据
*/
private static void createColumnDataDesc(HSSFWorkbook workbook,HSSFSheet sheet, int numNO, int rowNO, int currRowNO, int rowHeight,
String[][] columnData) {
createColumnDataDesc(workbook,sheet, numNO, rowNO, currRowNO, rowHeight, columnData, -1, null, null);
}
/**
* 创建数据行
*
* @param workbook
*
* @param sheet
* (创建sheet)
* @param rowNO
* 报表的单行行号(创建第几行)
* @param rowHeight
* 报表的单行行高
* @param columnData
* 报表行中显示的数据
* @param fontSize
* 字体大小 默认 200
* @param fontWeight
* 字体粗细 ( 值为bold 为加粗)
* @param align
* 字体水平位置 (center中间 right右 left左)
* @param maxValue
* Excel显示的最大上限
*/
public static HSSFSheet createColumnData(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, int rowHeight, String[][] columnData, int fontSize,
String fontWeight, String align, int maxValue) {
maxValue = (maxValue < 1 || maxValue > 65535) ? 65535 : maxValue;
int currRowNO = rowNO;
for (int numNO = currRowNO; numNO < columnData.length + currRowNO; numNO++) {
if (numNO % maxValue == 0) {
sheet = workbook.createSheet();
rowNO = 0;
}
createColumnDataDesc(workbook,sheet, numNO, rowNO, currRowNO, rowHeight, columnData, fontSize, fontWeight, align);
rowNO++;
}
return sheet;
}
/**
* 创建数据行
*
* @param workbook
*
* @param sheet
* (创建sheet)
* @param numNO
* 序列号
* @param rowNO
* 报表的单行行号(创建第几行)
* @param currRowNO
* 初始行号
* @param rowHeight
* 报表的单行行高
* @param columnData
* 报表行中显示的数据
* @param fontSize
* 字体的大小 (字体大小 默认 200)
* @param fontWeight
* 报表表头显示的字符
* @param align
* 字体水平位置 (center中间 right右 left左)
*/
@SuppressWarnings("unused")
private static void createColumnDataDesc(HSSFWorkbook workbook,HSSFSheet sheet, int numNO, int rowNO, int currRowNO, int rowHeight,
String[][] columnData, int fontSize, String fontWeight, String align) {
if (columnData == null || columnData.length < 1) {
// return ;
}
HSSFRow row = sheet.createRow(rowNO);
row.setHeight((short) (rowHeight < 1 ? 300 : rowHeight)); // 设置行高
HSSFCellStyle cellStyle = null;// createCellFontStyle(workbook,
// fontSize, fontWeight, align);
if (cellStyle != null) {
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 指定单元格居中对齐
} else {
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 指定单元格居中对齐
}
HSSFCell cell = null;
for (int i = 0; i < columnData[numNO - currRowNO].length; i++) {
sheet.setColumnWidth(i, 20 * 256); // 设置列宽，20个字符宽度。宽度参数为1/256，故乘以256
cell = row.createCell(i);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
if (cellStyle != null) {
cell.setCellStyle(cellStyle);
}
cell.setCellValue(new HSSFRichTextString(columnData[numNO - currRowNO][i]));
}
}
/**
* 创建通用的Excel最后一行的信息 (创建合计行 (最后一行))
*
* @param workbook
* 如果为空 则没有样式
* @param sheet
* @param colNum
* 报表的总列数 (合并)
* @param fontCaption
* 报表行中显示的字符
* @param fontSize
* 字体的大小 (字体大小 默认 200)
* @param fontWeight
* 报表表头显示的字符
* @param align
* 字体水平位置 (center中间 right右 left左)
* @param colNum
* 报表的列数 (需要合并到的列索引)
*/
public static void createSummaryRow(HSSFWorkbook workbook, HSSFSheet sheet, int colNum, String fontCaption, int fontSize,
String fontWeight, String align) {
HSSFCellStyle cellStyle = createCellFontStyle(workbook, fontSize, fontWeight, align);
HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
HSSFCell sumCell = lastRow.createCell(0);
sumCell.setCellValue(new HSSFRichTextString(fontCaption));
if (cellStyle != null) {
sumCell.setCellStyle(cellStyle);
}
sheet.addMergedRegion(
new Region(sheet.getLastRowNum(), (short) 0, sheet.getLastRowNum(), (short) (colNum - 1)));// 指定合并区域
}
//首字母转小写
public static String toLowerCaseFirstOne(String s){
if(Character.isLowerCase(s.charAt(0)))
return s;
else
return (new StringBuilder()).append(Character.toLowerCase(s.charAt(0))).append(s.substring(1)).toString();
}
//首字母转大写
public static String toUpperCaseFirstOne(String s){
if(Character.isUpperCase(s.charAt(0)))
return s;
else
return (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
}
action调用
1426
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
