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();

????????}

????}

}

?

?

?

?