代码如下:
package com.poi.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
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.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import com.poi.model.CkRdrecord10_001;
import com.poi.util.DBConnectionUtil;
public class QueryCkRdrecord10 {
public static List<CkRdrecord10_001> selectCkRdrecord10()throws Exception{
Connection conn = null;
ResultSet rs = null;
Statement st = null;
String sql = "select * from ck_rdrecord10";
conn = DBConnectionUtil.getConnection();
List<CkRdrecord10_001> list = new ArrayList<CkRdrecord10_001>();
try {
st = conn.createStatement();
rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int type = rsmd.getColumnType(1);
String columnName = rsmd.getColumnName(1);
System.out.println(columnName);
while(rs.next()){
CkRdrecord10_001 ck10 = new CkRdrecord10_001();
ck10.setBrdflag(rs.getInt("brdflag"));
ck10.setCvouchtype(rs.getString("cvouchtype"));
ck10.setCbustype(rs.getString("cbustype"));
ck10.setCsource(rs.getInt("csource"));
ck10.setCbuscode(rs.getString("cbuscode"));
ck10.setCwhcode(rs.getString("cwhcode"));
ck10.setDdate(rs.getInt("ddate"));
ck10.setCcode(rs.getString("ccode"));
ck10.setCrdcode(rs.getString("crdcode"));
ck10.setCdepcode(rs.getString("cdepcode"));
list.add(ck10);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBConnectionUtil.closeDB(rs, st, conn);
}
return list;
}
public void createExcel()throws Exception{
FileInputStream fs=new FileInputStream("d:\\java\\001.xls"); //获取d://test.xls
POIFSFileSystem ps=new POIFSFileSystem(fs); //使用POI提供的方法得到excel的信息
HSSFWorkbook workBook=new HSSFWorkbook(ps);
HSSFSheet sheet=workBook.getSheetAt(0); //获取到工作表,因为一个excel可能有多个工作表
HSSFRow row=sheet.getRow(0); //获取第一行(excel中的行默认从0开始,所以这就是为什么,一个excel必须有字段列头),即,字段列头,便于赋值
row=sheet.createRow(2); //在现有行号后追加数据
//row=sheet.createRow((short)(sheet.getLastRowNum()-4));
// HSSFCellStyle style = workBook.createCellStyle();
// style.setBorderBottom(BorderStyle.THIN);
// style.setBorderLeft(BorderStyle.THIN);
// style.setBorderRight(BorderStyle.THIN);
// style.setBorderTop(BorderStyle.THIN);
// sheet.shiftRows(2, sheet.getLastRowNum(), 1,true,false);
// row = sheet.createRow(2);
List<CkRdrecord10_001> list = QueryCkRdrecord10.selectCkRdrecord10();
if(list != null && list.size() > 0){
for(int i = 0; i < list.size(); i++){
CkRdrecord10_001 ck10 = list.get(i);
HSSFRow dataRow = sheet.createRow(i+2);
HSSFCell dataCell[] = new HSSFCell[10];
for(int j = 0; j < 10; j++){
dataCell[j] = dataRow.createCell(j+1);
HSSFFont font = workBook.createFont();
font.setFontName("宋体");
font.setFontHeight((short) 300);
HSSFCellStyle style = workBook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
dataCell[j].setCellStyle(style);
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);
}
dataCell[0].setCellValue(ck10.getBrdflag());
dataCell[1].setCellValue(ck10.getCvouchtype());
dataCell[2].setCellValue(ck10.getCbustype());
dataCell[3].setCellValue(ck10.getCsource());
dataCell[4].setCellValue(ck10.getCbuscode());
dataCell[5].setCellValue(ck10.getCwhcode());
dataCell[6].setCellValue(ck10.getDdate());
dataCell[7].setCellValue(ck10.getCcode());
dataCell[8].setCellValue(ck10.getCrdcode());
dataCell[9].setCellValue(ck10.getCdepcode());
//workBook.getSheet("sheet").shiftRows(sheet.getLastRowNum()-1, sheet.getLastRowNum(), 0);
//sheet.shiftRows(sheet.getLastRowNum()-1, sheet.getLastRowNum(),-5);
FileOutputStream out=new FileOutputStream("d:\\java\\001.xls"); //向d://test.xls中写数据
out.flush();
workBook.write(out);
out.close();
}
}
// HSSFWorkbook workBook = new HSSFWorkbook();
// HSSFSheet sheet = workBook.createSheet("sheet1");
// sheet.setColumnWidth(0, 2500);
// sheet.setColumnWidth(1, 5000);
// HSSFRow row = sheet.createRow(0);
// HSSFCell cell[] = new HSSFCell[100];
// for(int i = 0; i < 100; i++){
// cell[i] = row.createCell(i);
// HSSFCellStyle cellStyle = cell[i].getCellStyle();
// cellStyle.setAlignment(HorizontalAlignment.LEFT);
// }
// cell[0].setCellValue("bRdFlag");
// cell[1].setCellValue("cVouchType");
// cell[2].setCellValue("cBusType");
// cell[3].setCellValue("cSource");
// cell[4].setCellValue("cBusCode");
// cell[5].setCellValue("cWhCode");
//
// cell[0].setCellValue("收发标志");
// cell[1].setCellValue("单据类型编码");
// cell[2].setCellValue("业务类型");
// cell[3].setCellValue("单据来源");
// cell[4].setCellValue("对应业务单号");
// cell[5].setCellValue("单据日期");
// cell[6].setCellValue("收发单据号");
// cell[7].setCellValue("收发类别编码");
// cell[8].setCellValue("部门编码");
// cell[9].setCellValue("业务员编码");
//
/*List<CkRdrecord10> list = QueryCkRdrecord10.selectCkRdrecord10();
if(list != null && list.size() > 0){
for(int i = 0; i < list.size(); i++){
CkRdrecord10 ck10 = list.get(i);
HSSFRow dataRow = sheet.createRow(i+1);
HSSFCell dataCell[] = new HSSFCell[100];
for(int j = 0; j < 100; j++){
dataCell[j] = dataRow.createCell(j);
}
dataCell[0].setCellValue(ck10.getBrdflag());
dataCell[1].setCellValue(ck10.getCvouchtype());
dataCell[2].setCellValue(ck10.getCbustype());
dataCell[3].setCellValue(ck10.getCsource());
dataCell[4].setCellValue(ck10.getCbuscode());
dataCell[5].setCellValue(ck10.getCwhcode());
dataCell[6].setCellValue(ck10.getDdate());
dataCell[7].setCellValue(ck10.getCcode());
dataCell[8].setCellValue(ck10.getCrdcode());
dataCell[9].setCellValue(ck10.getCdepcode());
File file = new File("C:\\soft\\workspace\\mz\\.metadata\\.plugins\\org.eclipse.wst.server.core\\tmp0\\wtpwebapps\\poiExcel\\webapp\\xls\\001.xls");
//C:\soft\workspace\mz\.metadata\.plugins\org.eclipse.wst.server.core\tmp0
FileOutputStream fos = new FileOutputStream(file);
workBook.write(fos);
fos.close();
}
}*/
}
public static void main(String[] args)throws Exception {
QueryCkRdrecord10 queryCkRdrecord10 = new QueryCkRdrecord10();
queryCkRdrecord10.createExcel();
}
}
必要的准备:
1:对应的数据库
2:必要的xml模板