最近項(xiàng)目開(kāi)發(fā)中對(duì)excel操作比較頻繁,并結(jié)合正則表達(dá)式進(jìn)行了一些處理,整理一下. 1.正則表達(dá)式常用方法
/** * 在第一個(gè)字符串中查找匹配字符串的個(gè)數(shù) * @param str * @param regexStr * @return */ public static int count(String str,String regexStr){ int count = 0; Pattern pt = Pattern.compile(regexStr); Matcher m = pt.matcher(str); int start = 0; while(m.find()){ count++; str = str.replaceFirst(regexStr, ""); } return count; } /** * 根據(jù)正則表達(dá)式分割str字符串成為一個(gè)一個(gè)的小的單元! * (實(shí)際使用:在一個(gè)類(lèi)似語(yǔ)法分析的模塊中發(fā)揮重要作用) * 例如:3+5*4 根據(jù)正則表達(dá)式+-\* 分割成數(shù)組 3,+,5,*,4 * @param str * @param regexStr * @return */ public static List splitByStr(String str,String regexStr){ List temp = new ArrayList(); Pattern pt = Pattern.compile(regexStr); Matcher m = pt.matcher(str); int start = 0; while(m.find()){ //去掉下面的字符串中為空串的情況! if(m.start()!=start) temp.add(str.substring(start, m.start())); temp.add(str.substring(m.start(),m.end())); start = m.end(); } temp.add(str.substring(start)); return temp; } /** * 檢查是否含有指定的正則表達(dá)式匹配的子串. * @param str 目標(biāo)字符串 * @param regex 正則表達(dá)式,如果正則表達(dá)式含有"^......$"就是查找整個(gè)字符串對(duì)象是否符合正則表達(dá)式. * @return */ public static boolean checkInclude(String str,String regex){ Pattern pattern = Pattern.compile(regex); Matcher matcher = null; matcher = pattern.matcher(str); return matcher.find(); } /** * 方法字符串中符合正則表達(dá)式的子串的集合. * @param str * @param regex * @return */ public static List getRightSubStr(String str, String regex) { List ans = new ArrayList(); Pattern pattern = Pattern.compile(regex); Matcher matcher = pattern.matcher(str); while (matcher.find()) { //注意要下面的goup()函數(shù)中可以含有數(shù)字,表示查找得到正則表達(dá)式中的goup匹配串. ans.add(matcher.group()); System.out.println("找到匹配的字符串 \"" + matcher.group() + "\" 開(kāi)始于 " + matcher.start() + " 結(jié)束于 " + matcher.end() + "."); } return ans; } 下面是java正則表達(dá)式經(jīng)常使用的一些方法和說(shuō)明:
(1)使用matches方法快速建設(shè)是否表示給定的輸入字符串:Pattern.matches("\\d","1")返回true (2)split(string)使用方法:Pattern.compile(":").split("one:two:three:four:five"); 返回:解析出“one two three four five”單詞 再比如使用數(shù)字作為一個(gè)分割字符串的方法:(注意下面的\\d不是正則表達(dá)式,而是前面加了一個(gè)轉(zhuǎn)義符號(hào)\) Pattern.compile("\\d").split("one9two4three7four1five");也返回相同的結(jié)果。。 (3)在String類(lèi)中有的幾個(gè)與Pattern類(lèi)似的方法: public boolean matches(String regex): public String[] split(String regex, int limit): public String[] split(String regex): public String replace(CharSequence target,CharSequence replacement): (4) Matcher 類(lèi)中其他一些有用的方法 索引方法 索引方法(index methods)提供了一些正好在輸入字符串中發(fā)現(xiàn)匹配的索引值: public int start():返回之前匹配的開(kāi)始索引。 public int start(int group):返回之前匹配操作中通過(guò)給定組所捕獲序列的開(kāi)始索引。 public int end(): 返回最后匹配字符后的偏移量。 public int end(int group): 返回之前匹配操作中通過(guò)給定組所捕獲序列的最后字符之后的偏移量。 研究方法 研究方法(study methods)回顧輸入的字符串,并且返回一個(gè)用于指示是否找到模式的布爾值。 public boolean lookingAt(): 嘗試從區(qū)域開(kāi)頭處開(kāi)始,輸入序列與該模式匹配。 public boolean find(): 嘗試地尋找輸入序列中,匹配模式的下一個(gè)子序列。 public boolean find(int start): 重置匹配器,然后從指定的索引處開(kāi)始,嘗試地尋找輸入序列中,匹配模式的下一個(gè)子序列。 public boolean matches(): 嘗試將整個(gè)區(qū)域與模式進(jìn)行匹配 替換方法 替換方法(replacement methods)用于在輸入的字符串中替換文本有用處的方法。 public Matcher appendReplacement(StringBuffer sb, String replacement):實(shí)現(xiàn)非結(jié)尾處的增加和替換操作。 public StringBuffer appendTail(StringBuffer sb):實(shí)現(xiàn)結(jié)尾處的增加和替換操作。 public String replaceAll(String replacement):使用給定的替換字符串來(lái)替換輸入序列中匹配模式的每一個(gè)子序列。 public String replaceFirst(String replacement):使用給定的替換字符串來(lái)替換輸入序列中匹配模式的第一個(gè)子序列。 public static String quoteReplacement(String s):返回指定字符串的字面值來(lái)替換字符串。這個(gè)方法會(huì)生成一個(gè)字符串,用作 Matcher 的 appendReplacement 方法中的字面值替換 s。所產(chǎn)生的字符串將與作為字面值序列的 s 中的字符序列匹配。斜線(\)和美元符號(hào)($)將不再有特殊意義了。 正則表達(dá)式基礎(chǔ):
2.使用jxl進(jìn)行exlce的基本操作 下面基礎(chǔ)代碼來(lái)自于網(wǎng)絡(luò):
import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.Date; import jxl.Cell; import jxl.CellType; import jxl.Sheet; import jxl.Workbook; import jxl.WorkbookSettings; import jxl.format.Alignment; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.format.Colour; import jxl.format.VerticalAlignment; import jxl.write.Formula; import jxl.write.Label; import jxl.write.NumberFormat; import jxl.write.WritableCellFeatures; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; public class JExcelUtils { /** * 生成Excel文件 * @param path 文件路徑 * @param sheetName 工作表名稱 * @param dataTitles 數(shù)據(jù)標(biāo)題 */ public void createExcelFile(String path,String sheetName,String[] dataTitles){ WritableWorkbook workbook; try{ OutputStream os=new FileOutputStream(path); workbook=Workbook.createWorkbook(os); WritableSheet sheet = workbook.createSheet(sheetName, 0); //添加第一個(gè)工作表 initialSheetSetting(sheet); Label label; for (int i=0; i<dataTitles.length; i++){ //Label(列號(hào),行號(hào),內(nèi)容,風(fēng)格) label = new Label(i, 0, dataTitles[i],getTitleCellFormat()); sheet.addCell(label); } //插入一行 insertRowData(sheet,1,new String[]{"200201001","張三","100","60","100","260"},getDataCellFormat(CellType.STRING_FORMULA)); //一個(gè)一個(gè)插入行 label = new Label(0, 2,"200201002",getDataCellFormat(CellType.STRING_FORMULA)); sheet.addCell(label); label = new Label(1, 2,"李四",getDataCellFormat(CellType.STRING_FORMULA)); sheet.addCell(label); insertOneCellData(sheet,2,2,70.5,getDataCellFormat(CellType.NUMBER)); insertOneCellData(sheet,3,2,90.523,getDataCellFormat(CellType.NUMBER)); insertOneCellData(sheet,4,2,60.5,getDataCellFormat(CellType.NUMBER)); insertFormula(sheet,5,2,"C3+D3+E3",getDataCellFormat(CellType.NUMBER_FORMULA)); //插入日期 mergeCellsAndInsertData(sheet, 0, 3, 5, 3, new Date(), getDataCellFormat(CellType.DATE)); workbook.write(); workbook.close(); }catch(Exception e){ e.printStackTrace(); } } /** * 初始化表格屬性 * @param sheet */ public void initialSheetSetting(WritableSheet sheet){ try{ //sheet.getSettings().setProtected(true); //設(shè)置xls的保護(hù),單元格為只讀的 sheet.getSettings().setDefaultColumnWidth(10); //設(shè)置列的默認(rèn)寬度 //sheet.setRowView(2,false);//行高自動(dòng)擴(kuò)展 //setRowView(int row, int height);--行高 //setColumnView(int col,int width); --列寬 sheet.setColumnView(0,20);//設(shè)置第一列寬度 }catch(Exception e){ e.printStackTrace(); } } /** * 插入公式 * @param sheet * @param col * @param row * @param formula * @param format */ public void insertFormula(WritableSheet sheet,Integer col,Integer row,String formula,WritableCellFormat format){ try{ Formula f = new Formula(col, row, formula, format); sheet.addCell(f); }catch(Exception e){ e.printStackTrace(); } } /** * 插入一行數(shù)據(jù) * @param sheet 工作表 * @param row 行號(hào) * @param content 內(nèi)容 * @param format 風(fēng)格 */ public void insertRowData(WritableSheet sheet,Integer row,String[] dataArr,WritableCellFormat format){ try{ Label label; for(int i=0;i<dataArr.length;i++){ label = new Label(i,row,dataArr[i],format); sheet.addCell(label); } }catch(Exception e){ e.printStackTrace(); } } /** * 插入單元格數(shù)據(jù) * @param sheet * @param col * @param row * @param data */ public void insertOneCellData(WritableSheet sheet,Integer col,Integer row,Object data,WritableCellFormat format){ try{ if(data instanceof Double){ jxl.write.Number labelNF = new jxl.write.Number(col,row,(Double)data,format); sheet.addCell(labelNF); }else if(data instanceof Boolean){ jxl.write.Boolean labelB = new jxl.write.Boolean(col,row,(Boolean)data,format); sheet.addCell(labelB); }else if(data instanceof Date){ jxl.write.DateTime labelDT = new jxl.write.DateTime(col,row,(Date)data,format); sheet.addCell(labelDT); setCellComments(labelDT, "這是個(gè)創(chuàng)建表的日期說(shuō)明!"); }else{ Label label = new Label(col,row,data.toString(),format); sheet.addCell(label); } }catch(Exception e){ e.printStackTrace(); } } /** * 合并單元格,并插入數(shù)據(jù) * @param sheet * @param col_start * @param row_start * @param col_end * @param row_end * @param data * @param format */ public void mergeCellsAndInsertData(WritableSheet sheet,Integer col_start,Integer row_start,Integer col_end,Integer row_end,Object data, WritableCellFormat format){ try{ sheet.mergeCells(col_start,row_start,col_end,row_end);// 左上角到右下角 insertOneCellData(sheet, col_start, row_start, data, format); }catch(Exception e){ e.printStackTrace(); } } /** * 給單元格加注釋 * @param label * @param comments */ public void setCellComments(Object label,String comments){ WritableCellFeatures cellFeatures = new WritableCellFeatures(); cellFeatures.setComment(comments); if(label instanceof jxl.write.Number){ jxl.write.Number num = (jxl.write.Number)label; num.setCellFeatures(cellFeatures); }else if(label instanceof jxl.write.Boolean){ jxl.write.Boolean bool = (jxl.write.Boolean)label; bool.setCellFeatures(cellFeatures); }else if(label instanceof jxl.write.DateTime){ jxl.write.DateTime dt = (jxl.write.DateTime)label; dt.setCellFeatures(cellFeatures); }else{ Label _label = (Label)label; _label.setCellFeatures(cellFeatures); } } /** * 讀取excel * @param inputFile * @param inputFileSheetIndex * @throws Exception */ public ArrayList<String> readDataFromExcel(File inputFile, int inputFileSheetIndex){ ArrayList<String> list = new ArrayList<String>(); Workbook book = null; Cell cell = null; WorkbookSettings setting = new WorkbookSettings(); java.util.Locale locale = new java.util.Locale("zh","CN"); setting.setLocale(locale); setting.setEncoding("ISO-8859-1"); try{ book = Workbook.getWorkbook(inputFile, setting); }catch(Exception e){ e.printStackTrace(); } Sheet sheet = book.getSheet(inputFileSheetIndex); for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {//行 for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {//列 cell = sheet.getCell(colIndex, rowIndex); //System.out.println(cell.getContents()); list.add(cell.getContents()); } } book.close(); return list; } /** * 得到數(shù)據(jù)表頭格式 * @return */ public WritableCellFormat getTitleCellFormat(){ WritableCellFormat wcf = null; try { //字體樣式 WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.NO_BOLD,false);//最后一個(gè)為是否italic wf.setColour(Colour.RED); wcf = new WritableCellFormat(wf); //對(duì)齊方式 wcf.setAlignment(Alignment.CENTRE); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); //邊框 wcf.setBorder(Border.ALL,BorderLineStyle.THIN); //背景色 wcf.setBackground(Colour.GREY_25_PERCENT); } catch (WriteException e) { e.printStackTrace(); } return wcf; } /** * 得到數(shù)據(jù)格式 * @return */ public WritableCellFormat getDataCellFormat(CellType type){ WritableCellFormat wcf = null; try { //字體樣式 if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//數(shù)字 NumberFormat nf = new NumberFormat("#.00"); wcf = new WritableCellFormat(nf); }else if(type == CellType.DATE || type == CellType.DATE_FORMULA){//日期 jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm:ss"); wcf = new jxl.write.WritableCellFormat(df); }else{ WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一個(gè)為是否italic wcf = new WritableCellFormat(wf); } //對(duì)齊方式 wcf.setAlignment(Alignment.CENTRE); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); //邊框 wcf.setBorder(Border.LEFT,BorderLineStyle.THIN); wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN); wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN); //背景色 wcf.setBackground(Colour.WHITE); wcf.setWrap(true);//自動(dòng)換行 } catch (WriteException e) { e.printStackTrace(); } return wcf; } /** * 打開(kāi)文件看看 * @param exePath * @param filePath */ public void openExcel(String exePath,String filePath){ Runtime r=Runtime.getRuntime(); String cmd[]={exePath,filePath}; try{ r.exec(cmd); }catch(Exception e){ e.printStackTrace(); } } public static void main(String[] args){ String[] titles = {"學(xué)號(hào)","姓名","語(yǔ)文","數(shù)學(xué)","英語(yǔ)","總分"}; JExcelUtils jxl = new JExcelUtils(); String filePath = "E:/test.xls"; jxl.createExcelFile(filePath," 成績(jī)單",titles); jxl.readDataFromExcel(new File(filePath),0); jxl.openExcel("C:/Program Files/Microsoft Office/OFFICE11/EXCEL.EXE",filePath); } } 3.下面含有幾個(gè)十分有用針對(duì)excel操作的的工具方法:
import java.io.File; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import jxl.Cell; import jxl.CellView; import jxl.Sheet; import jxl.SheetSettings; import jxl.Workbook; import jxl.format.Alignment; import jxl.write.Label; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; /** * jxl操作excel的工具類(lèi). * */ public class JxlTool { public static int count = 1; //存儲(chǔ)帶有級(jí)別信息的內(nèi)容到位置的映射關(guān)系. private static Map levelToLocation = new HashMap(); public static void readExcel(String fileName) { Workbook wb = null; try { wb = Workbook.getWorkbook(new File(fileName)); Sheet[] sheets = wb.getSheets(); for(int i=0;i<sheets.length;i++){ Sheet ii = sheets[i]; System.out.println("第"+i+"個(gè)sheet的名字是"+ii.getName()); } } catch (Exception e) { System.out.println("出現(xiàn)異常" + e); e.printStackTrace(); } finally { wb.close(); } } private static String allChar = "abcdefghijklmnopqrstuvwxyz"; /** * 從字符中得到列數(shù).例如K-->10,A-->0,AA-->27 * @return */ public static int getNumFromExcelStr(String code) { int result = 0; code = code.toLowerCase(); if(code.length()>1){ char[] c = code.toCharArray(); int len = c.length; for(int i=0;i<len;i++){ result+=allChar.indexOf(c[i])+1; if(i<len-1){ result+=26; } } result-=1; } else return allChar.indexOf(code); return result; } /** * 根據(jù)行號(hào)和列號(hào)得到所在的單元格.例如(3,4)-->"E4" * @param vNum 縱坐標(biāo) * @param hNum 橫坐標(biāo) * @return */ public static String getCellInfo(int hNum,int vNum){ char[] cs = allChar.toCharArray(); String hStr = ""; if(vNum>25){ hStr = String.valueOf(cs[vNum/26-1])+String.valueOf(cs[vNum%26-1]); }else{ hStr = String.valueOf(cs[vNum]); } return (hStr+Integer.toString((hNum+1))).toUpperCase(); } /** * 得到一個(gè)字符串里面的字符.A12-->A * @param oldStr * @return */ public static String getCodeFromStr(String oldStr){ return oldStr.replaceAll("\\d", ""); } /** * 得到一個(gè)字符串里面的字符.A12-->12 * @param oldStr * @return */ public static int getNumFromStr(String oldStr){ return Integer.parseInt(oldStr.replaceAll("[a-zA-Z]", ""))-1; } /** * 讀取指定excel中的指定sheet的某一塊的數(shù)據(jù)....用于模板里面讀取單元格. * @param fileName * @param sheetIndex * @param startRow * @param endRow * @param startColumn * @param endColumn */ public static List readExcel(String fileName, int sheetIndex, int startRow, int endRow, int startColumn, int endColumn) { Workbook wb = null; List allData = new ArrayList(); Cell cell = null; try { wb = Workbook.getWorkbook(new File(fileName)); Sheet sheet = wb.getSheet(sheetIndex); int rowCount = sheet.getRows(); int columnCount = sheet.getColumns(); for (int r = startRow; r < rowCount && r <= endRow; r++) {// 行 for (int c = startColumn; c < columnCount && c <= endColumn; c++) {// 列 cell = sheet.getCell(c, r); // System.out.println(cell.getContents()); allData.add(cell.getContents()); } } } catch (Exception e) { System.out.println("出現(xiàn)異常" + e); e.printStackTrace(); } finally { wb.close(); } return allData; } /** * 讀取指定excel中的指定sheet的某一塊的數(shù)據(jù)....用于模板里面讀取單元格. * @param fileName * @param sheetIndex * @param startCell * @param endCell * @return */ public static List readExcel(String fileName, int sheetIndex,String startCell, String endCell) { int startRow = getNumFromStr(startCell); int endRow = getNumFromStr(endCell); int startColumn=getNumFromExcelStr(getCodeFromStr(startCell)); int endColumn = getNumFromExcelStr(getCodeFromStr(endCell)); return readExcel(fileName, sheetIndex, startRow, endRow, startColumn, endColumn); } /** * 設(shè)置excel中的sheet頁(yè)全部隱藏 * @param fileName */ public static void setAllHiddenSheet(String fileName) { Workbook wb = null; try { wb = Workbook.getWorkbook(new File(fileName)); // 打開(kāi)一個(gè)文件副本,并指定數(shù)據(jù)寫(xiě)回原文件. WritableWorkbook book = Workbook.createWorkbook(new File(fileName), wb); Sheet[] sheets = book.getSheets(); for(int i=3;i<sheets.length;i++){ Sheet ii = sheets[i]; ii.getSettings().setHidden(true); } book.write(); book.close(); } catch (Exception e) { System.out.println("出現(xiàn)異常" + e); e.printStackTrace(); } finally { wb.close(); System.out.print(111); } } /** * 添加一個(gè)新的sheet到指定excel文件 * @param fileName * @param sheetName sheet的name */ public static void addNewSheet(String fileName,String sheetName) { Workbook wb = null; try { wb = Workbook.getWorkbook(new File(fileName)); // 打開(kāi)一個(gè)文件副本,并指定數(shù)據(jù)寫(xiě)回原文件. WritableWorkbook book = Workbook.createWorkbook(new File(fileName), wb); // 創(chuàng)建一個(gè)新的sheet到第2頁(yè)的位置 String[] sheetNames = wb.getSheetNames(); for(int i=0;i<sheetNames.length;i++){ if(sheetNames[i].equals(sheetName)){ System.out.println("已經(jīng)存在了,不用添加了." ); return ; } } WritableSheet sheet = book.createSheet(sheetName, 1); sheet.addCell(new Label(0, 0, "新加的測(cè)試數(shù)據(jù)")); book.write(); book.close(); } catch (Exception e) { System.out.println("出現(xiàn)異常" + e); e.printStackTrace(); } finally { wb.close(); } } } |
|