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=http://www.mamicode.com/new ArrayList();            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()+"\t");                        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()+"\t");                        }else{                            domian.add(eva.getNumberValue());                            sbu.append(eva.getNumberValue()+"\t");                        }                        break;                    case Cell.CELL_TYPE_STRING:                        domian.add(eva.getStringValue());                        sbu.append(eva.getStringValue()+"\t");                        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("\r\n");            }        }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()));        }    }}