主流的操作Excel的有两种方法,一种是通过poi包,另一种是通过jxl包。这里我主要讲解通过jxl包来读写Excel。
首先需要导入一个jxl.jar包。
下载地址:http://www.andykhan.com/jexcelapi/download.html
下载后的文件会包含jxl相关的API。
下面直接上代码:
WriteExcel.java
1 import java.io.File; 2 3 import java.io.IOException; 4 5 import java.util.Locale; 6 7 8 9 import jxl.CellView; 10 11 import jxl.Workbook; 12 13 import jxl.WorkbookSettings; 14 15 import jxl.format.UnderlineStyle; 16 17 import jxl.write.Formula; 18 19 import jxl.write.Label; 20 21 import jxl.write.Number; 22 23 import jxl.write.WritableCellFormat; 24 25 import jxl.write.WritableFont; 26 27 import jxl.write.WritableSheet; 28 29 import jxl.write.WritableWorkbook; 30 31 import jxl.write.WriteException; 32 33 import jxl.write.biff.RowsExceededException; 34 35 36 37 public class WriteExcel { 38 39 40 41 private WritableCellFormat timesBoldUnderline; 42 43 private WritableCellFormat times; 44 45 private String inputFile; 46 47 48 49 public void setOutputFile(String inputFile) { 50 51 this.inputFile = inputFile; 52 53 } 54 55 56 57 public void write() throws IOException, WriteException { 58 59 File file = new File(inputFile); 60 61 WorkbookSettings wbSettings = new WorkbookSettings(); 62 63 64 65 wbSettings.setLocale(new Locale("en", "EN")); 66 67 68 69 WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings); 70 71 workbook.createSheet("Report", 0); 72 73 WritableSheet excelSheet = workbook.getSheet(0); 74 75 createLabel(excelSheet); 76 77 createContent(excelSheet); 78 79 80 81 workbook.write(); 82 83 workbook.close(); 84 85 } 86 87 88 89 private void createLabel(WritableSheet sheet) 90 91 throws WriteException { 92 93 94 95 WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10); 96 97 98 99 times = new WritableCellFormat(times10pt);100 101 102 103 times.setWrap(true);104 105 106 107 WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,108 109 UnderlineStyle.SINGLE);110 111 timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);112 113 114 115 timesBoldUnderline.setWrap(true);116 117 118 119 CellView cv = new CellView();120 121 cv.setFormat(times);122 123 cv.setFormat(timesBoldUnderline);124 125 cv.setAutosize(true);126 127 128 129 130 131 addCaption(sheet, 0, 0, "Header 1");132 133 addCaption(sheet, 1, 0, "This is another header");134 135 136 137 138 139 }140 141 142 143 private void createContent(WritableSheet sheet) throws WriteException,144 145 RowsExceededException {146 147 148 149 for (int i = 1; i < 10; i++) {150 151 152 153 addNumber(sheet, 0, i, i + 10);154 155 156 157 addNumber(sheet, 1, i, i * i);158 159 }160 161 162 163 StringBuffer buf = new StringBuffer();164 165 buf.append("SUM(A2:A10)");166 167 Formula f = new Formula(0, 10, buf.toString());168 169 sheet.addCell(f);170 171 buf = new StringBuffer();172 173 buf.append("SUM(B2:B10)");174 175 f = new Formula(1, 10, buf.toString());176 177 sheet.addCell(f);178 179 180 181 for (int i = 12; i < 20; i++) {182 183 addLabel(sheet, 0, i, "Boring text " + i);184 185 addLabel(sheet, 1, i, "Another text");186 187 }188 189 }190 191 192 193 private void addCaption(WritableSheet sheet, int column, int row, String s)194 195 throws RowsExceededException, WriteException {196 197 Label label;198 199 label = new Label(column, row, s, timesBoldUnderline);200 201 sheet.addCell(label);202 203 }204 205 206 207 private void addNumber(WritableSheet sheet, int column, int row,208 209 Integer integer) throws WriteException, RowsExceededException {210 211 Number number;212 213 number = new Number(column, row, integer, times);214 215 sheet.addCell(number);216 217 }218 219 220 221 private void addLabel(WritableSheet sheet, int column, int row, String s)222 223 throws WriteException, RowsExceededException {224 225 Label label;226 227 label = new Label(column, row, s, times);228 229 sheet.addCell(label);230 231 }232 233 234 235 }
ReadExcel.java
1 import java.io.File; 2 import java.io.IOException; 3 4 import jxl.Cell; 5 import jxl.CellType; 6 import jxl.Sheet; 7 import jxl.Workbook; 8 import jxl.read.biff.BiffException; 9 10 public class ReadExcel {11 private String inputFile;12 13 public void setInputFile(String inputFile) {14 this.inputFile = inputFile;15 }16 17 public void read() throws IOException {18 File inputWorkbook = new File(inputFile);19 Workbook w;20 try {21 w = Workbook.getWorkbook(inputWorkbook);22 23 Sheet sheet = w.getSheet(0);24 25 for (int j = 0; j < sheet.getColumns(); j++) {26 for (int i = 0; i < sheet.getRows(); i++) {27 Cell cell = sheet.getCell(j, i);28 CellType type = cell.getType();29 if (type == CellType.LABEL) {30 System.out.println("I got a label "31 + cell.getContents());32 }33 34 if (type == CellType.NUMBER) {35 System.out.println("I got a number "36 + cell.getContents());37 }38 39 }40 }41 } catch (BiffException e) {42 e.printStackTrace();43 }44 }45 46 }
调运WriteExcel的代码:
1 WriteExcel writeExcel=new WriteExcel(); 2 3 writeExcel.setOutputFile("/sdcard/test.xls"); 4 5 try { 6 7 writeExcel.write(); 8 9 } catch (IOException e) {10 11 e.printStackTrace();12 13 } catch (WriteException e) {14 15 e.printStackTrace();16 17 }
执行完WriteExcel后便会在Android虚拟机中生成test.xls文件
调运ReadExcel的代码:
1 ReadExcel readExcel=new ReadExcel(); 2 3 readExcel.setInputFile("/sdcard/test.xls"); 4 5 try { 6 7 readExcel.read(); 8 9 } catch (IOException e) {10 11 e.printStackTrace();12 13 }14 15
好了,Android读写Excel的功能就能基本实现了。