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 ;
|
}
|
}
|
