New file |
| | |
| | | package com.dy.common.util; |
| | | |
| | | import org.apache.poi.hssf.usermodel.*; |
| | | import org.apache.poi.hssf.util.HSSFColor; |
| | | import org.apache.poi.ss.usermodel.RichTextString; |
| | | import org.apache.poi.ss.util.CellRangeAddress; |
| | | import org.apache.poi.ss.util.RegionUtil; |
| | | |
| | | import java.io.File; |
| | | import java.io.FileNotFoundException; |
| | | import java.io.FileOutputStream; |
| | | import java.io.IOException; |
| | | |
| | | public abstract class HSSF { |
| | | |
| | | public HSSFCellStyle createTitleStyle_1(HSSFWorkbook workbook, boolean hasForegroundColor, int fontSize){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | if(hasForegroundColor) { |
| | | style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// The style settings |
| | | }else{ |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | } |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_NONE); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_NONE); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_NONE); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_NONE); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_CENTER); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontName("宋体"); |
| | | font.setFontHeightInPoints((short) fontSize); |
| | | font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); |
| | | font.setColor(HSSFColor.BLACK.index); |
| | | style.setFont(font); |
| | | return style ; |
| | | } |
| | | public HSSFCellStyle createTitleStyle_2(HSSFWorkbook workbook, boolean hasForegroundColor, int fontSize){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | if(hasForegroundColor) { |
| | | style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// The style settings |
| | | }else{ |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | } |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_NONE); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_NONE); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_NONE); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_NONE); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_LEFT); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontName("宋体"); |
| | | font.setFontHeightInPoints((short) fontSize); |
| | | //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); |
| | | font.setColor(HSSFColor.BLACK.index); |
| | | style.setFont(font); |
| | | return style ; |
| | | } |
| | | public HSSFCellStyle createTitleStyle_3(HSSFWorkbook workbook, boolean hasForegroundColor, int fontSize){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | if(hasForegroundColor) { |
| | | style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// The style settings |
| | | }else{ |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | } |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_CENTER); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontName("宋体"); |
| | | font.setFontHeightInPoints((short) fontSize); |
| | | //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); |
| | | font.setColor(HSSFColor.BLACK.index); |
| | | style.setFont(font); |
| | | return style ; |
| | | } |
| | | |
| | | |
| | | public HSSFCellStyle createTitleStyle(HSSFWorkbook workbook, boolean hasForegroundColor){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | if(hasForegroundColor) { |
| | | style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// The style settings |
| | | }else{ |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | } |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_CENTER); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontName("宋体"); |
| | | font.setFontHeightInPoints((short) 11); |
| | | font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); |
| | | font.setColor(HSSFColor.GREY_80_PERCENT.index); |
| | | style.setFont(font); |
| | | return style ; |
| | | } |
| | | |
| | | public HSSFCellStyle createContentCenterStyle(HSSFWorkbook workbook){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_CENTER); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontHeightInPoints((short) 10); |
| | | font.setFontName("宋体"); |
| | | font.setColor(HSSFColor.GREY_80_PERCENT.index); |
| | | |
| | | style.setFont(font); |
| | | |
| | | return style ; |
| | | } |
| | | |
| | | public HSSFCellStyle createContentCenterStyleBFH(HSSFWorkbook workbook){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_CENTER); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontHeightInPoints((short) 10); |
| | | font.setFontName("宋体"); |
| | | font.setColor(HSSFColor.GREY_80_PERCENT.index); |
| | | |
| | | style.setFont(font); |
| | | |
| | | style.setDataFormat(workbook.createDataFormat().getFormat("0.00%")); |
| | | |
| | | return style ; |
| | | } |
| | | public HSSFCellStyle createContentCenterStyle_1(HSSFWorkbook workbook, int fontSize){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_CENTER); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontName("宋体"); |
| | | font.setFontHeightInPoints((short) fontSize); |
| | | font.setColor(HSSFColor.GREY_80_PERCENT.index); |
| | | style.setFont(font); |
| | | |
| | | return style ; |
| | | } |
| | | |
| | | |
| | | public HSSFCellStyle createContentLeftStyle(HSSFWorkbook workbook){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_LEFT); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontHeightInPoints((short) 10); |
| | | font.setFontName("宋体"); |
| | | font.setColor(HSSFColor.GREY_80_PERCENT.index); |
| | | |
| | | style.setFont(font); |
| | | return style ; |
| | | } |
| | | |
| | | |
| | | public HSSFCellStyle createContentLeftStyle_1(HSSFWorkbook workbook, int fontSize){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_LEFT); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontName("宋体"); |
| | | font.setFontHeightInPoints((short) fontSize); |
| | | font.setColor(HSSFColor.GREY_80_PERCENT.index); |
| | | |
| | | style.setFont(font); |
| | | return style ; |
| | | } |
| | | |
| | | |
| | | public HSSFCellStyle createContentRightStyle(HSSFWorkbook workbook){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontHeightInPoints((short) 10); |
| | | font.setFontName("宋体"); |
| | | font.setColor(HSSFColor.GREY_80_PERCENT.index); |
| | | |
| | | style.setFont(font); |
| | | return style ; |
| | | } |
| | | |
| | | public HSSFCellStyle createContentRightStyle_1(HSSFWorkbook workbook, int fontSize){ |
| | | HSSFCellStyle style = workbook.createCellStyle();// Create a style |
| | | style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings |
| | | style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); |
| | | style.setBorderBottom(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderLeft(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderRight(HSSFCellStyle.BORDER_THIN); |
| | | style.setBorderTop(HSSFCellStyle.BORDER_THIN); |
| | | style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); |
| | | style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); |
| | | style.setWrapText(true);// 自动换行 |
| | | |
| | | HSSFFont font = workbook.createFont();// Create a font |
| | | font.setFontName("宋体"); |
| | | font.setFontHeightInPoints((short) fontSize); |
| | | font.setColor(HSSFColor.GREY_80_PERCENT.index); |
| | | |
| | | style.setFont(font); |
| | | return style ; |
| | | } |
| | | |
| | | public HSSFCell createTitleCell(HSSFSheet sheet, HSSFRow row, String name, int index, Integer width, HSSFCellStyle style){ |
| | | HSSFCell cell = row.createCell(index);//Create first column cell |
| | | if(style != null){ |
| | | cell.setCellStyle(style); |
| | | } |
| | | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
| | | cell.setCellValue(new HSSFRichTextString(name)); |
| | | if(width != null){ |
| | | sheet.setColumnWidth(index, width * 1000); |
| | | } |
| | | return cell ; |
| | | } |
| | | public HSSFCell createTitleCell_1(HSSFSheet sheet, HSSFRow row, String name, int index, Double width, HSSFCellStyle style){ |
| | | HSSFCell cell = row.createCell(index);//Create first column cell |
| | | if(style != null){ |
| | | cell.setCellStyle(style); |
| | | } |
| | | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
| | | cell.setCellValue(new HSSFRichTextString(name)); |
| | | if(width != null){ |
| | | sheet.setColumnWidth(index, (int)(width * 1000)); |
| | | } |
| | | return cell ; |
| | | } |
| | | |
| | | public void setMergedRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle style) { |
| | | for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) { |
| | | HSSFRow row = sheet.getRow(i); |
| | | if (row == null) { |
| | | row = sheet.createRow(i); |
| | | } |
| | | for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) { |
| | | HSSFCell cell = row.getCell(j); |
| | | if (cell == null) { |
| | | cell = row.createCell(j); |
| | | cell.setCellValue(""); |
| | | } |
| | | cell.setCellStyle(style); |
| | | } |
| | | } |
| | | } |
| | | public void setRegionBorder(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb){ |
| | | RegionUtil.setBorderBottom(border,region, sheet, wb); |
| | | RegionUtil.setBorderLeft(border,region, sheet, wb); |
| | | RegionUtil.setBorderRight(border,region, sheet, wb); |
| | | RegionUtil.setBorderTop(border,region, sheet, wb); |
| | | } |
| | | |
| | | public HSSFCell createDataDouCell(HSSFRow row, int index, Double value, HSSFCellStyle style){ |
| | | HSSFCell cell = row.createCell(index); |
| | | cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); |
| | | cell.setCellStyle(style); |
| | | if(value != null){ |
| | | cell.setCellValue(value); |
| | | } |
| | | return cell ; |
| | | } |
| | | |
| | | public HSSFCell createDataNumCell(HSSFRow row, int index, Integer value, HSSFCellStyle style){ |
| | | HSSFCell cell = row.createCell(index); |
| | | cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); |
| | | cell.setCellStyle(style); |
| | | if(value != null){ |
| | | cell.setCellValue(value); |
| | | } |
| | | return cell ; |
| | | } |
| | | |
| | | public HSSFCell createDataStrCell(HSSFRow row, int index, String value, HSSFCellStyle style){ |
| | | HSSFCell cell = row.createCell(index); |
| | | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
| | | cell.setCellStyle(style); |
| | | if(value != null){ |
| | | cell.setCellValue(value); |
| | | } |
| | | return cell ; |
| | | } |
| | | |
| | | |
| | | public HSSFCell createDataStrCell(HSSFRow row, int index, RichTextString value, HSSFCellStyle style){ |
| | | HSSFCell cell = row.createCell(index); |
| | | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
| | | cell.setCellStyle(style); |
| | | if(value != null){ |
| | | cell.setCellValue(value); |
| | | } |
| | | return cell ; |
| | | } |
| | | |
| | | |
| | | public HSSFCell[] createTitleCells(int startColIndex, Object[][] tcs, HSSFSheet sheet, HSSFRow row, HSSFCellStyle style){ |
| | | HSSFCell[] cells = new HSSFCell[tcs.length] ; |
| | | for(int i = 0; i < tcs.length; i++){ |
| | | cells[i] = createTitleCell(sheet, row, (String)tcs[i][0], startColIndex+ i, (Integer)tcs[i][1], style); |
| | | } |
| | | return cells ; |
| | | } |
| | | public HSSFCell[] createTitleCells_1(int startColIndex, Object[][] tcs, HSSFSheet sheet, HSSFRow row, HSSFCellStyle style){ |
| | | HSSFCell[] cells = new HSSFCell[tcs.length] ; |
| | | for(int i = 0; i < tcs.length; i++){ |
| | | cells[i] = createTitleCell_1(sheet, row, (String)tcs[i][0], startColIndex+ i, (Double)tcs[i][1], style); |
| | | } |
| | | return cells ; |
| | | } |
| | | |
| | | public void createContentCells(Object[][] ccs, HSSFRow row){ |
| | | for(int i = 0; i < ccs.length; i++){ |
| | | createDataStrCell(row, i, (String)ccs[i][0], (HSSFCellStyle)ccs[i][1]) ; |
| | | } |
| | | } |
| | | |
| | | |
| | | |
| | | /** |
| | | * 输入EXCEL文件 |
| | | * @param workbook |
| | | * @param fileName 文件名 |
| | | */ |
| | | public boolean outputExcel(HSSFWorkbook workbook, String fileName) { |
| | | boolean flag = true ; |
| | | FileOutputStream fos = null; |
| | | try { |
| | | fos = new FileOutputStream(new File(fileName)); |
| | | workbook.write(fos); |
| | | fos.close(); |
| | | } catch (FileNotFoundException e) { |
| | | e.printStackTrace(); |
| | | flag = false ; |
| | | } catch (IOException e) { |
| | | e.printStackTrace(); |
| | | flag = false ; |
| | | } |
| | | return flag ; |
| | | } |
| | | } |