package com.test.service;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.classic.Session;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.test.model.Excle;
public class CreateExcel{
public static void main(String[] args){
ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
SessionFactory sf = (SessionFactory)ctx.getBean("sessionFactory");
Session session = sf.openSession();
Transaction tx = session.beginTransaction();
Query query = session.createSQLQuery("select * from excle");
List list = query.list();//list有5个数组
new CreateExcel().createExcel(list);
// new CreateExcel().readExcel("D://test.xls");
}
/**
* 接受从数据库查询得到的集合list
* */
public void createExcel(List excelList) {
FileOutputStream fos = null;
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet页
HSSFSheet sheet = wb.createSheet("新华日报表");
HSSFRow row = sheet.createRow(0);
row.createCell((short)0).setCellValue("序号");
row.createCell((short)1).setCellValue("性别");
row.createCell((short)2).setCellValue("年龄");
row.createCell((short)3).setCellValue("经验值");
row.createCell((short)4).setCellValue("基本工资");
for(int i=0;i<excelList.size();i++){
Object obj[] = (Object[])excelList.get(i);
HSSFRow rows = sheet.createRow(i+1);
rows.createCell((short)0).setCellValue(obj[0].toString());
rows.createCell((short)1).setCellValue(obj[1].toString());
rows.createCell((short)2).setCellValue(obj[2].toString());
rows.createCell((short)3).setCellValue(obj[3].toString());
rows.createCell((short)4).setCellValue(obj[4].toString());
}
FileOutputStream fOut;
try {
fOut = new FileOutputStream("D://test.xls");
wb.write(fOut);
fOut.flush();
fOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 读取Excel文件
* */
public void readExcel(String excelpath){
try {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelpath));
//获得一张表的引用
HSSFSheet sheet = workbook.getSheet("新华日报表");
HSSFRow row = sheet.getRow(1);
HSSFCell cell = row.getCell(0);
System.out.println("左上单元是:==="+cell.getStringCellValue());
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 创建和设置字体和单元格的格式,然后再应用这些格式:
* */
public void excelStyle(){
HSSFWorkbook wb = new HSSFWorkbook();
//创建字体,设置其为红色、粗体:
HSSFFont font = wb.createFont();
font.setColor(HSSFFont.COLOR_RED);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//格式
HSSFCellStyle cellStyle= wb.createCellStyle();
cellStyle.setFont(font);
//应用格式
HSSFSheet sh = wb.createSheet("ss");
HSSFRow row = sh.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("标题 ");
}
}