poi导入导出

用到的jar包

代码

package com.exceloperation;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import javax.rmi.CORBA.Tie;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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 ExcelOperation {
    /**
     * 存所有的excel数据
     */
    static List<List<Object>>all=new ArrayList<List<Object>>();
    static Connection conn =null;
    public static void main(String[] args) {
         importExcel("D://数据模板1.xlsx");
//         System.out.println(conn);
//         try{
//                Statement createStatement = conn.createStatement();
//                for (List<Object> list : all) {
//                    String sql="insert into excelTable(tradType,tradTime,tradWater,institution,phone,name,"+
//                    "psamId,tradNo,tradNoTwo,tradMoney,tradPoundage,tradStatus,remark)"+"values("+
//                    "'"+list.get(1)+"','"+list.get(2)+"','"+list.get(3)+"','"+list.get(4)+"'," +
//                    "'"+list.get(5)+"','"+list.get(6)+"','"+list.get(7)+"','"+list.get(8)+"',"+
//                    "'"+list.get(9)+"','"+list.get(10)+"','"+list.get(11)+"','"+list.get(12)+"','"+list.get(13)+"')";
//                    System.out.println(sql);
//                    createStatement.execute(sql);
//                }
//                conn.close();
//            }catch (Exception e) {
//                e.printStackTrace();
//            }
            //导出
            outExcel("D://123456.xls");
    }
    
    static {
        try {
            String url = "jdbc:sqlserver://192.168.1.177:1433;DatabaseName=excelDataBase"; 
            String uername = "sa";                                                         
            String password = "123456";                                                    
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(url, uername, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
    *导出excel
    */
    private static void outExcel(String path){
        try {
            //List<String>data=new ArrayList<String>();
            String[] title={"编号","交易类型","交易时间","交易流水","隶属机构","手机号","姓名","PSAM卡号",
                    "交易卡号","第二次交易账号","交易金额","交易手续费","交易状态","备注"};
             HSSFWorkbook wb=new HSSFWorkbook();
             HSSFSheet sheet = wb.createSheet("sheet1");
             for(int sheetWidth=0;sheetWidth<title.length;sheetWidth++){
                 sheet.setColumnWidth(sheetWidth, 3700);
             }
             HSSFRow row = sheet.createRow(0);
             HSSFCellStyle style=wb.createCellStyle();
             style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
             style.setWrapText(true);
             HSSFCell cell=row.createCell(0);
             //设置title
             for(int i=0;i<title.length;i++){
                 cell.setCellValue(title[i]);
                 cell.setCellStyle(style);
                 cell=row.createCell((short)(i+1));
             }
             //写入数据
             for(int r=0;r<all.size();r++){
                 List<Object> list = all.get(r);
                     row=sheet.createRow(r+1);
                     for(int c=0;c<title.length;c++){
                         row.createCell((short)c).setCellValue(list.get(c)+"");
                     }
             }
             FileOutputStream out=new FileOutputStream(path);
             wb.write(out);
             out.close();
           
        } catch (Exception e) {
            e.printStackTrace();
        }  
    }
    /**
     * 获取excel的数据
     */
    private static void importExcel(String path){
        StringBuilder sbu=new StringBuilder();
        String xls=".xls";
        String xlsx=".xlsx";
        String ext=path.substring(path.lastIndexOf("."), path.length()).toLowerCase();
        System.out.println(ext);
        Workbook wl=null;
        if(!new File(path).exists())
        {
            throw new RuntimeException("文件不存在!");
        }
        try{
            if(ext.equals(xls)){
                wl=new HSSFWorkbook(new FileInputStream(new File(path)));
            }
            if(ext.equals(xlsx)){
                wl=new XSSFWorkbook(new FileInputStream(new File(path)));
            }
            // 解析公式结果  
            FormulaEvaluator evaluator = wl.getCreationHelper().createFormulaEvaluator();  
            Sheet sheetAt = wl.getSheetAt(0);
            int firstRowNum = sheetAt.getFirstRowNum();
            int lastRowNum = sheetAt.getLastRowNum();
            for(int i=firstRowNum;i<=lastRowNum;i++){
                List<Object>domian=new ArrayList<Object>();
                Row row = sheetAt.getRow(i+2);
                short firstColumnNum = row.getFirstCellNum();
                short lastColumnNum = row.getLastCellNum();
                for(short j=firstColumnNum;j<lastColumnNum;j++){
                     Cell cell = row.getCell(new Integer(j));
                     CellValue eva = evaluator.evaluate(cell);
                     if(null==eva){
                         continue;
                     }
                    // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了  
                    // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
                    switch (eva.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                         domian.add(eva.getBooleanValue());
                         sbu.append(eva.getBooleanValue()+"	");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)){
                            //new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue())
                             domian.add(cell.getDateCellValue());
                            sbu.append(cell.getDateCellValue()+"	");
                        }else{
                            domian.add(eva.getNumberValue());
                            sbu.append(eva.getNumberValue()+"	");
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        domian.add(eva.getStringValue());
                        sbu.append(eva.getStringValue()+"	");
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                    default:
                        break;
                    }
                }
                all.add(domian);
                sbu.append("
");
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        
        System.out.println(sbu.toString());
        System.out.println("----------------");
        for (List<Object> list : all) {
            System.out.println(Arrays.asList(list.toArray()));
        }
    }
}