操作方法
首先创建以下两个类： package com.util;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.util.ArrayList;import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class Excelread2010{ /** *//** 总行数 */ private static int totalRows = 0; /** *//** 总列数 */ private static int totalCells = 0; public static ArrayList<ArrayList<String>> zonglist=null; /** *//** 构造方法 */ public Excelread2010(){ totalRows=0;totalCells=0; } //<li>Description:[根据文件名读取excel文件]</li> public static String read(String fileName) { zonglist=null; String ret=""; ArrayList<ArrayList<String>> dataLst= new ArrayList<ArrayList<String>>(); /** *//** 检查文件名是否为空或者是否是Excel格式的文件 */ if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) { ret="路径或格式或文件错误"; return ret; } boolean isExcel2003 = true; /** *//** 对文件的合法性进行验证 */ //检查是否为2010版本还是2003版本 if (fileName.matches("^.+\\.(?i)(xlsx)$")) { isExcel2003 = false; } /** *//** 检查文件是否存在 */ File file = new File(fileName); if (file == null || !file.exists()) { ret="文件不存在！"; return ret; } try { //System.out.println(file+"file"); /** *//** 调用本类提供的根据流读取的方法 */ zonglist= read(new FileInputStream(file), isExcel2003); }catch (Exception ex) { ex.printStackTrace(); } /** *//** 返回最后读取的结果 */ return ret; } // <li>Description:[根据流读取Excel文件]</li> public static ArrayList<ArrayList<String>> read(InputStream inputStream, boolean isExcel2003) { ArrayList<ArrayList<String>> dataLst = null; try { System.out.println(isExcel2003+"isExcel2003"); /** *//** 根据版本选择创建Workbook的方式 */ // Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream): new XSSFWorkbook(inputStream); Workbook wb=null; if(isExcel2003){ wb =new HSSFWorkbook(inputStream); //System.out.println("2007一下版本--xls"); } else { // System.out.println("2007以上版本--xlsx"); wb =new XSSFWorkbook(inputStream); } dataLst = read(wb); } catch (IOException e){ e.printStackTrace(); } return dataLst; } //* * <li>Description:[得到总行数]</li> public int getTotalRows() { return totalRows; } //Description:[得到总列数]</li> public int getTotalCells() { return totalCells; } //Description:[读取数据]</li> //导入ss、usermodel private static ArrayList<ArrayList<String>> read(Workbook wb) { ArrayList<ArrayList<String>> zonglist = new ArrayList<ArrayList<String>>(); /** *//** 得到第一个shell */ Sheet sheet = wb.getSheetAt(0); //获取总行数 totalRows = sheet.getPhysicalNumberOfRows(); //System.out.println(totalRows+"总行数"); if (totalRows >= 1 && sheet.getRow(0) != null) { totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); //System.out.println(totalCells+"总列数"); } /** *//** 循环Excel的行 */ for (int r = 0; r < totalRows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } ArrayList<String> rowlist=new ArrayList<String> (); /** *//** 循环Excel的列 */ for (short c = 0; c < totalCells; c++) { Cell cell = row.getCell(c); String cellValue = ""; if (cell == null) { rowlist.add(cellValue); continue; } /** *//** 处理数字型的,自动去零 */ if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { cellValue = getRightStr(cell.getNumericCellValue() + ""); } /** *//** 处理字符串型 */ else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { cellValue = cell.getStringCellValue(); } /** *//** 处理布尔型 */ else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) { cellValue = cell.getBooleanCellValue() + ""; } /** *//** 其它的,非以上几种数据类型 */ else { cellValue = cell.toString() + ""; } rowlist.add(cellValue); } zonglist.add(rowlist); //rowlist.clear(); } return zonglist; } //[正确地处理整数后自动加零的情况]</li> private static String getRightStr(String sNum) { DecimalFormat decimalFormat = new DecimalFormat("#.000000"); String resultStr = decimalFormat.format(new Double(sNum)); if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) { resultStr = resultStr.substring(0, resultStr.indexOf(".")); } return resultStr; } // public static void main(String[] args) throws Exception {// String ret=new Excelreadandwriter20101().read("D:\\yuan1.xlsx");// System.out.println(ret+"ret");// //输出数据// for(int i=0;i<zonglist.size();i++){// // for(int j=0;j<zonglist.get(i).size();j++){// System.out.print(zonglist.get(i).get(j)+";");// }// System.out.println();// } //} }
package com.util; import java.io.File;import java.util.List; import jxl.Workbook;import jxl.format.UnderlineStyle;import jxl.write.Label;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook; public class ExcelUtils { /** * * @param file * :导出文件名 * @param title * :表格标题 * @param field * :表头字段 * @param data * :数据行 */ public static void exportExcel(File file, String title, String[] field, List<String[]> data) { try { WritableWorkbook book = Workbook.createWorkbook(file); WritableSheet sheet = book.createSheet("sheet1", 0); sheet.setColumnView(0, 27); sheet.setColumnView(1, 20); sheet.setColumnView(2, 17); sheet.setColumnView(4, 40); sheet.setColumnView(5, 40); sheet.setColumnView(10, 15); jxl.write.WritableFont font = new jxl.write.WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); jxl.write.WritableFont font1 = new jxl.write.WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); jxl.write.WritableCellFormat f = new jxl.write.WritableCellFormat(font); jxl.write.WritableCellFormat f1 = new jxl.write.WritableCellFormat(font1); f.setAlignment(jxl.format.Alignment.CENTRE); f1.setAlignment(jxl.format.Alignment.CENTRE); sheet.addCell(new Label(0, 0, title,f)); // 加入标题 sheet.mergeCells(0, 0, field.length - 1, 0);// 合并标题区 for (int i = 0; i < field.length; i++) { sheet.addCell(new Label(i, 1, field[i],f1)); // 加入表头字段 } for (int i = 0; i < data.size(); i++) { for (int j = 0; j < data.get(i).length; j++) { sheet.addCell(new Label(j, i + 2, data.get(i)[j])); // 加入表头字段 } } /* 写入数据并关闭文件 */ book.write(); book.close(); } catch (Exception e) { System.err.println("in#ExcelUtils#exportExcel"); e.printStackTrace(); } } public static void ExportExcel(File file, List<String[]> data) { try { WritableWorkbook book = Workbook.createWorkbook(file); WritableSheet sheet = book.createSheet("sheet1", 0); //设置网格宽度 sheet.setColumnView(0, 20); sheet.setColumnView(1, 20); sheet.setColumnView(2, 20); sheet.setColumnView(3, 20); sheet.setColumnView(4, 20); sheet.setColumnView(5, 20); sheet.setColumnView(6, 20); //准备字体（第一行设置的稍大一点字体） jxl.write.WritableFont font = new jxl.write.WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); jxl.write.WritableFont font1 = new jxl.write.WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); jxl.write.WritableCellFormat f = new jxl.write.WritableCellFormat(font); jxl.write.WritableCellFormat f1 = new jxl.write.WritableCellFormat(font1); f.setAlignment(jxl.format.Alignment.CENTRE); f1.setAlignment(jxl.format.Alignment.CENTRE); for (int i = 0; i < data.size(); i++) { if(i==0){ for (int j = 0; j < data.get(i).length; j++) { sheet.addCell(new Label(j, 1, data.get(i)[j],f)); // 加入表头字段 } }else{ for (int j = 0; j < data.get(i).length; j++) { sheet.addCell(new Label(j, i+1, data.get(i)[j],f)); // 加入表头字段 } } } /* 写入数据并关闭文件 */ book.write(); book.close(); } catch (Exception e) { System.err.println("Excel导出异常："+e.toString()); e.printStackTrace(); } }}
测试类： package com.util; import javax.swing.JOptionPane;public class TEST { public static void main(String[] args) { //桌面路径：C:\Users\Administrator\Desktop String filePath="D:\\123.xls"; String pathda=JOptionPane.showInputDialog("请核对路径：",filePath); System.out.println(pathda+"pathda"); //------------------------------------------------- //调用导入Excel的方法类和方法。 String ret= Excelread2010.read(pathda); StringBuffer xsml=new StringBuffer(); System.out.println(Excelread2010.zonglist.size()+"总列数"); System.out.println(Excelread2010.zonglist.get(0).size()+"总行数"); //循环遍历所有行数据。 for(int i=0;i<Excelread2010.zonglist.size();i++){ System.out.println(); //每一行的数据 for(int j=0;j<Excelread2010.zonglist.get(i).size();j++){ System.out.print(Excelread2010.zonglist.get(i).get(j)+" "); //连接数据库并存储数据 String sql=""; } } System.out.println("xsml.toString()"+xsml.toString()); //将zonglist里面的数据存入数据库对应的表中 } //导出方法： File fil=new File("D:\\211111.xls"); ArrayList<String[]> data=new ArrayList<String[]>(); for(int j=0;j<8;j++){ String [] aa=new String [9] ; for(int i=0;i<aa.length;i++){ aa[i]=j+"uu"+i+"tt"; } data.add(aa); } ExcelUtils.ExportExcel(fil, data); System.out.println("ok"); }