java对excel文件内容读写修改操作
Read.java
package domain; import java.io.FileInputStream; import java.io.InputStream; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; public class Read { public 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(); } } }
Writeadd.java
package domain; import java.io.File; import java.io.OutputStream; import jxl.Workbook; import jxl.format.UnderlineStyle; import jxl.write.Boolean; import jxl.write.DateFormat; import jxl.write.DateTime; import jxl.write.Label; import jxl.write.Number; import jxl.write.NumberFormat; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableImage; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class Writeadd { /** 输出Excel */ public void writeExcel(OutputStream os) { try { WritableWorkbook wwb = Workbook.createWorkbook(os); // 创建Excel工作表 指定名称和位置 WritableSheet ws = wwb.createSheet("Test Sheet 1", 0); /************** 往工作表中添加数据 *****************/ // 1.添加Label对象 Label label = new Label(5, 0, "测试"); Label labe2 = new Label(6, 0, "20163432"); Label labe3 = new Label(7, 0, "20163432"); ws.addCell(label); ws.addCell(labe2); ws.addCell(labe3); // 添加带有字型Formatting对象 WritableFont wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true); WritableCellFormat wcf = new WritableCellFormat(wf); Label labelcf = new Label(1, 0, "hello word !", 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, "石家庄铁道大学软件工程系", 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("C:\\Users\\lenovo\\Desktop\\timg.png"); WritableImage wimage = new WritableImage(6, 4, 4, 20, image); ws.addImage(wimage); // 7.写入工作表 wwb.write(); wwb.close(); } catch (Exception e) { e.printStackTrace(); } } }
Update.java
package domain; import java.io.File; import jxl.CellType; import jxl.Workbook; import jxl.format.UnderlineStyle; import jxl.write.Label; import jxl.write.WritableCell; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class Update { /** * 将file1拷贝后,进行修改并创建输出对象file2 * * 单元格原有的格式化修饰不能去掉，但仍可将新的单元格修饰加上去， * * 以使单元格的内容以不同的形式表现 * */ public 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(1, 0, "人物（新）", wcfFC); ws.addCell(labelCF); Label label = (Label) wc; label.setString("被修改"); } wwb.write(); wwb.close(); rwb.close(); } catch (Exception e) { e.printStackTrace(); } } }
Zhuhanshu.java
package domain; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import java.util.Scanner; public class Zhuhanshu { public static void main(String args[]) { // 读EXCEL Read read = null; // 输出EXCEL Writeadd writeadd = null; // 修改EXCEL Update update = null; try { int n = 0; Scanner in = new Scanner(System.in); while (n != 20) { System.out.println("**********java实现对excel文件的读写修改操作××××××××××"); System.out.println("1.读取指定的excel文件内容的信息"); System.out.println("2.向excel文件中写入内容"); System.out.println("3.修改excel文件");// 添加信息 System.out.println("5.退出"); System.out.println("请选择："); if (in.hasNextInt()) { n = in.nextInt(); } else { System.out.println("输入的不是整数，请重新输入："); continue; } switch (n) { case 1: { // 读EXCEL read = new Read(); read.readExcel("C:\\Users\\lenovo\\Desktop\\测试.xls"); break; } case 2: { // 输出EXCEL writeadd = new Writeadd(); File filewrite = new File("C:\\Users\\lenovo\\Desktop\\测试2.xls"); filewrite.createNewFile(); OutputStream os = new FileOutputStream(filewrite); writeadd.writeExcel(os); System.out.println("执行完成"); break; } case 3: { // 修改EXCEL update = new Update(); update.modifyExcel(new File("C:\\Users\\lenovo\\Desktop\\测试.xls"), new File("C:\\Users\\lenovo\\Desktop\\测试3.xls")); System.out.println("执行完成"); break; } case 5: break; default: System.out.println("输入错误，请重新输入"); break; } } } catch (Exception e) { e.printStackTrace(); } } }
运行截图
源代码：https://pan.baidu.com/s/1cXgc2woaq5xrpkN4Pqhbeg
