POI读写excel实例和JXL实现excel的方式 （2）

excel创建excel，对齐进行赋值，和单元格样式添加

通过了JXL方式生成excel  和POI方式生成excel

（功能：解析一个目录下的.rc文件进行行解析处理）

package parsing.MFC;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFColor;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import jxl.Workbook;import jxl.write.Label;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;public class ParsingRCFile {/*存储.rc文件的路径*/private ArrayList<String> rcFilePathList = new ArrayList<>();/*存储.rc文件中画面的id对应的对象*/private ArrayList<PageInfoBean> pageList = new ArrayList<>();/*存储入力路径*/private String inFilePath = "";/** * 解析工程 * 根据给定的目录结构，得出每个子项目，从中找取资源配置文件（即.rc后缀的文件）存在list中 */public void parsingProject(String inPath) {File file = new File(inPath);File[] subFile = file.listFiles();for(File s : subFile){if (s.getName().toLowerCase().equals("allmake")|| s.getName().toLowerCase().equals("bin")|| s.getName().toLowerCase().equals("include")|| s.getName().toLowerCase().equals("lib")) {continue;}if(s.getName().toLowerCase().endsWith(".rc")){rcFilePathList.add(s.getPath());}if(s.isDirectory()){parsingProject(s.getAbsolutePath());}}inFilePath = inPath;}/** * 解析给定的.rc文件，取得ID */public void getRCFilePageID(){UArrayLst txtList = new UArrayLst();boolean flag = false;boolean toRepeat = true;String mat = "";try {for(String filePath : rcFilePathList) {ArrayList<String> tempS = new ArrayList<>();txtList.readfile(filePath, "");for(String txt : txtList) {//获取dialog的idif(txt.matches(".+\\sDIALOG.+")){mat = " DIALOG";flag = true;}else if(txt.matches(".+\\sDIALOGEX.+")){mat = " DIALOGEX";flag = true;}if(flag){//验重复PageInfoBean pageInfoBean = new PageInfoBean();int end = txt.indexOf(mat);pageInfoBean.pageId = txt.substring(0, end).trim();int last = filePath.lastIndexOf("\\");pageInfoBean.pageFileName = filePath.substring(last+1);pageInfoBean.pageFolderName = filePath.substring(inFilePath.length(), last);pageInfoBean.pageClassName = "";if(!tempS.contains(pageInfoBean.pageId)){toRepeat = true;//表示该项不是重复tempS.add(pageInfoBean.pageId);}if(toRepeat) {pageList.add(pageInfoBean);toRepeat = false;//用于去重复}flag = false;}}txtList.clear();}} catch (IOException e) {e.printStackTrace();}}/** * 输出解析结果 JXL输出excel */public void outParsingFileToJXL(String outPath){try {if(outPath.isEmpty()){return ;}File file = new File(outPath);if (!file.exists()) {file.mkdirs();}WritableWorkbook wwb = Workbook.createWorkbook(new File(outPath + "\\aa.xls"));writeExcel(wwb);wwb.write();   wwb.close(); } catch (IOException | WriteException e) {e.printStackTrace();}}/** * JXL方式的excel赋值 * @param wwb */private void writeExcel(WritableWorkbook wwb) {try {//创建Excel工作表 指定名称和位置   int sheetNum = 0;WritableSheet sheet = wwb.createSheet("aa" + (sheetNum + 1),sheetNum);int colcount = 0;int rowcount = 1;//列名Label l = new Label(colcount, 0, "ID");Label l2 = new Label(colcount + 1, 0, "EXE名");Label l3 = new Label(colcount + 2, 0, "文件夹");Label l4 = new Label(colcount + 3, 0, "文件名");Label l5 = new Label(colcount + 4, 0, "类名");sheet.addCell(l);sheet.addCell(l2);sheet.addCell(l3);sheet.addCell(l4);sheet.addCell(l5);//赋值for(PageInfoBean pageInfoBean : pageList){Label label = new Label(colcount, rowcount, pageInfoBean.pageId);Label label2 = new Label(colcount + 1, rowcount, "");Label label3 = new Label(colcount + 2, rowcount, pageInfoBean.pageFolderName);Label label4 = new Label(colcount + 3, rowcount, pageInfoBean.pageFileName);Label label5 = new Label(colcount + 4, rowcount, pageInfoBean.pageClassName);sheet.addCell(label);sheet.addCell(label2);sheet.addCell(label3);sheet.addCell(label4);sheet.addCell(label5);rowcount = rowcount + 1;if(rowcount > 59999) {sheetNum = sheetNum + 1;sheet = wwb.createSheet("aa" + (sheetNum + 1),sheetNum);colcount = 0;rowcount = 1;//列名Label la = new Label(colcount, 0, "ID");Label la2 = new Label(colcount + 1, 0, "EXE名");Label la3 = new Label(colcount + 2, 0, "文件夹");Label la4 = new Label(colcount + 3, 0, "文件名");Label la5 = new Label(colcount + 4, 0, "类名");sheet.addCell(la);sheet.addCell(la2);sheet.addCell(la3);sheet.addCell(la4);sheet.addCell(la5);}}} catch (RowsExceededException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}}/** * 输出解析结果 POI输出excel */public void outParsingFileToPOI(String outPath){try {if(outPath.isEmpty()){System.out.println("输出路径不正确...");return ;}File file = new File(outPath);if (!file.exists()) {file.mkdirs();}XSSFWorkbook wb = new XSSFWorkbook();writeExcelToPoi(wb);FileOutputStream fileOut = new FileOutputStream(outPath + "\\aa.xlsx");    wb.write(fileOut);    fileOut.close();} catch (IOException e) {e.printStackTrace();}}/** * POI 方式的excel赋值 * @param wwb */private void writeExcelToPoi(XSSFWorkbook wb) {//创建Excel工作表 指定名称和位置   int sheetNum = 0;Sheet sheet = wb.createSheet("aa" + (sheetNum + 1));int rowcount = 1;//表头设置Row row = sheet.createRow(0);setHreadCell(wb,row);//赋值for(PageInfoBean pageInfoBean : pageList){row = sheet.createRow(rowcount);//表体设置setBodyCell(wb, row, pageInfoBean);rowcount = rowcount + 1;if(rowcount > 59999) {sheetNum = sheetNum + 1;sheet = wb.createSheet("aa" + (sheetNum + 1));rowcount = 1;//表头设置row = sheet.createRow(0);setHreadCell(wb,row);}}//设置自动宽度  一定要放在sheet最终位置sheet.autoSizeColumn((short)0);sheet.autoSizeColumn((short)1);sheet.autoSizeColumn((short)2);sheet.autoSizeColumn((short)3);sheet.autoSizeColumn((short)4);}/** * 获取存储.rc文件的路径list * @return */public ArrayList<String> getPathList(){return rcFilePathList;}/** * 表头单元格设定 */private void setHreadCell(XSSFWorkbook wb,Row row){XSSFCellStyle style = getStyle1(wb);Cell cell = row.createCell(0);cell.setCellValue("ID");cell.setCellStyle(style);cell = row.createCell(1);cell.setCellValue("EXE名");cell.setCellStyle(style);cell = row.createCell(2);cell.setCellValue("文件夹");cell.setCellStyle(style);cell = row.createCell(3);cell.setCellValue("文件名");cell.setCellStyle(style);cell = row.createCell(4);cell.setCellValue("类名");cell.setCellStyle(style);}/** * 表头单元格设定 */private void setBodyCell(XSSFWorkbook wb,Row row,PageInfoBean pageInfoBean){XSSFCellStyle style = getStyle2(wb);Cell cell = row.createCell(0);cell.setCellValue(pageInfoBean.pageId);cell.setCellStyle(style);cell = row.createCell(1);cell.setCellValue("");cell.setCellStyle(style);cell = row.createCell(2);cell.setCellValue(pageInfoBean.pageFolderName);cell.setCellStyle(style);cell = row.createCell(3);cell.setCellValue(pageInfoBean.pageFileName);cell.setCellStyle(style);cell = row.createCell(4);cell.setCellValue(pageInfoBean.pageClassName);cell.setCellStyle(style);}/** * 表头单元格背景色加边框的样式 */private XSSFCellStyle getStyle1(XSSFWorkbook wb){XSSFCellStyle style = wb.createCellStyle();style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);style.setBorderLeft(CellStyle.BORDER_THIN);style.setBorderRight(CellStyle.BORDER_THIN);style.setBorderTop(CellStyle.BORDER_THIN);style.setBorderBottom(CellStyle.BORDER_THIN);style.setBottomBorderColor(new XSSFColor(java.awt.Color.BLACK));   style.setTopBorderColor(new XSSFColor(java.awt.Color.BLACK));   style.setLeftBorderColor(new XSSFColor(java.awt.Color.BLACK)); style.setRightBorderColor(new XSSFColor(java.awt.Color.BLACK));return style;}/** * 表体单元格加边框的样式 */private XSSFCellStyle getStyle2(XSSFWorkbook wb){XSSFCellStyle style = wb.createCellStyle();style.setBorderLeft(CellStyle.BORDER_THIN);style.setBorderRight(CellStyle.BORDER_THIN);style.setBorderTop(CellStyle.BORDER_THIN);style.setBorderBottom(CellStyle.BORDER_THIN);style.setBottomBorderColor(new XSSFColor(java.awt.Color.BLACK));   style.setTopBorderColor(new XSSFColor(java.awt.Color.BLACK));   style.setLeftBorderColor(new XSSFColor(java.awt.Color.BLACK)); style.setRightBorderColor(new XSSFColor(java.awt.Color.BLACK));return style;}} 

主函数调用package cn.com.ydd.page.parsing;import java.util.Date;public class TestMain {public static void main(String[] args){String inpath = "E:\\1";String outPath = "E:\\2";System.out.println("抽出开始...."+new Date());ParsingRCFile prcf = new ParsingRCFile();prcf.parsingProject(inpath);System.out.println("抽出第一步完成...."+new Date());prcf.getRCFilePageID();System.out.println("抽出第二步完成...."+new Date());prcf.outParsingFileToPOI(outPath);System.out.println("抽出结束...."+new Date());}}