poi读取、通过poi导出数据库的记录到excl表

package com.nt.test;

 

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.util.Iterator;

 

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;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

 

public class TestPOI {

//    public static void main(String[] args) {

//        File file=new File("D://nt/question/1/木工.xls");

//        try {

//            FileInputStream fint=new FileInputStream(file);

//            POIFSFileSystem poifsFileSystem=new POIFSFileSystem(fint);

//            HSSFWorkbook workbook=new HSSFWorkbook(poifsFileSystem);

//            HSSFSheet sheet=workbook.getSheetAt(0);

//            

//            

//            Iterator rows=sheet.rowIterator();

//            //得到总行数

//            int rowtotalnumber=sheet.getLastRowNum();

//            System.out.println("rowtotal=========="+rowtotalnumber);

//            while (rows.hasNext()) {

//                HSSFRow row=(HSSFRow) rows.next();

//                int rownumber=row.getRowNum();

//                //当前行数

//                System.out.println("rownumber------"+rownumber);

//                

//                //当前行的列数

//                int lie=row.getPhysicalNumberOfCells();

//                System.out.println("lie============"+lie);

//                

//                

//                Iterator cells=row.cellIterator();

//                

//                while(cells.hasNext()){

//                    HSSFCell cell=(HSSFCell) cells.next();

//                    

//                    //列好 从0开始

//                    int cellnumber=cell.getCellNum();

//                    System.out.println("cellnumber----"+cellnumber);

//                    System.out.println(getCellStringValue(cell));

//                }

//            }

//        } catch (FileNotFoundException e) {

//            e.printStackTrace();

//        } catch (IOException e) {

//            e.printStackTrace();

//        }

//    }

    

      

 

public static String getCellStringValue(HSSFCell cell) {

String cellValue = "";

switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING://字符串类型

cellValue = cell.getStringCellValue();

if(cellValue.trim().equals("")||cellValue.trim().length()<=0)

cellValue=" ";

break;

case HSSFCell.CELL_TYPE_NUMERIC: //数值类型

cellValue = String.valueOf(cell.getNumericCellValue());

break;

case HSSFCell.CELL_TYPE_FORMULA: //公式

cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

cellValue = String.valueOf(cell.getNumericCellValue());

break;

case HSSFCell.CELL_TYPE_BLANK:

cellValue=" ";

break;

case HSSFCell.CELL_TYPE_BOOLEAN:

break;

case HSSFCell.CELL_TYPE_ERROR:

break;

default:

break;

}

return cellValue;

}

 

}

 

 

 

 

 

 

 

通过poi导出数据库的记录到excl表

 

 

package com.test.daotest;

 

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.Iterator;

import java.util.List;

 

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.hibernate.Session;

import org.hibernate.Transaction;

 

import com.test.model.Question;

import com.test.until.HibernateSessionFactory;

 

public class ExportQuestion {

    public static void main(String[] args) {

        int id=14;

         try {

            HSSFWorkbook wb=new HSSFWorkbook();

             FileOutputStream fileout = new FileOutputStream("test"+id+".xls");

             wb.write(fileout);

            

             HSSFSheet sheet=wb.createSheet("new sheet");

             //通过Hibernate来查询addressbook_table表中的数据,将其存储在List中

                 Session s=HibernateSessionFactory.getSession();

             Transaction tx = s.beginTransaction();

             org.hibernate.Query query= s.createQuery("from Question q where q.majorId="+id);

             List list = query.list();

             tx.commit();

             int k =0;

 

             //创建表格,创建表格行和单元格,将数据库中表的字段存储在单元格中.

             for(Iterator it=list.iterator();it.hasNext();){

             Question q =(Question)it.next();

             HSSFRow row=sheet.createRow((short)k);

             row.createCell((short)0).setCellValue(1);

             row.createCell((short)1).setCellValue(q.getQuestion());

             row.createCell((short)2).setCellValue(q.getOptionA());

             row.createCell((short)3).setCellValue(q.getOptionB());

             row.createCell((short)4).setCellValue(q.getOptionC());

             row.createCell((short)5).setCellValue(q.getOptionD());

             row.createCell((short)6).setCellValue(q.getAnswer());

             row.createCell((short)7).setCellValue(q.getMajorId());

             row.createCell((short)8).setCellValue(0);

             row.createCell((short)9).setCellValue(0);

             k++;

             }

             FileOutputStream fileout1 = new FileOutputStream("test"+id+".xls");

             wb.write(fileout1);

            

             fileout1.close();

 

 

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

}