private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) {
/*Font bold = workBook.createFont();
bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
bold.setFontHeightInPoints((short) 10);
bold.setColor(Font.COLOR_RED);*/
HSSFFont hssfFont = workBook.createFont();
hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
hssfFont.setColor(Font.COLOR_RED);
boldStyle = workBook.createCellStyle();
boldStyle.setBorderBottom(CellStyle.BORDER_THIN);
boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
boldStyle.setFont(hssfFont);
boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
defaultFont = workBook.createFont();
defaultFont.setFontHeightInPoints((short) 10);
defaultFont.setFontName("Arial");
defaultFont.setColor(IndexedColors.BLACK.getIndex());
defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
defaultFont.setItalic(true);
newStyle = workBook.createCellStyle();
// newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex());
// newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
newStyle.setAlignment(CellStyle.ALIGN_CENTER);
newStyle.setFont(defaultFont);
/* hssfCellStyle = workBook.createCellStyle();
hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
hssfCellStyle.setFont(bold);*/
}
public HSSFCellStyle createLastCellStyle(boolean white) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
if (white) {
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
} else {
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
}
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
public void applyTo(HSSFCellStyle style, HSSFWorkbook workbook) {
style.setAlignment(this.alignment);
style.setBorderBottom(this.borderBottom);
style.setBorderLeft(this.borderLeft);
style.setBorderRight(this.borderRight);
style.setBorderTop(this.borderTop);
style.setBottomBorderColor(this.bottomBorderColor);
style.setDataFormat(this.dataFormat);
style.setFillPattern(this.fillPattern);
style.setFillForegroundColor(this.fillForegroundColor);
style.setFillBackgroundColor(this.fillBackgroundColor);
style.setFont(workbook.getFontAt(this.fontIndex));
style.setHidden(this.hidden);
style.setIndention(this.indention);
style.setLeftBorderColor(this.leftBorderColor);
style.setLocked(this.locked);
style.setRightBorderColor(this.rightBorderColor);
style.setRotation(this.rotation);
style.setTopBorderColor(this.topBorderColor);
style.setVerticalAlignment(this.verticalAlignment);
style.setWrapText(this.wrapText);
}
public HSSFCellStyle createSubTitleCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.DARK_BLUE.index);
style.setFont(font);
return style;
}
/**
* Builds the column headers
*
* @param worksheet
* @param startRowIndex starting row offset
* @param startColIndex starting column offset
*/
public static void buildHeaders(
HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Production> datasource) {
// Create font style for the headers
Font font = worksheet.getWorkbook().createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// Create cell style for the headers
HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle();
headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headerCellStyle.setFillPattern(CellStyle.FINE_DOTS);
headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
headerCellStyle.setWrapText(true);
headerCellStyle.setFont(font);
headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
// Create the column headers
HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2);
rowHeader.setHeight((short) 500);
HSSFCell cell0 = rowHeader.createCell(startColIndex + 0);
cell0.setCellValue("Shift");
cell0.setCellStyle(headerCellStyle);
int dyn = 1;
List<Compensation> compensations = datasource.get(0).getCompensations();
for (Compensation compensation : compensations) {
HSSFCell cellc1 = rowHeader.createCell(startColIndex + 0 + dyn);
cellc1.setCellValue(compensation.getElectricmeter().getName());
cellc1.setCellStyle(headerCellStyle);
dyn++;
HSSFCell cellc2 = rowHeader.createCell(startColIndex + 0 + dyn);
cellc2.setCellValue(compensation.getElectricmeter().getName() + "\nkW·h");
cellc2.setCellStyle(headerCellStyle);
dyn++;
}
HSSFCell cell5 = rowHeader.createCell(startColIndex + 0 + dyn + 0);
cell5.setCellValue("580 İnd/Reak");
cell5.setCellStyle(headerCellStyle);
HSSFCell cell6 = rowHeader.createCell(startColIndex + 0 + dyn + 1);
cell6.setCellValue("880 Kap/Reak");
cell6.setCellStyle(headerCellStyle);
}
protected HSSFCellStyle createInternalCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
protected HSSFCellStyle createSummaryCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setTopBorderColor(HSSFColor.DARK_BLUE.index);
style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
HSSFFont font = workbook.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 15);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
/**
* Создает новый стиль в документе Excel который полностью копирует свойства некоторого исходного
* стиля этого же документа. Данный метод используется для того чтобы в последствии поменять в
* новом стиле один или несколько свойств не затрагивая при этом свойства исходного стиля.
*
* @param wb документ Excel.
* @param src стиль ячейки взятый в качестве шаблона.
* @return созданная копия исходного стиля в этом же документе.
*/
public static HSSFCellStyle copyStyle(final HSSFWorkbook wb, final HSSFCellStyle src) {
final HSSFCellStyle dst = wb.createCellStyle();
dst.setAlignment(src.getAlignment());
dst.setBorderBottom(src.getBorderBottom());
dst.setBorderLeft(src.getBorderLeft());
dst.setBorderRight(src.getBorderRight());
dst.setBorderTop(src.getBorderTop());
dst.setBottomBorderColor(src.getBottomBorderColor());
dst.setDataFormat(src.getDataFormat());
dst.setFillForegroundColor(src.getFillForegroundColor());
dst.setFillBackgroundColor(src.getFillBackgroundColor());
dst.setFillPattern(src.getFillPattern());
dst.setFont(src.getFont(wb));
dst.setHidden(src.getHidden());
dst.setIndention(src.getIndention());
dst.setLeftBorderColor(src.getLeftBorderColor());
dst.setLocked(src.getLocked());
dst.setRightBorderColor(src.getRightBorderColor());
dst.setRotation(src.getRotation());
dst.setTopBorderColor(src.getTopBorderColor());
dst.setVerticalAlignment(src.getVerticalAlignment());
dst.setWrapText(src.getWrapText());
return dst;
}
protected Map<Short, HSSFCellStyle> applyStyles(final Report report, final HSSFWorkbook wb) {
final StylePalette palette = report.getPalette();
final Map<Short, HSSFCellStyle> styles = new HashMap<Short, HSSFCellStyle>();
if (report.getTemplate() != null) {
for (final short styleIndex : palette.getStyles().keySet()) {
final HSSFCellStyle style = wb.getCellStyleAt(styleIndex);
if (style == null)
throw new RuntimeException(
"Inconsistent report template. Style not found: " + styleIndex);
styles.put(styleIndex, style);
}
return styles;
}
if (palette.getColors().size() > PaletteRecord.STANDARD_PALETTE_SIZE)
throw new RuntimeException("too many colors on report");
final HSSFPalette pal = wb.getCustomPalette();
for (final Color color : palette.getColors().values()) {
pal.setColorAtIndex(color.getId(), color.getRed(), color.getGreen(), color.getBlue());
}
final Map<Short, HSSFFont> fonts = new HashMap<Short, HSSFFont>();
final HSSFDataFormat formatter = wb.createDataFormat();
for (final Font font : palette.getFonts().values()) {
final HSSFFont f = POIUtils.ensureFontExists(wb, font);
fonts.put(font.getId(), f);
}
for (final CellStyle style : palette.getStyles().values()) {
final short bbc =
style.getBottomBorderColor() != null ? style.getBottomBorderColor().getId() : 0;
final short fbc =
style.getFillBackgroundColor() != null ? style.getFillBackgroundColor().getId() : 0;
final short ffc =
style.getFillForegroundColor() != null ? style.getFillForegroundColor().getId() : 0;
final short lbc = style.getLeftBorderColor() != null ? style.getLeftBorderColor().getId() : 0;
final short rbc =
style.getRightBorderColor() != null ? style.getRightBorderColor().getId() : 0;
final short tbc = style.getTopBorderColor() != null ? style.getTopBorderColor().getId() : 0;
final HSSFCellStyle s = wb.createCellStyle();
s.setAlignment(style.getAlignment());
s.setBorderBottom(style.getBorderBottom());
s.setBorderLeft(style.getBorderLeft());
s.setBorderRight(style.getBorderRight());
s.setBorderTop(style.getBorderTop());
s.setBottomBorderColor(bbc);
s.setDataFormat(formatter.getFormat(style.getDataFormat()));
s.setFillBackgroundColor(fbc);
s.setFillForegroundColor(ffc);
s.setFillPattern(style.getFillPattern());
s.setHidden(style.isHidden());
s.setIndention(style.getIndention());
s.setLeftBorderColor(lbc);
s.setLocked(style.isLocked());
s.setRightBorderColor(rbc);
s.setRotation(style.getRotation());
s.setTopBorderColor(tbc);
s.setVerticalAlignment(style.getVerticalAlignment());
s.setWrapText(style.isWrapText());
s.setFont(fonts.get(style.getFont().getId()));
styles.put(style.getId(), s);
}
return styles;
}
public void applyFillTo(HSSFCellStyle style) {
style.setFillPattern(this.fillPattern);
style.setFillForegroundColor(this.fillForegroundColor);
style.setFillBackgroundColor(this.fillBackgroundColor);
}
public static boolean writeArray2ExcelFile1(
String FileName, String[] titleNames, List<String[]> arry) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = titleNames.length;
int rowNum = arry.size();
// for (int i = 0; i < columnNum; i++) {
// sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
// }
HSSFCellStyle headStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
HSSFFont headFont = wb.createFont();
headFont.setFontName("Courier New");
headFont.setFontHeightInPoints((short) 10);
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
headStyle.setLocked(true);
headStyle.setFont(headFont);
headStyle.setBorderTop((short) 2);
headStyle.setBorderBottom((short) 1);
HSSFCellStyle contentStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
HSSFFont contentFont = wb.createFont();
contentFont.setFontName("Courier New");
contentFont.setFontHeightInPoints((short) 9);
// headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
contentFont.setColor(HSSFColor.BLACK.index);
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
contentStyle.setFont(contentFont);
// create titile row
HSSFRow row = sheet1.createRow(0);
int heandLine = 0;
HSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
heandLine++;
}
for (int i = 0; i < rowNum; i++) {
row = sheet1.createRow((i + heandLine));
String[] line = (String[]) arry.get(i);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?hssf.?usermodel.?HSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(FileName);
wb.write(fileOut);
fileOut.close();
return true;
}