<link rel="stylesheet" href="https://js.how234.com/third-party/SyntaxHighlighter/shCoreDefault.css" type="text/css" /><script type="text/javascript" src="https://js.how234.com/third-party/SyntaxHighlighter/shCore.js"></script><script type="text/javascript"> SyntaxHighlighter.all(); </script>
很多朋友都想知道java本地excel是什麼?怎麼讀取?下面就一起來了解一下吧~
java excel api:是一開放原始碼專案,通過它Java開發人員可以讀取Excel檔案的內容、建立新的Excel檔案、更新已經存在的Excel檔案。使用該API非Windows作業系統也可以通過純Java應用來處理Excel資料表。因為它是使用Java編寫的,所以在Web應用中可以通過JSP、Servlet來呼叫API實現對Excel資料表的訪問。
Java通過jexcelapi包操作excel檔案:
//in ExcelHandle import jxl.*; import jxl.format.UnderlineStyle; import jxl.write.*; import jxl.write.Number; import jxl.write.Boolean; import jxl.Cell; import java.io.*; public class ExcelHandle { public ExcelHandle() { } /***讀取Excel*/ public static void readExcel(String filePath) { try { InputStream is = new FileInputStream(filePath); Workbook rwb = Workbook.getWorkbook(is); //這裡有兩種方法獲取sheet表:名字和下標(從0開始) //Sheet st = rwb.getSheet("original"); Sheet st = rwb.getSheet(0); /** //獲得第一行第一列單元的值 Cell c00 = st.getCell(0,0); //通用的獲取cell值的方式,返回字串 String strc00 = c00.getContents(); //獲得cell具體型別值的方式 if(c00.getType() == CellType.LABEL) { LabelCell labelc00 = (LabelCell)c00; strc00 = labelc00.getString(); } //輸出 System.out.println(strc00);*/ //Sheet的下標是從0開始 //獲取第一張Sheet表 Sheet rst = rwb.getSheet(0); //獲取Sheet表中所包含的總列數 int rsColumns = rst.getColumns(); //獲取Sheet表中所包含的總行數 int rsRows = rst.getRows(); //獲取指定單元格的物件引用 for (int i = 0; i < rsRows; i++) { for (int j = 0; j < rsColumns; j++) { Cell cell = rst.getCell(j, i); System.out.print(cell.getContents() + " "); } System.out.println(); } //關閉 rwb.close(); } catch(Exception e) { e.printStackTrace(); } } /**輸出Excel*/ public static void writeExcel(OutputStream os) { try { /** 只能通過API提供的 工廠方法來建立Workbook,而不能使用WritableWorkbook的建構函式,因為類WritableWorkbook的建構函式為 protected型別:方法一:直接從目標檔案中讀取 WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));方法 二:如下例項所示 將WritableWorkbook直接寫入到輸出流*/ WritableWorkbook wwb = Workbook.createWorkbook(os); //建立Excel工作表 指定名稱和位置 WritableSheet ws = wwb.createSheet("Test Sheet 1",0); /**************往工作表中新增資料*****************/ //1.新增Label物件 Label label = new Label(0,0,"測試"); ws.addCell(label); //新增帶有字型Formatting物件 WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true); WritableCellFormat wcf = new WritableCellFormat(wf); Label labelcf = new Label(1,0,"this is a label test",wcf); ws.addCell(labelcf); //新增帶有字型顏色的Formatting物件 WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.DARK_YELLOW); WritableCellFormat wcfFC = new WritableCellFormat(wfc); Label labelCF = new Label(1,0,"Ok",wcfFC); ws.addCell(labelCF); //2.新增Number物件 Number labelN = new Number(0,1,3.1415926); ws.addCell(labelN); //新增帶有formatting的Number物件 NumberFormat nf = new NumberFormat("#.##"); WritableCellFormat wcfN = new WritableCellFormat(nf); Number labelNF = new jxl.write.Number(1,1,3.1415926,wcfN); ws.addCell(labelNF); //3.新增Boolean物件 Boolean labelB = new jxl.write.Boolean(0,2,true); ws.addCell(labelB); Boolean labelB1 = new jxl.write.Boolean(1,2,false); ws.addCell(labelB1); //4.新增DateTime物件 jxl.write.DateTime labelDT = new jxl.write.DateTime(0,3,new java.util.Date()); ws.addCell(labelDT); //5.新增帶有formatting的DateFormat物件 DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss"); WritableCellFormat wcfDF = new WritableCellFormat(df); DateTime labelDTF = new DateTime(1,3,new java.util.Date(),wcfDF); ws.addCell(labelDTF); //6.新增圖片物件,jxl只支援png格式圖片 File image = new File("f:1.png"); WritableImage wimage = new WritableImage(0,4,6,17,image); ws.addImage(wimage); //7.寫入工作表 wwb.write(); wwb.close(); } catch(Exception e) { e.printStackTrace(); } } /** 將file1拷貝後,進行修改並建立輸出物件file2 * 單元格原有的格式化修飾不能去掉,但仍可將新的單元格修飾加上去, * 以使單元格的內容以不同的形式表現 */ public static void modifyExcel(File file1,File file2) { try { Workbook rwb = Workbook.getWorkbook(file1); WritableWorkbook wwb = Workbook.createWorkbook(file2,rwb);//copy WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE); WritableCellFormat wcfFC = new WritableCellFormat(wfc); WritableSheet ws = wwb.getSheet(0); WritableCell wc = ws.getWritableCell(0,0); //判斷單元格的型別,做出相應的轉換 if(wc.getType() == CellType.LABEL) { Label labelCF =new Label(0,0,"人物(新)",wcfFC); ws.addCell(labelCF); //Label label = (Label)wc; //label.setString("被修改"); } wwb.write(); wwb.close(); rwb.close(); } catch(Exception e) { e.printStackTrace(); } } //測試 public static void main(String args[]) { try { //讀EXCEL ExcelHandle.readExcel("F:/紅樓人物.xls"); //輸出EXCEL File filewrite=new File("F:/紅樓人物2.xls"); filewrite.createNewFile(); OutputStream os=new FileOutputStream(filewrite); ExcelHandle.writeExcel(os); //修改EXCEL ExcelHandle.modifyExcel(new File("F:/紅樓人物.xls"), new File("F:/紅樓人物3.xls")); } catch(Exception e) { e.printStackTrace(); } } }