Java操作Excel

该程序所需要用到的jar包为commons-lang-2.6.jar和poi-3.8.jar

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.text.NumberFormat;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class Excel {//读取excel表格中的信息public List loadExcel(String filePath) {List list = new ArrayList();//创建一个excel表对象HSSFWorkbook wb = null;//用来读取"本地"的excel文件InputStream is = null;try {is = new FileInputStream(filePath);//将输入流传给 excel对象wb = new HSSFWorkbook(is);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}String str = null;//创建sheetHSSFSheet sheet = wb.getSheet("Sheet1");//获得sheet1中的所有HSSFRow对象Iterator it = sheet.rowIterator();while (it.hasNext()) {//如需要便利行中的列可调用//temp.getFirstCellNum();//temp.getLastCellNum();当做条件进行循环HSSFRow temp = (HSSFRow) it.next();try {switch(temp.getCell((short) 0).getCellType()){case  HSSFCell.CELL_TYPE_STRING://字符串类型str = temp.getCell((short)0).getStringCellValue();break;case HSSFCell.CELL_TYPE_NUMERIC://数值类型NumberFormat nf = NumberFormat.getInstance();nf.setGroupingUsed(false);//true时的格式：1,234,567,890double acno=temp.getCell((short)0).getNumericCellValue();//将科学技术法的值转换为计算之前的值str = nf.format(acno);break;case HSSFCell.CELL_TYPE_FORMULA://公式str = String.valueOf(temp.getCell((short)0).getNumericCellValue());break;case HSSFCell.CELL_TYPE_BLANK:str = "";break;case HSSFCell.CELL_TYPE_BOOLEAN:break;default :break;}} catch (Exception e) { continue;}list.add(str);}try {//关闭输入流is.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}return list;}public void write() {HSSFWorkbook wb = new HSSFWorkbook(); //相当于Excel整个文件FileOutputStream fos = null;try {//创建一个文件fos = new FileOutputStream("D:/data.xls");} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}HSSFSheet sheet = wb.createSheet("sheet1"); //生成Excel中的sheetHSSFRow row = sheet.createRow(0); //创建第一行HSSFCell cell = row.createCell((short) 0); //创建第一个单元格cell.setEncoding(HSSFCell.ENCODING_UTF_16); //设定单元格的字符编码cell.setCellValue("编号"); //设定单元格的名字cell = row.createCell((short) 1); //创建第二个单元格cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("姓名");cell = row.createCell((short) 2);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("年龄");/**至此为止，表头部分就定义好了**/for (int i = 0; i < 50; ++i) {row = sheet.createRow(i + 1); //创建行cell = row.createCell((short) 0);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(i + 1); //设定序号cell = row.createCell((short) 1);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("a");cell = row.createCell((short) 2);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("b");}try {//将这个文件交给HSSFWorkbook类 由它负责写入wb.write(fos);//关闭输出流fos.close();} catch (IOException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}}}

导入excel例子

public class ExcelUtils {@SuppressWarnings("all")public static List<MonitorObjectVO> loadExcel(File file) {List<MonitorObjectVO> excelGroups = null;String errorMsg = "";if (file.exists()) {try {Workbook workBook = null;InputStream is = new FileInputStream(file);try {if (".xls".equals(".xls")) { // 97-03workBook = new HSSFWorkbook(is);} else if (".xlsx".equals(".xls")) { // 2007workBook = new XSSFWorkbook(is);} else {System.out.println("不支持的文件类型！");return null;}} catch (Exception e) {System.out.println("解析xls文件出错！");e.printStackTrace();} finally {try {is.close();} catch (Exception e2) {}}int sheets = null != workBook ? workBook.getNumberOfSheets(): 0;excelGroups = new ArrayList<MonitorObjectVO>();Sheet sheet = workBook.getSheetAt(0); // 读取第一个sheetint rows = sheet.getPhysicalNumberOfRows(); // 获得行数if (rows > 1) { // 第一行默认为标题// sheet.getMargin(HSSFSheet.TopMargin);for (int j = 1; j < rows; j++) {Row row = sheet.getRow(j);MonitorObjectVO groupObj = new MonitorObjectVO();int cells = row.getLastCellNum();// 获得列数if (cells > 0) {for (int k = 0; k < cells; k++) {Cell cell = row.getCell(k);// 全部置成String类型的单元格cell.setCellType(Cell.CELL_TYPE_STRING);if (k <= 50) {groupObj.setGroup_name(cell.getStringCellValue());System.out.println(cell.getStringCellValue());} else {break;}}} else {errorMsg = "EXCEL没有数据，请确定。";}excelGroups.add(groupObj);}} else {errorMsg = "EXCEL没有数据，请确定。";}} catch (Exception ex) {ex.printStackTrace();}} else {errorMsg = "文件不存在。";}if (errorMsg.length() > 0) {System.out.println("错误消息：" + errorMsg);}return excelGroups;}}