package com.dayan.util;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Random;import org.apache.poi.hssf.usermodel.HSSFCell;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;import com.google.gson.Gson;/** * @Description: XlsUtil工具包 ,兼容高版本的xls * @Author: * @Version: V1.0 Copyright (C) 创建时间：2014年01月07日 */public class XlsUtils {    static Gson gson=new Gson();    public static List read(String filePath) throws Exception {        List list=null;        String fileType = filePath.substring(filePath.lastIndexOf(".") + 1,filePath.length());                InputStream stream = new FileInputStream(filePath);                Workbook wb = null;        if (fileType.equals("xls")) {            wb = new HSSFWorkbook(stream);        } else if (fileType.equals("xlsx")) {            wb = new XSSFWorkbook(stream);        } else {            System.out.println("您输入的excel格式不正确");        }        Sheet sheet1 = wb.getSheetAt(0);        for (Row row : sheet1) {            for (Cell cell : row) {                try {//                    System.out.print(cell.getStringCellValue() + "+");                    list.add(cell.getStringCellValue());                } catch (Exception e) {                    // TODO: handle exception                }                try {//                    System.out.print(cell.getNumericCellValue() + "-");                    list.add(cell.getNumericCellValue());                } catch (Exception e) {                    // TODO: handle exception                }            }        }        return list;    }  public static int write(String outPath,String sql ,String ziPathName) {    String fileType = outPath.substring(outPath.lastIndexOf(".") + 1, outPath.length());    try {    // 创建工作文档对象        Workbook wb = null;        if (fileType.equals("xls")) {          wb = new HSSFWorkbook();        } else if (fileType.equals("xlsx")) {          wb = new XSSFWorkbook();        } else {          System.out.println("您的文档格式不正确！");          return 0;        }        // 创建sheet对象        Sheet sheet1 = (Sheet) wb.createSheet("sheet1");                      Row rowChinaTitle = (Row) sheet1.createRow(1);      // 循环写入标题中文行数据       String title[]=china.split(",");      for (int i = 0; i < title.length; i++) {           Cell cell=rowChinaTitle.createCell(i);           cell.setCellValue(title[i] +i);      }                 // 循环写入行数据      try{          //调用Class.forName()方法加载驱动程序          Class.forName("com.mysql.jdbc.Driver");      }catch(ClassNotFoundException e1){          System.out.println("找不到MySQL驱动!");          e1.printStackTrace();      }            String url ="jdbc:mysql://192.168.2.112:3306/jeesite?useUnicode=true&characterEncoding=utf-8";    //JDBC的URL          //调用DriverManager对象的getConnection()方法，获得一个Connection对象      Connection conn;      try {          conn = DriverManager.getConnection(url, "root","root");          //创建一个Statement对象          Statement stmt = conn.createStatement(); //创建Statement对象          ResultSet rs = stmt.executeQuery(sql);//创建数据对象                    List list = new ArrayList();          ResultSetMetaData md = rs.getMetaData();          int columnCount = md.getColumnCount();              while (rs.next()){                   Map rowData = new HashMap();                  for (int i = 1; i <= columnCount; i++) {                      rowData.put(md.getColumnName(i), rs.getObject(i));                  }                  list.add(rowData);              }              List exl=new  ArrayList();              List exlArray=new  ArrayList();                           Row rowTitle = (Row) sheet1.createRow(0);                            String spliTitle="";              String gso=gson.toJson(list.get(0));              spliTitle=gso.substring(0, gso.indexOf("\":\""));              spliTitle=spliTitle.substring(2, spliTitle.length())+",";              String [] sotitle=gso.substring(1, gso.length()).split(",\"");              for (int j = 0; j < sotitle.length; j++) {                 String titleA=sotitle[j].substring(0 , sotitle[j].indexOf("\""));                 if(!"".equals(titleA)&&null!=titleA){                     spliTitle+=titleA+",";                 }             }                String javaBean=spliTitle.substring(0, spliTitle.length()-1);                              // 循环写入标题行数据                 String bean[]=javaBean.split(",");                  for (int i = 0; i < bean.length; i++) {                        Cell cell=rowTitle.createCell(i);                        cell.setCellValue(bean[i]);                   }                             for (int i = 0; i < list.size(); i++) {                 gso=gson.toJson(list.get(i));                String[] gs=gso.split("\":\"");                for (int j = 0; j < gs.length; j++) {                   String sub=gs[j].substring(0 , gs[j].lastIndexOf("\"")).replaceAll("\",", "");                    if(!sub.equals("{")){                       exl.add(sub);                     }                 }                exlArray.add(exl);                exl=new  ArrayList();             }                          for (int i = 0; i < exlArray.size(); i++) {                 List arry=(List) exlArray.get(i);                 Row row = (Row) sheet1.createRow(i+1);                 for (int j = 0; j < arry.size(); j++) {                     Cell cell = row.createCell(j);                    cell.setCellValue(arry.get(j)+"");               }             }          stmt.close();          conn.close();      } catch (SQLException e){          e.printStackTrace();      }              // 创建文件流          OutputStream stream;          stream = new FileOutputStream(outPath);            // 写入数据            wb.write(stream);            // 关闭文件流            stream.close();            ZipCompressor zip=new ZipCompressor(ziPathName);             zip.compressExe(outPath);                      //将缓冲文件夹中的文件删除           /* File file = new File(outPath);            if(file.exists()){             boolean d = file.delete();             if(d){                System.out.print("删除成功！");              }else{                 System.out.print("删除失败！");               }            }  */       } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();            return 1;        }        return 0;  }    /**         * 获取单元格数据内容为字符串类型的数据         * @param cell Excel单元格         * @return String 单元格数据内容         */         private String getStringCellValue(HSSFCell cell) {              String strCell = "";              if(StringUtils.isBlank(cell.getCellType()+"")){            return null;        }        switch (cell.getCellType()) {              case HSSFCell.CELL_TYPE_STRING:                  strCell = cell.getStringCellValue();                  break;              case HSSFCell.CELL_TYPE_NUMERIC:                  strCell = String.valueOf(cell.getNumericCellValue());                  break;              case HSSFCell.CELL_TYPE_BOOLEAN:                  strCell = String.valueOf(cell.getBooleanCellValue());                  break;              case HSSFCell.CELL_TYPE_BLANK:                  strCell = "";                  break;              default:                  strCell = "";                  break;              }              if (strCell.equals("") || strCell == null) {                  return "";              }              if (cell == null) {                  return "";              }              return strCell;          }        //例子  public static void main(String[] args) throws Exception {     try {         Random ne=new Random();      int res=XlsUtil.write("D:" + File.separator + "xx"+ne.nextInt(9)+".xlsx" , "SELECT * FROM cms_site","D:\\xx" +ne.nextInt(2)+".zip");      if(res==0){          System.out.println("SUCCESSED");      }else{          System.out.println("FAILED");      }        } catch (Exception e) {          e.printStackTrace();        }  /*      try {        List lrow =XlsUtil.read("D:" + File.separator + "xx.xls");        for (int i = 0; i < lrow.size(); i++) {            System.out.println("--lrow--" +lrow.get(i));        }        } catch (IOException e) {          e.printStackTrace();        }*/  }}