使用POI操作Excel，读取、写入Excel
来源：互联网 发布：短租房网站源码php 编辑：程序博客网 时间：2024/05/02 00:07
package com.ucf.boss.utils;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.math.BigDecimal;import java.text.DecimalFormat;import java.text.NumberFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Set;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.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.ss.usermodel.Font;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFRichTextString;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;public class PoiUtils { private static final Logger LOGGER = LoggerFactory .getLogger(PoiUtils.class); /* LONG */ protected static final String LONG = "java.lang.Long"; /* SHORT */ protected static final String SHORT = "java.lang.Short"; /* INT */ protected static final String INT = "java.lang.Integer"; /* STRING */ protected static final String STRING = "java.lang.String"; /* DATE */ protected static final String DATE = "java.sql.Timestamp"; /* BIG */ protected static final String BIG = "java.math.BigDecimal"; /* CLOB */ protected static final String CLOB = "oracle.sql.CLOB"; public static void main(String[] args) throws FileNotFoundException { String path ="C:/Program Files/feiq/AutoRecv Files/IP梳理记录表(1)(2).xlsx"; File file = new File(path); InputStream inputStream = new FileInputStream(file); int count = getRecordsCountReadStream(inputStream,1, false, 0); System.out.print(count); } /** * 通过文件路径获取Excel读取行数 * @param path 文件路径，只接受xls或xlsx结尾 * @param isHeader 是否表头 * @param headerCount 表头行数 * @return count 如果文件路径为空，返回0； */ public static int getRecordsCountReadPath(String path, boolean isHeader, int headerCount) { int count = 0; if(path == null){ return count; }else if(!path.endsWith("xls") && !path.endsWith("xlsx") && !path.endsWith("XLS") && !path.endsWith("XLSX")){ return count; } try { File file = new File(path); InputStream inputStream = new FileInputStream(file); Workbook hwb = null; if(path.endsWith("xls") || path.endsWith("XLS")){ hwb = new HSSFWorkbook(inputStream); }else if(path.endsWith("xlsx") || path.endsWith("XLSX")){ hwb = new XSSFWorkbook(inputStream); } if (null==hwb) { return count; } Sheet sheet = hwb.getSheetAt(0);//暂定只取首页签 int begin = sheet.getFirstRowNum(); if(isHeader){ begin += headerCount; } int end = sheet.getLastRowNum(); for (int i = begin; i <= end; i++) { if (null == sheet.getRow(i)) { continue; } count++; } } catch (FileNotFoundException e) { LOGGER.error("excel解析:", e); return 0; } catch (IOException e) { LOGGER.error("excel解析:", e); return 0; } return count; } /** * 通过文件流获取Excel读取行数 * @param path 文件路径 * @param type 类型，0为xls，1为xlsx； * @param isHeader 是否表头 * @param headerCount 表头行数 * @return count 如果文件路径为空，返回0； */ public static int getRecordsCountReadStream(InputStream inputStream,int type, boolean isHeader, int headerCount) { int count = 0; if(type != 0 && type != 1){ return count; } try { Workbook hwb = null; if(type == 0){ hwb = new HSSFWorkbook(inputStream); }else if(type == 1) { hwb = new XSSFWorkbook(inputStream); } if (null==hwb) { return count; } Sheet sheet = hwb.getSheetAt(0); int begin = sheet.getFirstRowNum(); if(isHeader){ begin += headerCount; } int end = sheet.getLastRowNum(); for (int i = begin; i <= end; i++) { if (null == sheet.getRow(i)) { continue; } count++; } } catch (FileNotFoundException e) { LOGGER.error("excel解析:", e); return 0; } catch (IOException e) { LOGGER.error("excel解析:", e); return 0; } return count; } /** * 通过文件流获取Excel读取 * @param path 文件路径 * @param type 类型，0为xls，1为xlsx； * @param isHeader 是否表头 * @param headerCount 表头行数 * @return poiList 如果文件路径为空，返回0； */ public static List<String[]> readRecordsInputStream(InputStream inputStream, int type, boolean isHeader, int headerCount) { List<String[]> poiList = new ArrayList<String[]>(); if(type != 0 && type != 1){ return null; } if(type == 0){ poiList = readXLSRecords(inputStream, isHeader, headerCount); }else if(type == 1) { poiList = readXLSXRecords(inputStream, isHeader, headerCount); } return poiList; } /** * 通过文件路径获取Excel读取 * @param path 文件路径，只接受xls或xlsx结尾 * @param isHeader 是否表头 * @param headerCount 表头行数 * @return count 如果文件路径为空，返回0； */ public static List<String[]> readRecordsInputPath(String path, boolean isHeader, int headerCount) { List<String[]> poiList = new ArrayList<String[]>(); if(path == null){ return null; }else if(!path.endsWith("xls") && !path.endsWith("xlsx") && !path.endsWith("XLS") && !path.endsWith("XLSX")){ return null; } File file = new File(path); try { InputStream inputStream = new FileInputStream(file); if(path.endsWith("xls") || path.endsWith("XLS")){ poiList = readXLSRecords(inputStream, isHeader, headerCount); }else if(path.endsWith("xlsx") || path.endsWith("XLSX")){ poiList = readXLSXRecords(inputStream, isHeader, headerCount); } } catch (Exception e) { LOGGER.error("excel解析:", e); return null; } return poiList; } /** * 解析EXCEL2003文件流 * 如果一行记录的行中或行尾出现空格，POI工具类可能会跳过空格不做处理，所以默认第一行是表头，所有待解析的记录都以表头为准 * @param inputStream 输入流 * @param isHeader 是否要跳过表头 * @param headerCount 表头占用行数 * @return 返回一个字符串数组List */ public static List<String[]> readXLSRecords(InputStream inputStream, boolean isHeader, int headerCount) { List<String[]> poiList = new ArrayList<String[]>(); try{ HSSFWorkbook wbs = new HSSFWorkbook(inputStream); HSSFSheet childSheet = wbs.getSheetAt(0); //获取表头 int begin = childSheet.getFirstRowNum(); HSSFRow firstRow = childSheet.getRow(begin); int cellTotal = firstRow.getPhysicalNumberOfCells(); //是否跳过表头解析数据 if(isHeader){ begin += headerCount; } //逐行获取单元格数据 for(int i = begin;i <= childSheet.getLastRowNum();i++){ HSSFRow row = childSheet.getRow(i); //一行的所有单元格格式都是常规的情况下，返回的row为null if(null != row){ String[] cells = new String[cellTotal]; for(int k=0;k<cellTotal;k++){ HSSFCell cell = row.getCell(k); cells[k] = getStringXLSCellValue(cell); } poiList.add(cells); } } }catch(Exception e){ LOGGER.error("excel解析:", e); return null; } return poiList; } /** * 解析EXCEL2003文件流 * 如果一行记录的行中或行尾出现空格，POI工具类可能会跳过空格不做处理，所以默认第一行是表头，所有待解析的记录都以表头为准 * 该解析方法只适用于表头占用一行的情况 * @param inputStream 输入流 * @param isHeader 是否要跳过表头 * @param headerCount 表头占用行数 * @param maxColNum 最大列数，适用于多表头 * @return 返回一个字符串数组List */ public static List<String[]> readXLSRecords(InputStream inputStream, boolean isHeader, int headerCount, int maxColNum) { List<String[]> poiList = new ArrayList<String[]>(); try{ HSSFWorkbook wbs = new HSSFWorkbook(inputStream); HSSFSheet childSheet = wbs.getSheetAt(0); //获取表头 int begin = childSheet.getFirstRowNum(); //HSSFRow firstRow = childSheet.getRow(begin); //int cellTotal = firstRow.getPhysicalNumberOfCells(); //是否跳过表头解析数据 if(isHeader){ begin += headerCount; } //逐行获取单元格数据 for(int i = begin;i <= childSheet.getLastRowNum();i++){ HSSFRow row = childSheet.getRow(i); //一行的所有单元格格式都是常规的情况下，返回的row为null String[] cells = new String[maxColNum]; //空行对应空串数组 for(int k=0;k<maxColNum;k++){ HSSFCell cell = row==null?null:row.getCell(k); cells[k] = getStringXLSCellValue(cell); } poiList.add(cells); } }catch(Exception e){ LOGGER.error("excel解析:", e); return null; } return poiList; } /** * 解析EXCEL2007文件流 * 如果一行记录的行中或行尾出现空格，POI工具类可能会跳过空格不做处理，所以默认第一行是表头，所有待解析的记录都以表头为准 * 该处理方法中，表头对应都占用一行 * @param inputStream 输入流 * @param isHeader 是否要跳过表头 * @param headerCount 表头占用行数 * @return 返回一个字符串数组List */ public static List<String[]> readXLSXRecords(InputStream inputStream, boolean isHeader, int headerCount) { List<String[]> poiList = new ArrayList<String[]>(); try{ XSSFWorkbook wbs = new XSSFWorkbook(inputStream); XSSFSheet childSheet = wbs.getSheetAt(0); //获取表头 int begin = childSheet.getFirstRowNum(); XSSFRow firstRow = childSheet.getRow(begin); int cellTotal = firstRow.getPhysicalNumberOfCells(); //是否跳过表头解析数据 if(isHeader){ begin += headerCount; } for(int i = begin;i <= childSheet.getLastRowNum();i++){ XSSFRow row = childSheet.getRow(i); //一行的所有单元格格式都是常规的情况下，返回的row为null if(null != row){ String[] cells = new String[cellTotal]; for(int k=0;k<cellTotal;k++){ XSSFCell cell = row.getCell(k); cells[k] = getStringXLSXCellValue(cell); } poiList.add(cells); } } }catch(Exception e){ LOGGER.error("excel解析:", e); return null; } return poiList; } /** * 解析EXCEL2007文件流 * 如果一行记录的行中或行尾出现空格，POI工具类可能会跳过空格不做处理，所以默认第一行是表头，所有待解析的记录都以表头为准 * 该处理方法中，表头对应都占用一行 * @param inputStream 输入流 * @param isHeader 是否要跳过表头 * @param headerCount 表头占用行数 * @param maxColNum 最大列数，适用于多表头的情况 * @return 返回一个字符串数组List */ public static List<String[]> readXLSXRecords(InputStream inputStream, boolean isHeader, int headerCount, int maxColNum) { List<String[]> poiList = new ArrayList<String[]>(); try{ XSSFWorkbook wbs = new XSSFWorkbook(inputStream); XSSFSheet childSheet = wbs.getSheetAt(0); //获取表头 int begin = childSheet.getFirstRowNum(); //XSSFRow firstRow = childSheet.getRow(begin); //int cellTotal = firstRow.getPhysicalNumberOfCells(); //是否跳过表头解析数据 if(isHeader){ begin += headerCount; } for(int i = begin;i <= childSheet.getLastRowNum();i++){ XSSFRow row = childSheet.getRow(i); //一行的所有单元格格式都是常规的情况下，返回的row为null String[] cells = new String[maxColNum]; //空行对应空串数组 for(int k=0;k<maxColNum;k++){ XSSFCell cell = row==null?null:row.getCell(k); cells[k] = getStringXLSXCellValue(cell); } poiList.add(cells); } }catch(Exception e){ LOGGER.error("excel解析:", e); return null; } return poiList; } /** * 获取单元格数据内容为字符串类型的数据 * * @param cell Excel单元格 * @return String 单元格数据内容 */ private static String getStringXLSCellValue(HSSFCell cell) { String strCell = ""; if (cell == null) { return ""; } //将数值型参数转成文本格式，该算法不能保证1.00这种类型数值的精确度 DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance(); StringBuffer sb = new StringBuffer(); sb.append("0"); df.applyPattern(sb.toString()); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: strCell = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: double value = cell.getNumericCellValue(); while(Double.parseDouble(df.format(value))!=value){ if("0".equals(sb.toString())){ sb.append(".0"); }else{ sb.append("0"); } df.applyPattern(sb.toString()); } strCell = df.format(value); break; case HSSFCell.CELL_TYPE_BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: strCell = ""; break; default: strCell = ""; break; } if (strCell == null || "".equals(strCell)) { return ""; } return strCell; } /** * 获取单元格数据内容为字符串类型的数据 * * @param cell Excel单元格 * @return String 单元格数据内容 */ private static String getStringXLSXCellValue(XSSFCell cell) { String strCell = ""; if (cell == null) { return ""; } //将数值型参数转成文本格式，该算法不能保证1.00这种类型数值的精确度 DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance(); StringBuffer sb = new StringBuffer(); sb.append("0"); df.applyPattern(sb.toString()); switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: strCell = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: double value = cell.getNumericCellValue(); while(Double.parseDouble(df.format(value))!=value){ if("0".equals(sb.toString())){ sb.append(".0"); }else{ sb.append("0"); } df.applyPattern(sb.toString()); } strCell = df.format(value); break; case XSSFCell.CELL_TYPE_BOOLEAN: strCell = String.valueOf(cell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: strCell = ""; break; default: strCell = ""; break; } if (strCell == null || "".equals(strCell)) { return ""; } return strCell; } /** * 导出Excel * @param response * @param request * @param title * @param map key为标题，list为数据，单表头导出 * @param type 0为xls，1为xlsx */ public static void output(HttpServletResponse response,HttpServletRequest request, String title,Map<String, List<Object>> map, int type) throws Exception{ if(type != 0 && type != 1){ throw new Exception("无效的excel导出类型，type=0表示xls，type=1表示xlsx"); } List<Map<String, List<Object>>> list = new ArrayList<Map<String, List<Object>>>(); list.add(map); if(type == 0){ outputXLS(response, request, title, list); }else if(type == 1) { outputXLSX(response, request, title, list); } } /** * 导出Excel * @param response * @param request * @param title * @param List<Map> 支持多表头导出 * @param type 0为xls，1为xlsx */ public static void output(HttpServletResponse response,HttpServletRequest request, String title, List<Map<String, List<Object>>> list, int type) throws Exception{ if(type != 0 && type != 1){ throw new Exception("无效的excel导出类型，type=0表示xls，type=1表示xlsx"); } if(type == 0){ outputXLS(response, request, title, list); }else if(type == 1) { outputXLSX(response, request, title, list); } } /** * 导出Excel * @param response * @param request * @param title * @param List<Map> 支持多表头按顺序导出 * @param type 0为xls，1为xlsx */ public static void outputByColId(HttpServletResponse response,HttpServletRequest request, String title, List<Map<String, List<Object>>> list, int type) throws Exception{ if(type != 0 && type != 1){ throw new Exception("无效的excel导出类型，type=0表示xls，type=1表示xlsx"); } if(type == 0){ outputXLSByCol(response, request, title, list); }else if(type == 1) { outputXLSByCol(response, request, title, list); } } public static void outputXLS(HttpServletResponse response,HttpServletRequest request, String title,List<Map<String, List<Object>>> list)throws Exception{ //输出流定义 OutputStream os = response.getOutputStream(); byte[] fileNameByte = (title + ".xls").getBytes("GBK"); String filename = new String(fileNameByte, "ISO8859-1"); response.setContentType("application/x-msdownload"); response.setCharacterEncoding("UTF-8");response.setHeader("Content-Disposition", "attachment;filename=" + filename); //创建excel文件 HSSFWorkbook hssf_w_book=new HSSFWorkbook(); HSSFSheet hssf_w_sheet=hssf_w_book.createSheet(title); hssf_w_sheet.setDefaultColumnWidth(21); //固定列宽度 HSSFRow hssf_w_row=null;//创建一行 HSSFCell hssf_w_cell=null;//创建每个单元格 //定义表头单元格样式 HSSFCellStyle head_cellStyle = hssf_w_book.createCellStyle(); //定义表头字体样式 HSSFFont head_font = hssf_w_book.createFont(); head_font.setFontName("宋体");//设置头部字体为宋体 head_font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体 head_font.setFontHeightInPoints((short) 10); //字体大小 //表头单元格样式设置 head_cellStyle.setFont(head_font);//单元格样式使用字体 head_cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); head_cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);//head_cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);//head_cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//head_cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//head_cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); //定义数据单元格样式 HSSFCellStyle cellStyle_CN = hssf_w_book.createCellStyle();//创建数据单元格样式(数据库数据样式)//cellStyle_CN.setBorderBottom(XSSFCellStyle.BORDER_THIN);//cellStyle_CN.setBorderLeft(XSSFCellStyle.BORDER_THIN);//cellStyle_CN.setBorderRight(XSSFCellStyle.BORDER_THIN);//cellStyle_CN.setBorderTop(XSSFCellStyle.BORDER_THIN); //在多表头导出时，定义第一个表头出现位置 int titleFlag = 0; //遍历写入表数据的list for(Map<String, List<Object>> map : list){ //遍历map获取表头字段，并将表头字段放进String型的数组 Set<String> key = map.keySet(); String titles = ""; int count = 0; for (Iterator<String> it = key.iterator(); it.hasNext();) { if(count != 0){ titles += ";"; } titles += (String) it.next(); count++; } String[] titleArray = titles.split(";"); //表头写入位置 hssf_w_row = hssf_w_sheet.createRow(titleFlag); for(int i = 0; i < titleArray.length; i++){ hssf_w_cell = hssf_w_row.createCell(i); hssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING); hssf_w_cell.setCellValue(titleArray[i]); hssf_w_cell.setCellStyle(head_cellStyle); //hssf_w_sheet.autoSizeColumn(( short ) i ); } //循环写入表数据，获取表的总列数，然后逐行写入数据 for(int i = 0; i < map.get(titleArray[0]).size(); i++){ //定义数据行 hssf_w_row = hssf_w_sheet.createRow(i+titleFlag+1); //按行将每一列的数据写入单元格 for(int j = 0; j < titleArray.length; j++){ hssf_w_cell = hssf_w_row.createCell(j); Object in = map.get(titleArray[j]).get(i); type4ExcelXLS(in,hssf_w_cell,cellStyle_CN); //hssf_w_sheet.autoSizeColumn(( short ) i ); } } //下一个表头的写入位置，和上一个表头数据之间隔一行 titleFlag+=map.get(titleArray[0]).size()+2; } //excel文件导出 hssf_w_book.write(os); os.close(); request.getSession().setAttribute("EXCEL_FINISH", "1"); } public static void outputXLSByCol(HttpServletResponse response,HttpServletRequest request, String title,List<Map<String, List<Object>>> list)throws Exception{ //输出流定义 OutputStream os = response.getOutputStream(); byte[] fileNameByte = (title + ".xls").getBytes("GBK"); String filename = new String(fileNameByte, "ISO8859-1"); response.setContentType("application/x-msdownload"); response.setCharacterEncoding("UTF-8");response.setHeader("Content-Disposition", "attachment;filename=" + filename); //创建excel文件 HSSFWorkbook hssf_w_book=new HSSFWorkbook(); HSSFSheet hssf_w_sheet=hssf_w_book.createSheet(title); hssf_w_sheet.setDefaultColumnWidth(21); //固定列宽度 HSSFRow hssf_w_row=null;//创建一行 HSSFCell hssf_w_cell=null;//创建每个单元格 //定义表头单元格样式 HSSFCellStyle head_cellStyle = hssf_w_book.createCellStyle(); //定义表头字体样式 HSSFFont head_font = hssf_w_book.createFont(); head_font.setFontName("宋体");//设置头部字体为宋体 head_font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体 head_font.setFontHeightInPoints((short) 10); //字体大小 //表头单元格样式设置 head_cellStyle.setFont(head_font);//单元格样式使用字体 head_cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); head_cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);//head_cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);//head_cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//head_cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//head_cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); //定义数据单元格样式 HSSFCellStyle cellStyle_CN = hssf_w_book.createCellStyle();//创建数据单元格样式(数据库数据样式)//cellStyle_CN.setBorderBottom(XSSFCellStyle.BORDER_THIN);//cellStyle_CN.setBorderLeft(XSSFCellStyle.BORDER_THIN);//cellStyle_CN.setBorderRight(XSSFCellStyle.BORDER_THIN);//cellStyle_CN.setBorderTop(XSSFCellStyle.BORDER_THIN); //在多表头导出时，定义第一个表头出现位置 int titleFlag = 0; //遍历写入表数据的list for(Map<String, List<Object>> map : list){ //遍历map获取表头字段，并将表头字段放进String型的数组 Set<String> key = map.keySet(); String titles = ""; int count = 0; for (Iterator<String> it = key.iterator(); it.hasNext();) { if(count != 0){ titles += ";"; } titles += (String) it.next(); count++; } String[] titleArray = titles.split(";"); String[] temArr = new String[titleArray.length]; String[] temCol = new String[titleArray.length]; for(int k = 0;k<titleArray.length;k++){ String tem = titleArray[k]; String[] t= tem.split("_"); String c = t[1]; int n = Integer.parseInt(c); temArr[n] = t[0]+"_"+c; temCol[n] = t[0]; } //表头写入位置 hssf_w_row = hssf_w_sheet.createRow(titleFlag); for(int i = 0; i < temArr.length; i++){ hssf_w_cell = hssf_w_row.createCell(i); hssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING); hssf_w_cell.setCellValue(temCol[i]); hssf_w_cell.setCellStyle(head_cellStyle); //hssf_w_sheet.autoSizeColumn(( short ) i ); } //循环写入表数据，获取表的总列数，然后逐行写入数据 for(int i = 0; i < map.get(temArr[0]).size(); i++){ //定义数据行 hssf_w_row = hssf_w_sheet.createRow(i+titleFlag+1); //按行将每一列的数据写入单元格 for(int j = 0; j < temArr.length; j++){ hssf_w_cell = hssf_w_row.createCell(j); Object in = map.get(temArr[j]).get(i); type4ExcelXLS(in,hssf_w_cell,cellStyle_CN); //hssf_w_sheet.autoSizeColumn(( short ) i ); } } //下一个表头的写入位置，和上一个表头数据之间隔一行 titleFlag+=map.get(temArr[0]).size()+2; } //excel文件导出 hssf_w_book.write(os); os.close(); request.getSession().setAttribute("EXCEL_FINISH", "1"); } public static void outputXLSX(HttpServletResponse response,HttpServletRequest request, String title,List<Map<String, List<Object>>> list) throws Exception{ //输出流定义 OutputStream os = response.getOutputStream(); byte[] fileNameByte = (title + ".xlsx").getBytes("GBK"); String filename = new String(fileNameByte, "ISO8859-1"); response.setContentType("application/x-msdownload"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + filename); //创建excel文件 XSSFWorkbook xssf_w_book=new XSSFWorkbook(); XSSFSheet xssf_w_sheet=xssf_w_book.createSheet(title); xssf_w_sheet.setDefaultColumnWidth(21); //固定列宽度 XSSFRow xssf_w_row=null;//创建一行 XSSFCell xssf_w_cell=null;//创建每个单元格 //定义表头单元格样式 XSSFCellStyle head_cellStyle=xssf_w_book.createCellStyle(); //定义表头字体样式 XSSFFont head_font=xssf_w_book.createFont(); head_font.setFontName("宋体");//设置头部字体为宋体 head_font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体 head_font.setFontHeightInPoints((short) 10); //表头单元格样式设置 head_cellStyle.setFont(head_font);//单元格使用表头字体样式 head_cellStyle.setAlignment(HorizontalAlignment.CENTER); head_cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); head_cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); //定义数据单元格样式 XSSFCellStyle cellStyle_CN=xssf_w_book.createCellStyle();//创建数据单元格样式(数据库数据样式) cellStyle_CN.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderTop(XSSFCellStyle.BORDER_THIN); //在多表头导出时，定义第一个表头出现位置 int titleFlag = 0; //遍历写入表数据的list for(Map<String, List<Object>> map : list){ //遍历map获取表头字段，并将表头字段放进String型的数组 Set<String> key = map.keySet(); String titles = ""; int count = 0; for (Iterator<String> it = key.iterator(); it.hasNext();) { if(count != 0){ titles += ";"; } titles += (String) it.next(); count++; } String[] titleArray = titles.split(";"); //第一行写入表头 xssf_w_row=xssf_w_sheet.createRow(titleFlag); for(int i = 0; i < titleArray.length; i++){ xssf_w_cell = xssf_w_row.createCell(i); xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING); xssf_w_cell.setCellValue(titleArray[i]); xssf_w_cell.setCellStyle(head_cellStyle); //xssf_w_sheet.autoSizeColumn(( short ) i ); } //循环写入表数据 for(int i = 0; i < map.get(titleArray[0]).size(); i++){ //定义数据行 xssf_w_row=xssf_w_sheet.createRow(i+titleFlag+1); for(int j = 0; j < titleArray.length; j++){ xssf_w_cell = xssf_w_row.createCell(j); Object in = map.get(titleArray[j]).get(i); type4ExcelXLSX(in,xssf_w_cell,cellStyle_CN); //xssf_w_sheet.autoSizeColumn(( short ) i ); } } //下一个表头的写入位置，和上一个表头数据之间隔一行 titleFlag+=map.get(titleArray[0]).size()+2; } //excel文件导出 xssf_w_book.write(os); os.close(); request.getSession().setAttribute("EXCEL_FINISH", "1"); } /** * 导出Excel * @param response * @param request * @param title * @param map key为标题，list为数据，统计信息在一行 * @param map key为标题，list为数据，表数据 * @param type 0为xls，1为xlsx * @param add null不加结尾说明，非null时表示添加结尾说明 */ public static void output(HttpServletResponse response,HttpServletRequest request, String title, Map<String, List<Object>> mapTitle, Map<String, List<Object>> map, int type, String add) throws Exception{ if(type != 0 && type != 1){ throw new Exception("无效的excel导出类型，type=0表示xls，type=1表示xlsx"); } List<Map<String, List<Object>>> listTitle = new ArrayList<Map<String, List<Object>>>(); if (null!=mapTitle) listTitle.add(mapTitle); List<Map<String, List<Object>>> list = new ArrayList<Map<String, List<Object>>>(); list.add(map); if(type == 0){ outputXLS(response, request, title, listTitle, list, add); }else if(type == 1) { outputXLSX(response, request, title, listTitle, list, add); } } public static void outputXLS(HttpServletResponse response,HttpServletRequest request, String title,List<Map<String, List<Object>>> listTitle,List<Map<String, List<Object>>> list, String add)throws Exception{ //输出流定义 OutputStream os = response.getOutputStream(); byte[] fileNameByte = (title + ".xls").getBytes("GBK"); String filename = new String(fileNameByte, "ISO8859-1"); response.setContentType("application/x-msdownload"); response.setCharacterEncoding("UTF-8");response.setHeader("Content-Disposition", "attachment;filename=" + filename); //创建excel文件 HSSFWorkbook hssf_w_book=new HSSFWorkbook(); HSSFSheet hssf_w_sheet=hssf_w_book.createSheet(title); hssf_w_sheet.setDefaultColumnWidth(21); //固定列宽度 HSSFRow hssf_w_row=null;//创建一行 HSSFCell hssf_w_cell=null;//创建每个单元格 //定义表头单元格样式 HSSFCellStyle head_cellStyle = hssf_w_book.createCellStyle(); //定义表头字体样式 HSSFFont head_font = hssf_w_book.createFont(); head_font.setFontName("宋体");//设置头部字体为宋体 head_font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体 head_font.setFontHeightInPoints((short) 10); //字体大小 //表头单元格样式设置 head_cellStyle.setFont(head_font);//单元格样式使用字体 head_cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); head_cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); head_cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); //定义数据单元格样式 HSSFCellStyle cellStyle_CN = hssf_w_book.createCellStyle();//创建数据单元格样式(数据库数据样式) cellStyle_CN.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderTop(XSSFCellStyle.BORDER_THIN); //在多表头导出时，定义第一个表头出现位置 int titleFlag = 0; List<List<Map<String, List<Object>>>> listDatas = new ArrayList<List<Map<String, List<Object>>>>(); if (null!=listTitle) listDatas.add(listTitle); listDatas.add(list); for (List<Map<String, List<Object>>> listData : listDatas) { //遍历写入表数据的list for(Map<String, List<Object>> map : listData){ //遍历map获取表头字段，并将表头字段放进String型的数组 Set<String> key = map.keySet(); String titles = ""; int count = 0; for (Iterator<String> it = key.iterator(); it.hasNext();) { if(count != 0){ titles += ";"; } titles += (String) it.next(); count++; } String[] titleArray = titles.split(";"); //表头写入位置 hssf_w_row = hssf_w_sheet.createRow(titleFlag); for(int i = 0; i < titleArray.length; i++){ hssf_w_cell = hssf_w_row.createCell(i); hssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING); hssf_w_cell.setCellValue(titleArray[i]); hssf_w_cell.setCellStyle(head_cellStyle); //hssf_w_sheet.autoSizeColumn(( short ) i ); } //循环写入表数据，获取表的总列数，然后逐行写入数据 for(int i = 0; i < map.get(titleArray[0]).size(); i++){ //定义数据行 hssf_w_row = hssf_w_sheet.createRow(i+titleFlag+1); //按行将每一列的数据写入单元格 for(int j = 0; j < titleArray.length; j++){ hssf_w_cell = hssf_w_row.createCell(j); Object in = map.get(titleArray[j]).get(i); type4ExcelXLS(in,hssf_w_cell,cellStyle_CN); //hssf_w_sheet.autoSizeColumn(( short ) i ); } } //下一个表头的写入位置，和上一个表头数据之间隔一行 titleFlag+=map.get(titleArray[0]).size()+1; } } if (null!=add) { //定义表头单元格样式 HSSFCellStyle head_cellStyle2 = hssf_w_book.createCellStyle(); //表头单元格样式设置 head_cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); head_cellStyle2.setAlignment(HSSFCellStyle.ALIGN_LEFT); head_cellStyle2.setWrapText(true); //定义表头字体样式 HSSFFont head_font2 = hssf_w_book.createFont(); head_font2.setFontName("宋体");//设置头部字体为宋体 head_font2.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体 head_font2.setFontHeightInPoints((short) 9); //字体大小 head_font2.setColor(Font.COLOR_RED); HSSFFont head_font3 = hssf_w_book.createFont(); head_font3.setFontName("宋体");//设置头部字体为宋体 head_font3.setFontHeightInPoints((short) 9); //字体大小 head_font3.setColor(Font.COLOR_RED); HSSFRichTextString ts= new HSSFRichTextString(add); ts.applyFont(0,30,head_font2); ts.applyFont(30,ts.length(),head_font3); titleFlag+=3; hssf_w_row = hssf_w_sheet.createRow(titleFlag); hssf_w_cell = hssf_w_row.createCell(0); hssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING); hssf_w_cell.setCellValue(ts); hssf_w_cell.setCellStyle(head_cellStyle2); hssf_w_sheet.addMergedRegion(new CellRangeAddress(titleFlag,titleFlag+8,0,4)); } //excel文件导出 hssf_w_book.write(os); os.close(); } public static void outputXLSX(HttpServletResponse response,HttpServletRequest request, String title,List<Map<String, List<Object>>> listTitle,List<Map<String, List<Object>>> list, String add) throws Exception{ //输出流定义 OutputStream os = response.getOutputStream(); byte[] fileNameByte = (title + ".xlsx").getBytes("GBK"); String filename = new String(fileNameByte, "ISO8859-1"); response.setContentType("application/x-msdownload"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + filename); //创建excel文件 XSSFWorkbook xssf_w_book=new XSSFWorkbook(); XSSFSheet xssf_w_sheet=xssf_w_book.createSheet(title); xssf_w_sheet.setDefaultColumnWidth(21); //固定列宽度 XSSFRow xssf_w_row=null;//创建一行 XSSFCell xssf_w_cell=null;//创建每个单元格 //定义表头单元格样式 XSSFCellStyle head_cellStyle=xssf_w_book.createCellStyle(); //定义表头字体样式 XSSFFont head_font=xssf_w_book.createFont(); head_font.setFontName("宋体");//设置头部字体为宋体 head_font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体 head_font.setFontHeightInPoints((short) 10); //表头单元格样式设置 head_cellStyle.setFont(head_font);//单元格使用表头字体样式 head_cellStyle.setAlignment(HorizontalAlignment.CENTER); head_cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); head_cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); head_cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); //定义数据单元格样式 XSSFCellStyle cellStyle_CN=xssf_w_book.createCellStyle();//创建数据单元格样式(数据库数据样式) cellStyle_CN.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle_CN.setBorderTop(XSSFCellStyle.BORDER_THIN); //在多表头导出时，定义第一个表头出现位置 int titleFlag = 0; //遍历写入表数据的list for(Map<String, List<Object>> map : list){ //遍历map获取表头字段，并将表头字段放进String型的数组 Set<String> key = map.keySet(); String titles = ""; int count = 0; for (Iterator<String> it = key.iterator(); it.hasNext();) { if(count != 0){ titles += ";"; } titles += (String) it.next(); count++; } String[] titleArray = titles.split(";"); //第一行写入表头 xssf_w_row=xssf_w_sheet.createRow(titleFlag); for(int i = 0; i < titleArray.length; i++){ xssf_w_cell = xssf_w_row.createCell(i); xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING); xssf_w_cell.setCellValue(titleArray[i]); xssf_w_cell.setCellStyle(head_cellStyle); //xssf_w_sheet.autoSizeColumn(( short ) i ); } //循环写入表数据 for(int i = 0; i < map.get(titleArray[0]).size(); i++){ //定义数据行 xssf_w_row=xssf_w_sheet.createRow(i+titleFlag+1); for(int j = 0; j < titleArray.length; j++){ xssf_w_cell = xssf_w_row.createCell(j); Object in = map.get(titleArray[j]).get(i); type4ExcelXLSX(in,xssf_w_cell,cellStyle_CN); //xssf_w_sheet.autoSizeColumn(( short ) i ); } } //下一个表头的写入位置，和上一个表头数据之间隔一行 titleFlag+=map.get(titleArray[0]).size()+2; } if (null!=add) { titleFlag+=3; xssf_w_row = xssf_w_sheet.createRow(titleFlag); xssf_w_cell = xssf_w_row.createCell(0); xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING); xssf_w_cell.setCellValue(add); xssf_w_cell.setCellStyle(head_cellStyle); xssf_w_sheet.addMergedRegion(new CellRangeAddress(titleFlag,titleFlag+8,0,4)); } //excel文件导出 xssf_w_book.write(os); os.close(); } /** * 根据类型自适应格式 * @param col * @param row * @param in * @return * @throws Exception */ public static void type4ExcelXLSX(Object in, XSSFCell cell, XSSFCellStyle style) throws Exception{ if (null == in){ cell.setCellType(XSSFCell.CELL_TYPE_STRING); XSSFRichTextString xssfString = new XSSFRichTextString(""); cell.setCellValue(xssfString); cell.setCellStyle(style); }else{ in = ClobUtils.clobToString(in); String type = in.getClass().getName(); if (INT.equals(type)){ cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(String.valueOf(in))); cell.setCellStyle(style); }else if (LONG.equals(type) && String.valueOf(in).length() <= 11){ cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(String.valueOf(in))); cell.setCellStyle(style); }else if (SHORT.equals(type)){ cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(String.valueOf(in))); cell.setCellStyle(style); }else if (DATE.equals(type)) { java.sql.Timestamp sqlDate = (java.sql.Timestamp)in; Date d = new java.util.Date(sqlDate.getTime()); Date ds = new SimpleDateFormat("yyyy-MM-dd").parse(new SimpleDateFormat("yyyy-MM-dd").format(d)); SimpleDateFormat df = null; if (d.compareTo(ds) == 0){ df = new SimpleDateFormat("yyyy-MM-dd"); }else{ df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); XSSFRichTextString xssfString = new XSSFRichTextString(df.format(d)); cell.setCellValue(xssfString); cell.setCellStyle(style); }else if (in instanceof java.util.Date){ Date d = (Date)in; Date ds = new SimpleDateFormat("yyyy-MM-dd").parse(new SimpleDateFormat("yyyy-MM-dd").format(d)); SimpleDateFormat df = null; if (d.compareTo(ds) == 0){ df = new SimpleDateFormat("yyyy-MM-dd"); }else{ df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); XSSFRichTextString xssfString = new XSSFRichTextString(df.format(d)); cell.setCellValue(xssfString); cell.setCellStyle(style); }else if (STRING.equals(type)){ cell.setCellType(XSSFCell.CELL_TYPE_STRING); XSSFRichTextString xssfString = new XSSFRichTextString(String.valueOf(in)); cell.setCellValue(xssfString); cell.setCellStyle(style); }else if (in instanceof BigDecimal){ cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(String.valueOf(in))); cell.setCellStyle(style); }else{ try{ double d = Double.parseDouble(String.valueOf(in)); //if (String.valueOf(d).equals(String.valueOf(in)) && String.valueOf(in).length() <= 11){ if (String.valueOf(in).length() <= 11){ cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(d); cell.setCellStyle(style); }else{ cell.setCellType(XSSFCell.CELL_TYPE_STRING); XSSFRichTextString xssfString = new XSSFRichTextString(String.valueOf(in)); cell.setCellValue(xssfString); cell.setCellStyle(style); } }catch (Exception e) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); XSSFRichTextString xssfString = new XSSFRichTextString(""); cell.setCellValue(xssfString); cell.setCellStyle(style); LOGGER.error("excel解析:", e); } } } } /** * 根据类型自适应格式 * @param col * @param row * @param in * @return * @throws Exception */ public static void type4ExcelXLS(Object in, HSSFCell cell, HSSFCellStyle style) throws Exception{ if (null == in){ cell.setCellType(HSSFCell.CELL_TYPE_STRING); HSSFRichTextString hssfString = new HSSFRichTextString(""); cell.setCellValue(hssfString); cell.setCellStyle(style); }else{ in = ClobUtils.clobToString(in); String type = in.getClass().getName(); if (INT.equals(type)){ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(String.valueOf(in))); cell.setCellStyle(style); }else if (LONG.equals(type) && String.valueOf(in).length() <= 11){ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(String.valueOf(in))); cell.setCellStyle(style); }else if (SHORT.equals(type)){ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(String.valueOf(in))); cell.setCellStyle(style); }else if (DATE.equals(type)) { java.sql.Timestamp sqlDate = (java.sql.Timestamp)in; Date d = new java.util.Date(sqlDate.getTime()); Date ds = new SimpleDateFormat("yyyy-MM-dd").parse(new SimpleDateFormat("yyyy-MM-dd").format(d)); SimpleDateFormat df = null; if (d.compareTo(ds) == 0){ df = new SimpleDateFormat("yyyy-MM-dd"); }else{ df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } cell.setCellType(HSSFCell.CELL_TYPE_STRING); HSSFRichTextString hssfString = new HSSFRichTextString(df.format(d)); cell.setCellValue(hssfString); cell.setCellStyle(style); }else if (in instanceof java.util.Date){ Date d = (Date)in; Date ds = new SimpleDateFormat("yyyy-MM-dd").parse(new SimpleDateFormat("yyyy-MM-dd").format(d)); SimpleDateFormat df = null; if (d.compareTo(ds) == 0){ df = new SimpleDateFormat("yyyy-MM-dd"); }else{ df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } cell.setCellType(HSSFCell.CELL_TYPE_STRING); HSSFRichTextString hssfString = new HSSFRichTextString(df.format(d)); cell.setCellValue(hssfString); cell.setCellStyle(style); }else if (STRING.equals(type)){ cell.setCellType(HSSFCell.CELL_TYPE_STRING); HSSFRichTextString hssfString = new HSSFRichTextString(String.valueOf(in)); cell.setCellValue(hssfString); cell.setCellStyle(style); }else if (in instanceof BigDecimal){ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(String.valueOf(in))); cell.setCellStyle(style); }else{ try{ double d = Double.parseDouble(String.valueOf(in)); //if (String.valueOf(d).equals(String.valueOf(in)) && String.valueOf(in).length() <= 11){ if (String.valueOf(in).length() <= 11){ cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(d); cell.setCellStyle(style); }else{ cell.setCellType(HSSFCell.CELL_TYPE_STRING); HSSFRichTextString hssfString = new HSSFRichTextString(String.valueOf(in)); cell.setCellValue(hssfString); cell.setCellStyle(style); } }catch (Exception e) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); HSSFRichTextString hssfString = new HSSFRichTextString(""); cell.setCellValue(hssfString); cell.setCellStyle(style); LOGGER.error("excel解析:", e); } } } }} 0 0
- 使用POI操作Excel，读取、写入Excel
- POI操作Excel，读取，写入
- poi 读取写入excel
- POI读取和写入Excel
- java8 POI 读取写入EXCEL
- POI excel的读取，写入
- poi读取数据写入excel
- Java使用POI读取Excel操作
- 使用POI操作Excel
- 使用POI操作EXCEl
- 使用poi操作excel
- 使用POI操作Excel
- 使用poi操作excel
- 使用poi操作excel
- Apache POI 读取、写入Excel文件教程
- poi 中读取和写入excel 方法
- POI处理EXCEL文件（读取，写入）
- java操作excel之poi读取excel
- 后台服务(Service)简述
- Python 安装使用模块
- Qt 中一些常用类的中文说明
- 屏蔽windows的全半角切换快捷键
- Linux Input子系统以及A/B（SLOT）协议
- 使用POI操作Excel，读取、写入Excel
- 【java】itoo项目实战之优化后具体代码
- Activity的封装以及美化
- 备战2015GRE之核心词组under the banner of
- Android中Application类用法
- SAT阅读填空题常考单词整理(1)
- 锘致echo 不是内部或外部命令，也不是可运行的程序 或批处理文件。
- 高精度(还有其它的以后再补充)
- 内存池
