java读取excel.doc

java读取excel0人收藏此文章,我要收藏发表于5个月前(2012-03-05 13:47) , 已有53次阅读 共0个评论 xls2csv可以将xls转成csv格式，利用windows批处理命令for即可实现批量转换。命令如下： echo off rem 放在xls目录外面，将在xls目录下生成对应的csv文件 rem xls2cvs工具所在的目录 set XLS2CSV_HOME=E:setupxls2csv for /r %i in (*.xls) do %XLS2CSV_HOME%xls2csv.exe %i gbk pause010-04-08java读取数据库并导出到Excel博客分类： JavaExcelJavaSQLMicrosoftJDBCimport java.io.File; import jxl.*; import jxl.write.*; import jxl.write.biff.RowsExceededException; import java.sql.*; import java.util.*; public class DBtoExcel /* * 导出Excel表 * param rs 数据库结果集 * param filePath 要保存的路径，文件名为 fileName.xls * param sheetName 工作簿名称 工作簿名称，本方法目前只支持导出一个Excel工作簿 * param columnName 列名，类型为Vector */ public void WriteExcel(ResultSet rs, String filePath, String sheetName, Vector columnName) WritableWorkbook workbook = null; WritableSheet sheet = null; int rowNum = 1; / 从第一行开始写入 try workbook = Workbook.createWorkbook(new File(filePath); / 创建Excel文件 sheet = workbook.createSheet(sheetName, 0); / 创建名为 sheetName 的工作簿 this.writeCol(sheet, columnName, 0); / 首先将列名写入 / 将结果集写入 while(rs.next() Vector col = new Vector(); / 用以保存一行数据 for(int i = 1; i = columnName.size(); i+) / 将一行内容保存在col中 col.add(rs.getString(i); / 写入Excel this.writeCol(sheet, col, rowNum+); catch(Exception e) e.printStackTrace(); finally try / 关闭 workbook.write(); workbook.close(); rs.close(); catch(Exception e) e.printStackTrace(); /* * 将数组写入工作簿 * param sheet 要写入的工作簿 * param col 要写入的数据数组 * param rowNum 要写入哪一行 * throws WriteException * throws RowsExceededException */ private void writeCol(WritableSheet sheet, Vector col, int rowNum) throws RowsExceededException, WriteException int size = col.size(); / 获取集合大小 for(int i = 0; i size; i+) / 写入每一列 Label label = new Label(i, rowNum, col.get(i); sheet.addCell(label); import java.io.File;import jxl.*;import jxl.write.*;import jxl.write.biff.RowsExceededException;import java.sql.*;import java.util.*;public class DBtoExcel /* * 导出Excel表 * param rs 数据库结果集 * param filePath 要保存的路径，文件名为 fileName.xls * param sheetName 工作簿名称 工作簿名称，本方法目前只支持导出一个Excel工作簿 * param columnName 列名，类型为Vector */public void WriteExcel(ResultSet rs, String filePath, String sheetName, Vector columnName) WritableWorkbook workbook = null; WritableSheet sheet = null; int rowNum = 1; / 从第一行开始写入 try workbook = Workbook.createWorkbook(new File(filePath); / 创建Excel文件 sheet = workbook.createSheet(sheetName, 0); / 创建名为 sheetName 的工作簿 this.writeCol(sheet, columnName, 0); / 首先将列名写入 / 将结果集写入 while(rs.next() Vector col = new Vector(); / 用以保存一行数据 for(int i = 1; i = columnName.size(); i+) / 将一行内容保存在col中 col.add(rs.getString(i); / 写入Excel this.writeCol(sheet, col, rowNum+); catch(Exception e) e.printStackTrace(); finally try / 关闭 workbook.write(); workbook.close(); rs.close(); catch(Exception e) e.printStackTrace(); /* * 将数组写入工作簿 * param sheet 要写入的工作簿 * param col 要写入的数据数组 * param rowNum 要写入哪一行 * throws WriteException * throws RowsExceededException */private void writeCol(WritableSheet sheet, Vector col, int rowNum) throws RowsExceededException, WriteException int size = col.size(); / 获取集合大小 for(int i = 0; i size; i+) / 写入每一列 Label label = new Label(i, rowNum, col.get(i); sheet.addCell(label); 首先我用到的是jxl 的东西，所以要先下个Jar包，/source/487467 导入Jar包之后，可以用这个.读取excelpackage com;import java.io.BufferedInputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Arrays;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class ExcelOperate public static void main(String args) throws Exception File file = new File(C:/mm.xls); String result = getData(file, 1); int rowLength = result.length; for(int i=0;irowLength;i+) for(int j=0;jresulti.length;j+) System.out.print(resultij+tt); System.out.println(); /* * 操作数据结果和行 * throws FileNotFoundException * throws IOException */ public void oper() throws FileNotFoundException, IOException File file = new File(C:/mm.xls); String result = getData(file, 1); int rowLength = result.length; /* * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行 * param file 读取数据的源Excel * param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1 * return 读出的Excel中数据的内容 * throws FileNotFoundException * throws IOException */ public static String getData(File file, int ignoreRows) throws FileNotFoundException, IOException List result = new ArrayList(); int rowSize = 0; BufferedInputStream in = new BufferedInputStream(new FileInputStream( file); / 打开HSSFWorkbook POIFSFileSystem fs = new POIFSFileSystem(in); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFCell cell = null; for (int sheetIndex = 0; sheetIndex wb.getNumberOfSheets(); sheetIndex+) HSSFSheet st = wb.getSheetAt(sheetIndex); / 第一行为标题，不取 for (int rowIndex = ignoreRows; rowIndex rowSize) rowSize = tempRowSize; String values = new StringrowSize; Arrays.fill(values, ); boolean hasValue = false; for (short columnIndex = 0; columnIndex = row.getLastCellNum(); columnIndex+) String value = ; cell = row.getCell(columnIndex); if (cell != null) / 注意：一定要设成这个，否则可能会出现乱码 cell.setEncoding(HSSFCell.ENCODING_UTF_16); switch (cell.getCellType() case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell) Date date = cell.getDateCellValue(); if (date != null) value = new SimpleDateFormat(yyyy-MM-dd) .format(date); else value = ; else value = new DecimalFormat(0).format(cell .getNumericCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: / 导入时