导出Excel工具类

package tools;import java.util.List;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.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Sheet;public class ExportExcelTool {/** * 创建一个工作薄对象 * @return */public static HSSFWorkbook getHSSFWorkbookInstance(){return new HSSFWorkbook(); // 创建工作簿对象}/** * 创建一个工作表对象，设置表头样式、内容 * @param title * @param rowName * @return  */public static HSSFWorkbook setSheet(HSSFWorkbook wb, String title, String[] rowName) {Sheet sheet = (Sheet) wb.createSheet(title); // 创建工作表// sheet样式定义 ，均为自定义方法 - 可扩展HSSFCellStyle columnTopStyle = getColumnTopStyle(wb); // 获取列头样式对象// 产生表格标题行，并合并两行HSSFRow rowm0 = (HSSFRow) sheet.createRow(0);HSSFCell cellTiltle = rowm0.createCell((short) 0);// 合并单元格，使标题占整个第一行；设置样式；设置单元格内容sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));cellTiltle.setCellStyle(columnTopStyle);cellTiltle.setCellValue(title);// 定义所需列数int columnNum = rowName.length;HSSFRow rowRowName = (HSSFRow) sheet.createRow(2); // 在索引2的位置创建行// 将列头设置到sheet的单元格中for (int n = 0; n < columnNum; n++) {HSSFCell cellRowName = rowRowName.createCell((short) n); // 创建列头对应个数的单元格cellRowName.setCellValue(rowName[n]); // 设置列头单元格的值cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式}return wb;}/** * 在工作薄中放入Excel表格数据 * @param wb * @param dataList * @param title * @return */public static HSSFWorkbook putDataList(HSSFWorkbook wb, List<List<String>> dataList, String title){HSSFCellStyle style = getStyle(wb); // 单元格样式对象// 将查询出的数据设置到sheet对应的单元格中HSSFSheet sheet = wb.getSheet(title);for(int i = 0; i < dataList.size(); i++){HSSFRow row = (HSSFRow) sheet.createRow(i + 3);// 创建所需的行数,第几行short j = 0;List<String> list = dataList.get(i);for (String s : list) {HSSFCell cell = row.createCell(j++, HSSFCell.CELL_TYPE_STRING);; // 设置单元格的数据类型cell.setCellValue(ExportExcelTool.safeString(s));cell.setCellStyle(style); // 设置单元格样式}}return wb;}/** * 设置每个单元格的宽度，让列宽随着导出的列长自动适应 * @param workbook * @param title * @param rowName * @return */public static HSSFWorkbook setColumnLength(HSSFWorkbook workbook,String title, String[] rowName) {int columnNum = rowName.length;HSSFSheet sheet = workbook.getSheet(title);for (int colNum = 0; colNum < columnNum; colNum++) {int columnWidth = sheet.getColumnWidth((short) colNum) / 256;for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {HSSFRow currentRow;// 当前行未被使用过if (sheet.getRow(rowNum) == null) {currentRow = (HSSFRow) sheet.createRow(rowNum);} else {currentRow = (HSSFRow) sheet.getRow(rowNum);}if (currentRow.getCell((short) colNum) != null) {HSSFCell currentCell = currentRow.getCell((short) colNum);if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {int length = currentCell.getStringCellValue().getBytes().length;if (columnWidth < length) {columnWidth = length;}}}}sheet.setColumnWidth((short) colNum,(short) ((columnWidth + 4) * 256));}return workbook;}public static String safeString(Object o) {return o == null ? "" : o.toString().trim();}/** * 列头单元格样式 */public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {// 设置字体HSSFFont font = workbook.createFont();// 设置字体大小font.setFontHeightInPoints((short) 11);// 字体加粗font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体名字font.setFontName("Courier New");// 设置样式;HSSFCellStyle style = workbook.createCellStyle();// 设置底边框;style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 设置底边框颜色;style.setBottomBorderColor(HSSFColor.BLACK.index);// 设置左边框;style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 设置左边框颜色;style.setLeftBorderColor(HSSFColor.BLACK.index);// 设置右边框;style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 设置右边框颜色;style.setRightBorderColor(HSSFColor.BLACK.index);// 设置顶边框;style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 设置顶边框颜色;style.setTopBorderColor(HSSFColor.BLACK.index);// 在样式用应用设置的字体;style.setFont(font);// 设置自动换行;style.setWrapText(false);// 设置水平对齐的样式为居中对齐;style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置垂直对齐的样式为居中对齐;style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);return style;}/** * 列数据信息单元格样式 */public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {// 设置字体HSSFFont font = workbook.createFont();// 设置字体大小// font.setFontHeightInPoints((short)10);// 字体加粗// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置字体名字font.setFontName("Courier New");// 设置样式;HSSFCellStyle style = workbook.createCellStyle();// 设置底边框;style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 设置底边框颜色;style.setBottomBorderColor(HSSFColor.BLACK.index);// 设置左边框;style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 设置左边框颜色;style.setLeftBorderColor(HSSFColor.BLACK.index);// 设置右边框;style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 设置右边框颜色;style.setRightBorderColor(HSSFColor.BLACK.index);// 设置顶边框;style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 设置顶边框颜色;style.setTopBorderColor(HSSFColor.BLACK.index);// 在样式用应用设置的字体;style.setFont(font);// 设置自动换行;style.setWrapText(false);// 设置水平对齐的样式为居中对齐;style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置垂直对齐的样式为居中对齐;style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);return style;}}

用的jar包是poi 3.8