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