最近項目開(kāi)發(fā)中對excel操作比較頻繁,并結合正則表達式進(jìn)行了一些處理,整理一下.
1.正則表達式常用方法
Java代碼
/**
* 在第一個(gè)字符串中查找匹配字符串的個(gè)數
* @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;
}
/**
* 根據正則表達式分割str字符串成為一個(gè)一個(gè)的小的單元!
* (實(shí)際使用:在一個(gè)類(lèi)似語(yǔ)法分析的模塊中發(fā)揮重要作用)
* 例如:3+5*4 根據正則表達式+-\* 分割成數組 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;
}
/**
* 檢查是否含有指定的正則表達式匹配的子串.
* @param str 目標字符串
* @param regex 正則表達式,如果正則表達式含有"^......$"就是查找整個(gè)字符串對象是否符合正則表達式.
* @return
*/
public static boolean checkInclude(String str,String regex){
Pattern pattern = Pattern.compile(regex);
Matcher matcher = null;
matcher = pattern.matcher(str);
return matcher.find();
}
/**
* 方法字符串中符合正則表達式的子串的集合.
* @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()函數中可以含有數字,表示查找得到正則表達式中的goup匹配串.
ans.add(matcher.group());
System.out.println("找到匹配的字符串 \"" + matcher.group()
+ "\" 開(kāi)始于 " + matcher.start()
+ " 結束于 " + matcher.end() + ".");
}
return ans;
}
/*** 在第一個(gè)字符串中查找匹配字符串的個(gè)數* @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;}/*** 根據正則表達式分割str字符串成為一個(gè)一個(gè)的小的單元!* (實(shí)際使用:在一個(gè)類(lèi)似語(yǔ)法分析的模塊中發(fā)揮重要作用)* 例如:3+5*4 根據正則表達式+-\* 分割成數組 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;}/*** 檢查是否含有指定的正則表達式匹配的子串.* @param str 目標字符串* @param regex 正則表達式,如果正則表達式含有"^......$"就是查找整個(gè)字符串對象是否符合正則表達式.* @return*/public static boolean checkInclude(String str,String regex){Pattern pattern = Pattern.compile(regex);Matcher matcher = null;matcher = pattern.matcher(str);return matcher.find();}/*** 方法字符串中符合正則表達式的子串的集合.* @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()函數中可以含有數字,表示查找得到正則表達式中的goup匹配串.ans.add(matcher.group());System.out.println("找到匹配的字符串 \"" + matcher.group()+ "\" 開(kāi)始于 " + matcher.start()+ " 結束于 " + matcher.end() + ".");}return ans;}
下面是java正則表達式經(jīng)常使用的一些方法和說(shuō)明:
Java代碼
（1）使用matches方法快速建設是否表示給定的輸入字符串：Pattern.matches("\\d","1")返回true
（2）split(string)使用方法：Pattern.compile(":").split("one:two:three:four:five"); 返回:解析出“one two three four five”單詞
再比如使用數字作為一個(gè)分割字符串的方法：（注意下面的\\d不是正則表達式，而是前面加了一個(gè)轉義符號\）
Pattern.compile("\\d").split("one9two4three7four1five");也返回相同的結果。。
（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ā)現匹配的索引值：
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(): 嘗試從區域開(kāi)頭處開(kāi)始，輸入序列與該模式匹配。
public boolean find(): 嘗試地尋找輸入序列中，匹配模式的下一個(gè)子序列。
public boolean find(int start): 重置匹配器，然后從指定的索引處開(kāi)始，嘗試地尋找輸入序列中，匹配模式的下一個(gè)子序列。
public boolean matches(): 嘗試將整個(gè)區域與模式進(jìn)行匹配
替換方法
替換方法（replacement methods）用于在輸入的字符串中替換文本有用處的方法。
public Matcher appendReplacement(StringBuffer sb, String replacement)：實(shí)現非結尾處的增加和替換操作。
public StringBuffer appendTail(StringBuffer sb)：實(shí)現結尾處的增加和替換操作。
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 中的字符序列匹配。斜線(xiàn)（\）和美元符號（$）將不再有特殊意義了。
（1）使用matches方法快速建設是否表示給定的輸入字符串：Pattern.matches("\\d","1")返回true（2）split(string)使用方法：Pattern.compile(":").split("one:two:three:four:five"); 返回:解析出“one two three four five”單詞再比如使用數字作為一個(gè)分割字符串的方法：（注意下面的\\d不是正則表達式，而是前面加了一個(gè)轉義符號\）Pattern.compile("\\d").split("one9two4three7four1five");也返回相同的結果。。（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ā)現匹配的索引值： public int start()：返回之前匹配的開(kāi)始索引?！ ublic int start(int group)：返回之前匹配操作中通過(guò)給定組所捕獲序列的開(kāi)始索引?！ ublic int end(): 返回最后匹配字符后的偏移量?！ ublic int end(int group): 返回之前匹配操作中通過(guò)給定組所捕獲序列的最后字符之后的偏移量。研究方法 研究方法（study methods）回顧輸入的字符串，并且返回一個(gè)用于指示是否找到模式的布爾值?！ ublic boolean lookingAt(): 嘗試從區域開(kāi)頭處開(kāi)始，輸入序列與該模式匹配?！ ublic boolean find(): 嘗試地尋找輸入序列中，匹配模式的下一個(gè)子序列?！ ublic boolean find(int start): 重置匹配器，然后從指定的索引處開(kāi)始，嘗試地尋找輸入序列中，匹配模式的下一個(gè)子序列?！ ublic boolean matches(): 嘗試將整個(gè)區域與模式進(jìn)行匹配替換方法 替換方法（replacement methods）用于在輸入的字符串中替換文本有用處的方法?！ ublic Matcher appendReplacement(StringBuffer sb, String replacement)：實(shí)現非結尾處的增加和替換操作?！ ublic StringBuffer appendTail(StringBuffer sb)：實(shí)現結尾處的增加和替換操作?！ ublic String replaceAll(String replacement)：使用給定的替換字符串來(lái)替換輸入序列中匹配模式的每一個(gè)子序列?！ ublic String replaceFirst(String replacement)：使用給定的替換字符串來(lái)替換輸入序列中匹配模式的第一個(gè)子序列?！ ublic static String quoteReplacement(String s)：返回指定字符串的字面值來(lái)替換字符串。這個(gè)方法會(huì )生成一個(gè)字符串，用作 Matcher 的 appendReplacement 方法中的字面值替換 s。所產(chǎn)生的字符串將與作為字面值序列的 s 中的字符序列匹配。斜線(xiàn)（\）和美元符號（$）將不再有特殊意義了。
正則表達式基礎:
字符類(lèi)
[abc] a, b 或 c（簡(jiǎn)單類(lèi)）
[^abc] 除 a, b 或 c 之外的任意字符（取反）
[a-zA-Z] a 到 z，或 A 到 Z，包括（范圍）
[a-d[m-p]] a 到 d，或 m 到 p---等價(jià)于[a-dm-p]（并集） （特別注意這里的并集的方式啊，很特別?。。?div style="height:15px;">
[a-z&&[def]] d，e 或 f（交集）
[a-z&&[^bc]] 除 b 和 c 之外的 a 到 z 字符等價(jià)于[ad-z]（差集）
[a-z&&[^m-p]] a 到 z，并且不包括 m 到 p等價(jià)于[a-lq-z]（差集）
預定義字符類(lèi)
. 任何字符（匹配或者不匹配行結束符）
\d 數字字符：[0-9]
\t tab鍵
\D 非數字字符：[^0-9]
\s 空白字符：[\t\n\x0B\f\r]
\S 非空白字符：[^\s]
\w 單詞字符：[a-zA-Z_0-9]
\W 非單詞字符：[^\w]
邊界匹配器
^ 行首
$ 行尾
\b 單詞邊界
\B 非單詞邊界
\A 輸入的開(kāi)頭
\G 上一個(gè)匹配的結尾
\Z 輸入的結尾，僅用于最后的結束符（如果有的話(huà)）
\z 輸入的結尾
2.使用jxl進(jìn)行exlce的基本操作
下面基礎代碼來(lái)自于網(wǎng)絡(luò ):
Java代碼
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 工作表名稱(chēng)
* @param dataTitles 數據標題
*/
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(列號,行號,內容,風(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); //設置xls的保護，單元格為只讀的
sheet.getSettings().setDefaultColumnWidth(10); //設置列的默認寬度
//sheet.setRowView(2,false);//行高自動(dòng)擴展
//setRowView(int row, int height);--行高
//setColumnView(int col,int width); --列寬
sheet.setColumnView(0,20);//設置第一列寬度
}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();
}
}
/**
* 插入一行數據
* @param sheet 工作表
* @param row 行號
* @param content 內容
* @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();
}
}
/**
* 插入單元格數據
* @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();
}
}
/**
* 合并單元格，并插入數據
* @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;
}
/**
* 得到數據表頭格式
* @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);
//對齊方式
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;
}
/**
* 得到數據格式
* @return
*/
public WritableCellFormat getDataCellFormat(CellType type){
WritableCellFormat wcf = null;
try {
//字體樣式
if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//數字
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);
}
//對齊方式
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é)號","姓名","語(yǔ)文","數學(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);
}
}
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 工作表名稱(chēng)* @param dataTitles 數據標題*/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(列號,行號,內容,風(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); //設置xls的保護，單元格為只讀的sheet.getSettings().setDefaultColumnWidth(10); //設置列的默認寬度//sheet.setRowView(2,false);//行高自動(dòng)擴展//setRowView(int row, int height);--行高//setColumnView(int col,int width); --列寬sheet.setColumnView(0,20);//設置第一列寬度}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();}}/*** 插入一行數據* @param sheet 工作表* @param row 行號* @param content 內容* @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();}}/*** 插入單元格數據* @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();}}/*** 合并單元格，并插入數據* @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;}/*** 得到數據表頭格式* @return*/public WritableCellFormat getTitleCellFormat(){WritableCellFormat wcf = null;try {//字體樣式WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.NO_BOLD,false);//最后一個(gè)為是否italicwf.setColour(Colour.RED);wcf = new WritableCellFormat(wf);//對齊方式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;}/*** 得到數據格式* @return*/public WritableCellFormat getDataCellFormat(CellType type){WritableCellFormat wcf = null;try {//字體樣式if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//數字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è)為是否italicwcf = new WritableCellFormat(wf);}//對齊方式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é)號","姓名","語(yǔ)文","數學(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è)十分有用針對excel操作的的工具方法:
Java代碼
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;
//存儲帶有級別信息的內容到位置的映射關(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("出現異常" + e);
e.printStackTrace();
} finally {
wb.close();
}
}
private static String allChar = "abcdefghijklmnopqrstuvwxyz";
/**
* 從字符中得到列數.例如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;
}
/**
* 根據行號和列號得到所在的單元格.例如(3,4)-->"E4"
* @param vNum 縱坐標
* @param hNum 橫坐標
* @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的某一塊的數據....用于模板里面讀取單元格.
* @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("出現異常" + e);
e.printStackTrace();
} finally {
wb.close();
}
return allData;
}
/**
* 讀取指定excel中的指定sheet的某一塊的數據....用于模板里面讀取單元格.
* @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);
}
/**
* 設置excel中的sheet頁(yè)全部隱藏
* @param fileName
*/
public static void setAllHiddenSheet(String fileName) {
Workbook wb = null;
try {
wb = Workbook.getWorkbook(new File(fileName));
// 打開(kāi)一個(gè)文件副本,并指定數據寫(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("出現異常" + 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è)文件副本,并指定數據寫(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, "新加的測試數據"));
book.write();
book.close();
} catch (Exception e) {
System.out.println("出現異常" + e);
e.printStackTrace();
} finally {
wb.close();
}
}
}
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;//存儲帶有級別信息的內容到位置的映射關(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("出現異常" + e);e.printStackTrace();} finally {wb.close();}}private static String allChar = "abcdefghijklmnopqrstuvwxyz";/*** 從字符中得到列數.例如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;}elsereturn allChar.indexOf(code);return result;}/*** 根據行號和列號得到所在的單元格.例如(3,4)-->"E4"* @param vNum 縱坐標* @param hNum 橫坐標* @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的某一塊的數據....用于模板里面讀取單元格.* @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("出現異常" + e);e.printStackTrace();} finally {wb.close();}return allData;}/*** 讀取指定excel中的指定sheet的某一塊的數據....用于模板里面讀取單元格.* @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);}/*** 設置excel中的sheet頁(yè)全部隱藏* @param fileName*/public static void setAllHiddenSheet(String fileName) {Workbook wb = null;try {wb = Workbook.getWorkbook(new File(fileName));// 打開(kāi)一個(gè)文件副本,并指定數據寫(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("出現異常" + 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è)文件副本,并指定數據寫(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, "新加的測試數據"));book.write();book.close();} catch (Exception e) {System.out.println("出現異常" + e);e.printStackTrace();} finally {wb.close();}}}
