java生成excel报表文件示例

package jdbc;import java.io.FileInputStream;import java.sql.Connection;import java.util.Properties;import org.apache.commons.dbcp.BasicDataSource;public class BaseDAO {  private static BasicDataSource ds;  static{                                             try {                                               //1.读取配置文件conf.properties,采用java.util.Properties来读取                 Properties p=new Properties();                                 //2.通过文件流读取并解析配置文件内容,本地数据库用的mysql,所以把配置文件mysql的配置放开,其他数据库配置注释                              p.load(new FileInputStream("src/jdbc.properties"));                        String driverName=p.getProperty("jdbc.driverClassName");//获取驱动名称                  String url=p.getProperty("jdbc.url");//获取数据库的url                      String user=p.getProperty("jdbc.username");//用户名                           String password=p.getProperty("jdbc.password");//密码                        int maxActive=Integer.parseInt(p.getProperty("jdbc.maxActive"));//获取最大连接数           int maxWait=Integer.parseInt(p.getProperty("jdbc.maxWait"));//获取最大等待时间            //3.创建一个连接池                                       ds=new BasicDataSource();                                    ds.setDriverClassName(driverName);//设置驱动名称                        ds.setUrl(url);//设置数据库地址                                 ds.setUsername(user);//设置用户名                                ds.setPassword(password);//设置密码                               ds.setMaxActive(maxActive);//设置最大连接数                           ds.setMaxWait(maxWait);//设置最大等待时间                                                                    } catch (Exception e) {                                      e.printStackTrace();                                    }                                             }    public static Connection getConnection() throws Exception {    try {      return ds.getConnection();    } catch (Exception e) {      System.out.println("连接数据库异常");      throw e;    }  }    public static void close(Connection conn){                           if(conn!=null){                                          try {                                                conn.close();                                         } catch (Exception e) {                                       e.printStackTrace();                                     }                                              }                                             } }

package entity;public class Test {  private String a;  private String b;  private String c;    private String d;  private String e;  private String f;  private String g;  private String h;  private String i;  private String j;  public String getA() {    return a;  }  public void setA(String a) {    this.a = a;  }  public String getB() {    return b;  }  public void setB(String b) {    this.b = b;  }  public String getC() {    return c;  }  public void setC(String c) {    this.c = c;  }  public String getD() {    return d;  }  public void setD(String d) {    this.d = d;  }  public String getE() {    return e;  }  public void setE(String e) {    this.e = e;  }  public String getF() {    return f;  }  public void setF(String f) {    this.f = f;  }  public String getG() {    return g;  }  public void setG(String g) {    this.g = g;  }  public String getH() {    return h;  }  public void setH(String h) {    this.h = h;  }  public String getI() {    return i;  }  public void setI(String i) {    this.i = i;  }  public String getJ() {    return j;  }  public void setJ(String j) {    this.j = j;  }  }

package readExcel;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;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.CellStyle;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 ReadExcel {  /**   * @param args   * @throws IOException    */    public List<List<String>> readExcel(File file) throws IOException{    List<List<String>> list=new ArrayList<List<String>>();    if(!file.exists()){      System.out.println("文件不存在");    }else{      InputStream fis=new FileInputStream(file);        list=parseExcel(file,fis);    }    return list;      }    public List<List<String>> parseExcel(File file,InputStream fis) throws IOException{    Workbook workbook=null;    List<List<String>> list=new ArrayList<List<String>>();    if(file.toString().endsWith("xls")){      workbook=new HSSFWorkbook(fis);    }else if(file.toString().endsWith("xlsx")){      workbook=new XSSFWorkbook(fis);    }else{      System.out.println("文件不是excel文档类型 ，此处无法读取");    }    for(int i=0;i<workbook.getNumberOfSheets();i++){      Sheet sheet=workbook.getSheetAt(i);       if(sheet!=null){                int lastRow=sheet.getLastRowNum();        //获取表格中的每一行        for(int j=0;j<=lastRow;j++){          Row row=sheet.getRow(j);          short firstCellNum=row.getFirstCellNum();          short lastCellNum=row.getLastCellNum();            List<String> rowsList=new ArrayList<String>();          if(firstCellNum!=lastCellNum){                        //获取每一行中的每一列            for(int k=firstCellNum;k<lastCellNum;k++){              Cell cell=row.getCell(k);              if(cell==null){                rowsList.add("");              }else{                rowsList.add(chanegType(cell));              }            }          }else{            System.out.println("该表格只有一列");          }          list.add(rowsList);        }              }    }    return list;    }    public String chanegType(Cell cell){    String result = new String();     switch (cell.getCellType()) { //获取单元格的类型    case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型       if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ //如果是数值类型        short format = cell.getCellStyle().getDataFormat(); //获取这个单元的类型对应的数值        SimpleDateFormat sdf = null;         if(format == 14 || format == 31 || format == 57 || format == 58){ //如果数值为14,31,57,58其中的一种           //对应的日期格式为 2016-03-01这种形式,          sdf = new SimpleDateFormat("yyyy-MM-dd");           double value = cell.getNumericCellValue();           Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);           result = sdf.format(date);//得到yyyy-MM-dd这种格式日期        }else if (format == 20 || format == 32) {           //时间           sdf = new SimpleDateFormat("HH:mm");           double value = cell.getNumericCellValue();           Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);           result = sdf.format(date);//得到HH:mm        } else {          double value = cell.getNumericCellValue();           CellStyle style = cell.getCellStyle();           DecimalFormat dataformat = new DecimalFormat();           String temp = style.getDataFormatString();           // 单元格设置成常规           if (temp.equals("General")) {             dataformat.applyPattern("#");           }           result = dataformat.format(value); //得到单元格数值        }      }       break;     case HSSFCell.CELL_TYPE_STRING:// String类型       result = cell.getRichStringCellValue().toString();       break;     case HSSFCell.CELL_TYPE_BLANK:       result = "";     default:       result = "";       break;     }     return result;      }  }

package importdata;import java.io.File;import java.sql.Connection;import java.sql.PreparedStatement;import java.util.ArrayList;import java.util.List;import entity.Test;import readExcel.ReadExcel;import jdbc.BaseDAO;public class inportData {        public static void main(String[] args) throws Exception {    // TODO Auto-generated method stub    List<List<String>> list = new ArrayList<List<String>>();    ReadExcel readExcel=new ReadExcel();    File file=new File("d:/test.xlsx");    list=readExcel.readExcel(file);        Test test=new Test();    Connection conn=BaseDAO.getConnection();    PreparedStatement ps=null;    int i=1;    for(List<String> rowlist:list){      if(rowlist!=null){        test.setA(rowlist.get(0).toString());        test.setB(rowlist.get(1).toString());        test.setC(rowlist.get(2).toString());        test.setD(rowlist.get(3).toString());              test.setE(rowlist.get(4).toString());        test.setF(rowlist.get(5).toString());        test.setG(rowlist.get(6).toString());        test.setH(rowlist.get(7).toString());        test.setI(rowlist.get(8).toString());        test.setJ(rowlist.get(9).toString());        String sql="insert into TEST(A,B,C,D,E,F,G,H,I,J) values(?,?,?,?,?,?,?,?,?,?)";        ps=conn.prepareStatement(sql);        ps.setString(1,test.getA());        ps.setString(2,test.getB());        ps.setString(3,test.getC());        ps.setString(4,test.getD());        ps.setString(5,test.getE());        ps.setString(6,test.getF());        ps.setString(7,test.getG());        ps.setString(8,test.getH());        ps.setString(9,test.getI());        ps.setString(10,test.getJ());        int n=ps.executeUpdate();        if(n!=1){          System.out.println("数据插入数据库失败");        }        System.out.println("第"+i+"条数据插入成功");        System.out.println();        i++;      }    }          }}

package export;import java.io.FileOutputStream;import java.io.IOException;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;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 entity.Test;import jdbc.BaseDAO;public class Export {      public static void createExcel(List<Test> list){    FileOutputStream fos=null;    Workbook workbook=new XSSFWorkbook();      Sheet sheet=workbook.createSheet("测试文件");    String[]    Row row=sheet.createRow((short)0);    int i=0;    for(String s:title){      Cell cell=row.createCell(i);      cell.setCellValue(s);      i++;    }    int j=1;    for(Test t:list){      //创建第二行      Row rowData=sheet.createRow((short)j);      //第一列数据      Cell cell0=rowData.createCell((short)0);      cell0.setCellValue(t.getA());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第二列数据      Cell cell1=rowData.createCell((short)1);      cell1.setCellValue(t.getB());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第三列数据      Cell cell2=rowData.createCell((short)2);      cell2.setCellValue(t.getC());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第四列数据      Cell cell3=rowData.createCell((short)3);      cell3.setCellValue(t.getD());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第五列数据      Cell cell4=rowData.createCell((short)4);      cell4.setCellValue(t.getE());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第六列数据      Cell cell5=rowData.createCell((short)5);      cell5.setCellValue(t.getF());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第七列数据      Cell cell6=rowData.createCell((short)6);      cell6.setCellValue(t.getG());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第八列数据      Cell cell7=rowData.createCell((short)7);      cell7.setCellValue(t.getH());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第九列数据      Cell cell8=rowData.createCell((short)8);      cell8.setCellValue(t.getI());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      //第十列数据      Cell cell9=rowData.createCell((short)9);      cell9.setCellValue(t.getJ());      //设置单元格的宽度      sheet.setColumnWidth((short)0, (short)10000);      j++;    }    try {      //导出数据库文件保存路径      fos=new FileOutputStream("D:/export.xlsx");      /*if(fos.toString().endsWith("xlsx")){        workbook=new XSSFWorkbook();      }else if(fos.toString().endsWith("xls")){        workbook=new HSSFWorkbook();      }*/      //将工作簿写入文件      workbook.write(fos);        System.out.println("导出文件成功");    } catch (IOException e) {      // TODO Auto-generated catch block      e.printStackTrace();      System.out.println("导出文件失败");    }  }  public static void main(String[] args) throws Exception {    //连接数据库    Connection conn=BaseDAO.getConnection();    PreparedStatement ps=null;        String sql="select * from TEST";    //执行sql语句    ps=conn.prepareStatement(sql);    //查询数据库之后得到的结果    ResultSet rs=ps.executeQuery();    List<Test> list=new ArrayList<Test>();    //遍历查询结果    while(rs.next()){      Test test=new Test();      test.setA(rs.getString("A"));      test.setB(rs.getString("B"));      test.setC(rs.getString("C"));      test.setD(rs.getString("D"));      test.setE(rs.getString("E"));      test.setF(rs.getString("F"));      test.setG(rs.getString("G"));      test.setH(rs.getString("H"));      test.setI(rs.getString("I"));      test.setJ(rs.getString("J"));      list.add(test);          }    createExcel(list);  }  }