Java操作文件二（excel）

ゞ浴缸里的玫瑰 2022-07-12 05:57 198阅读 0赞

**一、jxl读写excel**

需要jxl.jar包

package com.primeton.eos.sdic.file; import java.awt.image.BufferedImage; import java.io.File; import java.util.Date; import javax.imageio.ImageIO; import jxl.Cell; import jxl.CellType; import jxl.DateCell; import jxl.NumberCell; import jxl.Sheet; import jxl.Workbook; import jxl.format.UnderlineStyle; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableImage; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class ExcelUtil \{ //读取excel public void readExcel(String filePath) \{ Workbook wb = null; try \{ // 获取excel对象 wb = Workbook.getWorkbook(new File(filePath)); for (int i = 0; i < wb.getNumberOfSheets(); i++) \{ // 取sheet页 Sheet sheet = wb.getSheet(i); System.out.println("sheet: " + sheet.getName()+ " start \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*"); // 每行读取 for (int rownum = 0; rownum < sheet.getRows(); rownum++) \{ // 读取列 for (int colnum = 0; colnum < sheet.getColumns(); colnum++) \{ // 取单元格信息 Cell cell = sheet.getCell(colnum, rownum); if(cell.getType()==CellType.DATE)\{ DateCell dc=(DateCell)cell; Date date = dc.getDate(); System.out.print(date); \}else if(cell.getType()==CellType.NUMBER||cell.getType()==CellType.NUMBER\_FORMULA)\{ NumberCell nc=(NumberCell)cell; double num = nc.getValue(); System.out.print(num); \}else\{ String str = cell.getContents(); System.out.print(str); \} System.out.print("("+cell.getType()+") | "); \} System.out.println(); \} System.out.println("sheet: " + sheet.getName()+ " end \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*"); \} \} catch (Exception e) \{ e.printStackTrace(); \} finally\{ try\{ if(wb!=null)\{ wb.close(); \} \}catch(Exception e)\{ e.printStackTrace(); \} \} \} //写入excel public void writeExcel(String filePath) \{ WritableWorkbook wwb = null; try \{ // 读取excel模板文件 // Workbook wb = Workbook.getWorkbook(new File("srcFile.xls")); // WritableWorkbook wwb = Workbook.createWorkbook(new File(filePath),wb); // 创建空的excel wwb = Workbook.createWorkbook(new File(filePath)); // 创建名称为sheet1的sheet页 wwb.createSheet("sheet1", 1); // 获取当前操作sheet WritableSheet ws = wwb.getSheet(0); //预定义的一些字体和格式，同一个Excel中最好不要有太多格式 //字体格式 WritableFont wfont= new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false,UnderlineStyle.NO\_UNDERLINE,jxl.format.Colour.BLACK); //单元格格式 WritableCellFormat celFormat= new WritableCellFormat(wfont); celFormat.setAlignment(jxl.format.Alignment.CENTRE);//设置单元格垂直居中 celFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//设置单元格水平居中 celFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN, jxl.format.Colour.DARK\_BLUE);//边框深蓝色 celFormat.setBackground(jxl.format.Colour.ICE\_BLUE);//设置底色为冰蓝 //边框 WritableCellFormat boder = new WritableCellFormat(); boder.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN, jxl.format.Colour.DARK\_BLUE);//边框深蓝色 //数字格式 jxl.write.NumberFormat nf = new jxl.write.NumberFormat("\#.\#\#"); WritableCellFormat numFormat = new WritableCellFormat(nf); //日期格式 jxl.write.DateFormat df=new jxl.write.DateFormat("yyyy-MM-dd"); WritableCellFormat dateFormat = new WritableCellFormat (wfont, df); int rows = 0; ws.setColumnView(rows, 15);// 设置列宽 ws.mergeCells(0, rows, 2, rows+1); //合并单元格，0 到 1 行，0 到 2 列 ws.addCell(new Label(0, rows, "\*\*\*\*标题\*\*\*\*",celFormat)); rows=rows+2; ws.setRowView(rows, 600);// 第 row 行的高度 // 写入数据 ws.addCell(new Label(0, rows, "测试1",celFormat)); ws.addCell(new Label(1, rows, "测试2",celFormat)); ws.addCell(new Label(2, rows, "测试3",celFormat)); rows++; for(int i=rows;i<20;i++)\{ ws.addCell(new Label(0, i, "测试1", boder)); ws.addCell(new Label(1, i, "测试2", boder)); ws.addCell(new Label(2, i, "测试3", boder)); rows++; \} //写入日期时间格式单元格 jxl.write.DateTime labelDT = new jxl.write.DateTime(0, rows, new Date(), dateFormat); ws.addCell(labelDT); //写入数字格式单元格 jxl.write.Number labelNF = new jxl.write.Number(1, rows,-12.3456, numFormat); ws.addCell(labelNF); //写入Boolean格式单元格 jxl.write.Boolean labelBO = new jxl.write.Boolean(2, rows, false); ws.addCell(labelBO); rows++; //写格式单元格 ws.addCell(new Label(0, rows, "测试", celFormat)); //添加图片对象，jxl只支持png格式图片，此处图片会被拉伸 WritableImage wimage = new WritableImage(1, rows,1, 1, new File("D:/logo.png")); ws.addImage(wimage); //添加图片对象，此处保持图片大小100% addImage(2, rows, new File("D:/logo.png"), ws); wwb.write(); \} catch (Exception e) \{ e.printStackTrace(); \} finally \{ try \{ if (wwb != null) \{ wwb.close(); \} \} catch (Exception e) \{ e.printStackTrace(); \} \} \} /\*\* \* 导入图片，保持图片尺寸 \* @param double 图片左上角相对excel位置（列位置） \* @param double 图片左上角相对excel位置（行位置） \* @param File 图片文件 \* @param WritableSheet 操作sheet \*/ private static void addImage(double col,double row,File file,WritableSheet ws) throws Exception\{ // 获取图象大小 BufferedImage bi7 = ImageIO.read(file); System.out.println("filePath=" + file.getAbsolutePath()); int picWidth = bi7.getWidth(); // 图片宽, 像素 150 int picHeight = bi7.getHeight(); // 图片高, 像素 105 // 输入参数, 图片显示的位置 double picBeginCol = col; double picBeginRow = row; /\* \* 实际像素: 150/105 = 2.78 cm / 3.97 cm = 4832 / 1590 实际像素: 300/210 = 2倍大小 = \* 9600 / 3150 比率: 32 / 15 \*/ // 计算参数( picCellWidth, picCellHeight ), 图片显示大小, 默认 100% 显示: begin // 图片cell宽度 = 图片实际跨越每个cell所占长度的相对各个cell ratio的和 // 方法: 根据起始位置,计算图片实际跨越的区域, 然后计算相对ratio,然后累加 // double picCellWidth = 0.0; // 是 cell的跨越个数, 可小数 double picCellHeight = 0.0; int \_picWidth = picWidth \* 32; // pic的宽度,循环递减, 是jxl的宽度单位, 32/15 for (int x = 0; x < 1234; x++) \{ int bc = (int) Math.floor(picBeginCol + x); // 3.6 to 3 // // 本次循环所在cell位置 int v = ws.getColumnView(bc).getSize(); // 本次cell宽,jxl单位 double \_offset0 = 0.0; // >= 0 // 离左边的偏移量, 仅 x = 0 的时候才用 if (0 == x) \_offset0 = (picBeginCol - bc) \* v; if (0.0 + \_offset0 + \_picWidth > v) // \_picWidth // 剩余长度超过一个cell时 \{ // 计算本次cell内, pic 所占 ratio值, 累加到 picCellWidth double \_ratio = 1.0; if (0 == x) \_ratio = (0.0 + v - \_offset0) / v; // picCellWidth += 1.0; picCellWidth += \_ratio; \_picWidth -= (int) (0.0 + v - \_offset0); // int \} else // \_picWidth 剩余长度在一个cell内时 \{ double \_ratio = 0.0; if (v != 0) \_ratio = (0.0 + \_picWidth) / v; picCellWidth += \_ratio; break; \} \} // 此时 picCellWidth 是图片实际的值了 int \_picHeight = picHeight \* 15; // pic的高度,循环递减, 是jxl的高度单位, 32/15 for (int x = 0; x < 1234; x++) \{ int bc = (int) Math.floor(picBeginRow + x); // 3.6 to 3 // // 本次循环所在cell位置 int v = ws.getRowView(bc).getSize(); // 本次cell高,jxl单位 double \_offset0 = 0.0; // >= 0 // 离顶部的偏移量, 仅 x = 0 的时候才用 if (0 == x) \_offset0 = (picBeginRow - bc) \* v; // if (0.0 + \_offset0 + \_picHeight > v) // \_picHeight // 剩余长度超过一个cell时 \{ // 计算本次cell内, pic 所占 ratio值, 累加到 picCellHeight double \_ratio = 1.0; if (0 == x) \_ratio = (0.0 + v - \_offset0) / v; // picCellHeight += 1.0; picCellHeight += \_ratio; \_picHeight -= (int) (0.0 + v - \_offset0); // int \} else // \_picHeight 剩余长度在一个cell内时 \{ double \_ratio = 0.0; if (v != 0) \_ratio = (0.0 + \_picHeight) / v; picCellHeight += \_ratio; break; \} \} // 此时 picCellHeight 是图片实际的值了 // 计算参数( picCellWidth, picCellHeight ), 图片显示大小, 默认 100% 显示: end System.out.println("picBeginCol=" + picBeginCol); System.out.println("picBeginRow=" + picBeginRow); System.out.println("picCellWidth=" + picCellWidth); System.out.println("picCellHeight=" + picCellHeight); WritableImage wimage = new WritableImage(picBeginCol, picBeginRow,picCellWidth, picCellHeight, file); // 图片写到xls中 ws.addImage(wimage); \} public static void main(String args\[\])\{ ExcelUtil eu = new ExcelUtil(); eu.writeExcel("D:/test.xls"); \} \}

**二、poi读写excel**

**五、JavaScript导出excel**

html table --> excel

<table id="tableExcel"> <tr> <td>1</td> <td>2</td> </tr> <tr> <td>3</td> <td>4</td> </tr> </table> <input type="button" οnclick="javascript:method2('tableExcel');" value="导出EXCEL"> <SCRIPT LANGUAGE="javascript"> function method1(tableid) \{//整个表格拷贝到EXCEL中 var curTbl = document.getElementById(tableid); var oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel var oWB = oXL.Workbooks.Add(); //获取workbook对象 var oSheet = oWB.ActiveSheet; //激活当前sheet var sel = document.body.createTextRange(); sel.moveToElementText(curTbl); //把表格中的内容移到TextRange中 sel.select(); //全选TextRange中内容 sel.execCommand("Copy"); //复制TextRange中内容 oSheet.Paste(); //粘贴到活动的EXCEL中 oXL.Visible = true; //设置excel可见属性 \} function method2(tableid) //读取表格中每个单元到EXCEL中 \{ var curTbl = document.getElementById(tableid); var oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel var oWB = oXL.Workbooks.Add(); //获取workbook对象 var oSheet = oWB.ActiveSheet; //激活当前sheet var Lenr = curTbl.rows.length; //取得表格行数 for (i = 0; i < Lenr; i++) \{ var Lenc = curTbl.rows(i).cells.length; //取得每行的列数 for (j = 0; j < Lenc; j++) \{ oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText; //赋值 \} \} oXL.Visible = true; //设置excel可见属性 \} </SCRIPT>

一、jxl读写excel