public static XSSFCellStyle getNewStyle( XSSFWorkbook workBook, CellStyle cellStyle, XSSFFont font) { XSSFCellStyle style = workBook.createCellStyle(); // 对齐方式 style.setAlignment(cellStyle.getAlignment()); style.setVerticalAlignment(cellStyle.getVAlignment()); // 设置背景颜色 // 最好的设置Pattern style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 单元格背景的显示模式 style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式. // style.setFillBackgroundColor(arg0); // 设置边框 style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框 style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框 style.setBorderTop(cellStyle.getBorderTop()); // 上边框 style.setBorderRight(cellStyle.getBorderRight()); // 右边框 // 设置边框颜色 style.setBottomBorderColor(cellStyle.getBottomBorderColor()); style.setTopBorderColor(cellStyle.getTopBorderColor()); style.setLeftBorderColor(cellStyle.getLeftBorderColor()); style.setRightBorderColor(cellStyle.getRightBorderColor()); // 设置自动换行 style.setWrapText(cellStyle.getWrapText()); style.setHidden(cellStyle.getHidden()); // 数据格式 style.setDataFormat(cellStyle.getDataFormate()); style.setLocked(cellStyle.getLocked()); // 文本旋转 请注意,这里的Rotation取值是从-90到90,而不是0-180度 style.setRotation(cellStyle.getRotation()); // 文本缩进 style.setIndention(cellStyle.getIndention()); // 设置字体 style.setFont(font); return style; }
public static boolean writeMultArray2XLSXFile( String fileName, List<List<String[]>> arrys, List<String> sheetLabels, boolean hasHead, int indexKey) throws Exception { if (arrys.isEmpty()) { System.err.println("No input data!"); return false; } XSSFWorkbook wb = new XSSFWorkbook(); XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); String lastKey = null; int switcher = -1; XSSFCell cell = null; String[] titleNames = null; int d = 0; for (List<String[]> arry : arrys) { XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d)); if (hasHead) { titleNames = (String[]) arry.get(0); } int columnNum = ((String[]) arry.get(0)).length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20))); } int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } rowIndex++; } int rowNum = arry.size(); for (int i = rowIndex; i < rowNum; i++) { row = sheet1.createRow((i)); String[] line = (String[]) arry.get(i); columnNum = line.length; if (indexKey >= 0) { if (lastKey == null && line[indexKey] != null) { lastKey = line[indexKey]; switcher *= -1; } else if (lastKey != null && line[indexKey] == null) { lastKey = line[indexKey]; switcher *= -1; } else if (lastKey == null && line[indexKey] == null) { } else { if (!lastKey.equals(line[indexKey])) { switcher *= -1; lastKey = line[indexKey]; } } } else { switcher = 1; } for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (switcher > 0) { cell.setCellStyle(bodyStyle); } else { cell.setCellStyle(markedBodyStyle); } if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue("."); } } } d++; } // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); return true; }
public static boolean writeArray2XLSXSheet( XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception { int rowNum = arry.size(); if (rowNum == 0) { System.err.println("No input data!"); return false; } String[] titleNames = null; if (hasHead) { titleNames = (String[]) arry.get(0); } int columnNum = ((String[]) arry.get(0)).length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20))); } XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); XSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); XSSFCell cell = null; if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell((short) i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } rowIndex++; } for (int i = rowIndex; i < rowNum; i++) { row = sheet1.createRow((i)); String[] line = (String[]) arry.get(i); columnNum = line.length; for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (line[0] != null) { cell.setCellStyle(markedBodyStyle); } else { cell.setCellStyle(bodyStyle); } if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue(""); } } } return true; }
public static boolean convertTextFile2XLSXFile( String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception { BufferedReader br = LocalFileFunc.getBufferedReader(inFileName); String line = br.readLine(); if (line == null) { return false; } String[] cells1 = Util.tokenize(line, '\t'); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("Data"); int columnNum = cells1.length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20))); } XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); String lastKey = null; int switcher = -1; XSSFCell cell = null; if (hasHead) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(cells1[i]); cell.setCellStyle(headStyle); } } else { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(cells1[i]); cell.setCellStyle(bodyStyle); } } rowIndex++; while ((line = br.readLine()) != null) { cells1 = Util.tokenize(line, '\t'); row = sheet1.createRow((rowIndex)); columnNum = cells1.length; if (indexKey >= 0) { if (lastKey == null && cells1[indexKey] != null) { lastKey = cells1[indexKey]; switcher *= -1; } else if (lastKey != null && cells1[indexKey] == null) { lastKey = cells1[indexKey]; switcher *= -1; } else if (lastKey == null && cells1[indexKey] == null) { } else { if (!lastKey.equals(cells1[indexKey])) { switcher *= -1; lastKey = cells1[indexKey]; } } } else { switcher = 1; } // System.out.println(cells1[0]); for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (switcher > 0) { cell.setCellStyle(bodyStyle); } else { cell.setCellStyle(markedBodyStyle); } if (cells1[j] != null) { if (Util.isNumeric(cells1[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(cells1[j])); } else { cell.setCellValue(cells1[j]); } } else { cell.setCellValue("."); } } rowIndex++; } br.close(); // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(outFileName); wb.write(fileOut); fileOut.close(); return true; }
public static boolean writeArray2XLSXFile( String FileName, String[] titleNames, List<String[]> arry) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet 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))); // } XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom headFont to the text in the comment XSSFFont headFont = wb.createFont(); headFont.setFontName("Courier New"); headFont.setFontHeightInPoints((short) 10); headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); headFont.setColor(HSSFColor.BLACK.index); headStyle.setAlignment(XSSFCellStyle.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); XSSFCellStyle contentStyle = wb.createCellStyle(); // apply custom headFont to the text in the comment XSSFFont contentFont = wb.createFont(); contentFont.setFontName("Courier New"); contentFont.setFontHeightInPoints((short) 9); // headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); contentFont.setColor(HSSFColor.BLACK.index); contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); contentStyle.setFillForegroundColor(HSSFColor.BLACK.index); contentStyle.setFont(contentFont); // create titile row XSSFRow row = sheet1.createRow(0); int heandLine = 0; XSSFCell 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.?XSSF.?usermodel.?XSSFCell.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; }