package com.ufida.cutm.export;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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;
import com.ufida.cutm.dao.DBConnection;
import com.ufida.cutm.util.ConstantsExcel;
import com.ufida.cutm.util.ExcelWriter;
import com.ufida.cutm.util.StringUtilCommon;
import com.ufida.cutm.util.ZipCompressorByAnt;
import com.ufida.cutm.vo.T1;
/**
* 测试导出excel
* 最大数据量
* 2011-3-9 22：00
* @author zengq
*
*/
public class ExportExcel {
public static final String[] excelHeader=new String[]{"id","ida","idb","name","pwd","aa","bb","cc","dd","ee","ff","gg",
"hh","ii","jj","kk","ll","mm","nn","oo","pp","qq","rr","ss","tt"};
public static final String File_Path="E:\\projects\\java\\ws_0\\export\\data";
/**
* @author zengq
* @param sql 获取记录行数的sql
* @param recordStr sql语句中记录数的别名
* @return 总记录数
* 2011-3-10 14:25
* @throws Exception
*/
public Long getRecordNumber(String sql,String recordStr) throws Exception{
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs1=null;
recordStr=StringUtilCommon.getPropertyValue(recordStr);
try {
Long recordNum=0l;
conn=DBConnection.getConnection();
pst=conn.prepareStatement(sql);
rs1=pst.executeQuery();
while(rs1.next()){
if(recordStr.length()==0){
recordNum=rs1.getLong(1);
}else{
recordNum=rs1.getLong(recordStr);
}
}
return recordNum;
} catch (RuntimeException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeResource(rs1,pst,conn);
}
return new Long(0);
}
public List getAll(String sql) throws Exception{
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs1=null;
List list=null;
//FileOutputStream out=null;
FileWriter out=null;
try {
list=new ArrayList();
if(sql==null){
sql="select *from t1 ";
}
conn=DBConnection.getConnection();
// Statement st=conn.createStatement();
// ResultSet rs1=st.executeQuery(sql);
pst=conn.prepareStatement(sql);
rs1=pst.executeQuery();
rs1.last();
System.out.println("rs1.getRow()="+rs1.getRow());
rs1.beforeFirst();
File file=new File("c:/test.doc");
// if(!file.exists()){
// file=new File("c:/test.doc");
// }
//out=new FileOutputStream(file,true);
// out=new FileWriter(file,true);
while(rs1.next()){
T1 t=new T1();
t.setId(rs1.getLong("id"));
t.setIda(rs1.getLong("ida"));
t.setIdb(rs1.getString("idb"));
t.setName(rs1.getString("name"));
t.setPwd(rs1.getString("pwd"));
t.setAa(rs1.getString("aa"));
t.setBb(rs1.getString("bb"));
t.setCc(rs1.getString("cc"));
t.setDd(rs1.getString("dd"));
t.setEe(rs1.getString("ee"));
t.setFf(rs1.getString("ff"));
t.setGg(rs1.getString("gg"));
t.setHh(rs1.getString("hh"));
t.setIi(rs1.getString("ii"));
t.setJj(rs1.getString("jj"));
t.setKk(rs1.getString("kk"));
t.setLl(rs1.getString("ll"));
t.setMm(rs1.getString("mm"));
t.setNn(rs1.getString("nn"));
t.setOo(rs1.getString("oo"));
t.setPp(rs1.getString("pp"));
t.setQq(rs1.getString("qq"));
t.setRr(rs1.getString("rr"));
t.setSs(rs1.getString("ss"));
t.setTt(rs1.getString("tt"));
//System.out.println("t1="+t);
//out.write(t.toString().getBytes());
// out.write(t.toString()+"\t\r");
list.add(t);
}
System.out.println("空一行*********************************************");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeResource(rs1,pst,conn);
if(out!=null){
out.flush();
out.close();
out=null;
}
}
return list;
}
public void closeResource(ResultSet rs1,Statement pst,Connection conn) throws Exception{
if(rs1!=null){
rs1.close();
rs1=null;
}if(pst!=null){
pst.close();
pst=null;
}
DBConnection.closeConnection(conn);
System.gc();
}
/**
* 获取总页数
* @param recordNums
* @param pageSize
* @return
* @throws Exception
*/
public static Long getTotalPage(Long recordNums,Long pageSize) throws Exception{
if(recordNums==0||pageSize==0)
return 0l;
return (long)Math.ceil(recordNums/(double)pageSize);
}
/**
* 为sql语句增加提取数
* @param basicSql
* @param begin
* @param limitNum
* @return
* @throws Exception
*/
public static String getPageSql(String basicSql,Long begin,Long limitNum) throws Exception{
basicSql=StringUtilCommon.getPropertyValue(basicSql);
if(basicSql.length()==0)
return "";
String sql=" limit "+begin+","+limitNum;
return sql;
}
public Map getPageList(Map argsMap) throws Exception{
String path=(String)argsMap.get("path");
if(path==null){
path=this.File_Path;
}
File file=new File(path+"/test0.xls");
String sql="select count(*) record from t1";
Long totalNum=getRecordNumber(sql, "");
System.out.println("totalNum="+totalNum);
Long pageSize=10000l;
Long totalPage=getTotalPage(totalNum, pageSize);
System.out.println("totalPage="+totalPage);
String sql2="select *from t1 limit ";
ExcelWriter writer=new ExcelWriter();
for (int i = 0; i < totalPage; i++) {
if(i==0){
sql2+=" 0,"+pageSize;
System.out.println("sql2="+sql2);
List list=getAll(sql2);
WriteToExcel(file,list);
}else{
sql2="select *from t1 limit "+pageSize*i+","+pageSize;
System.out.println("sql2="+sql2);
List list=getAll(sql2);
WriteToExcel(new File(path+"/test"+i+".xls"), list);
//reWirte(file, list);
}
}
return null;
}
HSSFWorkbook workbook=null;
HSSFSheet sheet=null;
HSSFRow row=null;
HSSFCell cell=null;
public void WriteToExcel(File file,List list) throws Exception{
if(file==null&&!file.exists()){
System.out.println("文件不能为空！");
return;
}
FileOutputStream out=new FileOutputStream(file,true);
BufferedOutputStream buffout=new BufferedOutputStream(out);
ExcelWriter writer=new ExcelWriter();
writer=new ExcelWriter(buffout);
writer.createRow(0);
for (int i = 0; i < excelHeader.length; i++) {
writer.setCell(i, excelHeader[i]);
}
int rownum=0;
for (int j=0;j<list.size();j++) {
T1 t=(T1)list.get(j);
//System.out.println("WriteToExcel="+t);
//Thread.sleep(1000);
writer.createRow(rownum++);
for(int k=0;k<excelHeader.length;k++){
String header=excelHeader[k];
//System.out.println("j="+j+",k="+k+",header="+header);
if(header.equals("id")){
writer.setCell(0,t.getId());
}else if(header.equals("ida")){
writer.setCell(1,t.getIda()+"ida");
}else if(header.equals("idb")){
writer.setCell(2,t.getIdb()+"idb");
}else if(header.equals("name")){
writer.setCell(3,t.getName()+"name");
}else if(header.equals("pwd")){
writer.setCell(4,t.getPwd()+"pwd");
}
//下一个10行
else if(header.equals("aa")){
writer.setCell(5,t.getAa()+"aa");
}else if(header.equals("bb")){
writer.setCell(6,t.getBb()+"bb");
}else if(header.equals("cc")){
writer.setCell(7,t.getCc()+"cc");
}else if(header.equals("dd")){
writer.setCell(8,t.getDd()+"dd");
}else if(header.equals("ee")){
writer.setCell(9,t.getEe()+"ee");
}else if(header.equals("ff")){
writer.setCell(10,t.getFf()+"ff");
}else if(header.equals("gg")){
writer.setCell(11,t.getGg()+"gg");
}else if(header.equals("hh")){
writer.setCell(12,t.getHh()+"hh");
}else if(header.equals("ii")){
writer.setCell(13,t.getIi()+"ii");
}else if(header.equals("jj")){
writer.setCell(14,t.getJj()+"jj");
}
//下一个10行
else if(header.equals("kk")){
writer.setCell(15,t.getKk()+"kk");
}else if(header.equals("ll")){
writer.setCell(16,t.getLl()+"ll");
}else if(header.equals("mm")){
writer.setCell(17,t.getMm()+"mm");
}else if(header.equals("nn")){
writer.setCell(18,t.getNn()+"nn");
}else if(header.equals("oo")){
writer.setCell(19,t.getOo()+"oo");
}else if(header.equals("pp")){
writer.setCell(20,t.getPp()+"pp");
}else if(header.equals("qq")){
writer.setCell(21,t.getQq()+"qq");
}else if(header.equals("rr")){
writer.setCell(22,t.getRr()+"rr");
}else if(header.equals("ss")){
writer.setCell(23,t.getSs()+"ss");
}else if(header.equals("tt")){
writer.setCell(24,t.getTt()+"tt");
}
}
}
try {
writer.export();
writer.releaseResource();
System.out.println("file.size="+file.length()/(1000*1000)+"mb");
System.out.println(" 导出Excel：文件[成功] ");
} catch (IOException ex) {
System.out.println(" 导出Excel：文件[失败] ");
ex.printStackTrace();
}
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
ExportExcel export = new ExportExcel();
try {
Long start=System.currentTimeMillis();
export.getPageList(new HashMap());
Long end=System.currentTimeMillis();
System.out.println("生成excel總計时间="+(end-start));
//export.buildExcelByXml(null, null);
// export.buildMutiSheetExcel(null);
System.out.println("*************生成excel成功!");
} catch (Exception e) {
System.out.println("*************生成excel失败!");
e.printStackTrace();
}
try {
System.out.println("*****************下面开始打包！");
Long start=System.currentTimeMillis();
ZipCompressorByAnt zca = new ZipCompressorByAnt("e:/ZipCompressorByAnt.zip");
zca.compress(export.File_Path);
Long end=System.currentTimeMillis();
System.out.println("打包總計时间="+(end-start));
System.out.println("*****************打包成功！");
} catch (RuntimeException e) {
System.out.println("*****************打包失败！");
e.printStackTrace();
}
}
//追加行
public void reWirte(File file,List list) throws FileNotFoundException, IOException {
//获取workbook
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(
new FileInputStream(file)));
//获取sheet
HSSFSheet sheet = wb.getSheetAt(0);
//获取最后一行行号，从0开始
int maxRow = sheet.getLastRowNum();
HSSFRow row = null;//sheet.createRow(maxRow + 1);
// row.createCell((short) 0).setCellValue("追加的1");
int rownum=0;
for (int j=0;j<list.size();j++) {
T1 t=(T1)list.get(j);
//System.out.println("WriteToExcel="+t);
row=sheet.createRow(++maxRow);
for(int k=0;k<excelHeader.length;k++){
String header=excelHeader[k];
//System.out.println("j="+j+",k="+k+",header="+header);
if(header.equals("id")){
row.createCell((short) 0).setCellValue(t.getId());
}else if(header.equals("ida")){
row.createCell((short) 1).setCellValue(t.getIda());
}else if(header.equals("idb")){
row.createCell((short) 2).setCellValue(t.getIdb()+"idb");
}else if(header.equals("name")){
row.createCell((short) 3).setCellValue(t.getName()+"name");
}else if(header.equals("pwd")){
row.createCell((short) 4).setCellValue(t.getPwd()+"pwd");
}else if(header.equals("aa")){
row.createCell((short) 5).setCellValue(t.getAa()+"aa");
}else if(header.equals("bb")){
row.createCell((short) 6).setCellValue(t.getBb()+"bb");
}else if(header.equals("cc")){
row.createCell((short) 7).setCellValue(t.getCc()+"cc");
}else if(header.equals("dd")){
row.createCell((short) 8).setCellValue(t.getDd()+"dd");
}else if(header.equals("ee")){
row.createCell((short) 9).setCellValue(t.getEe()+"ee");
}else if(header.equals("ff")){
row.createCell((short) 10).setCellValue(t.getFf()+"ff");
}else if(header.equals("gg")){
row.createCell((short) 11).setCellValue(t.getGg()+"gg");
}else if(header.equals("hh")){
row.createCell((short) 12).setCellValue(t.getHh()+"hh");
}else if(header.equals("ii")){
row.createCell((short) 13).setCellValue(t.getIi()+"ii");
}else if(header.equals("jj")){
row.createCell((short) 14).setCellValue(t.getJj()+"jj");
}else if(header.equals("kk")){
row.createCell((short) 15).setCellValue(t.getKk()+"kk");
}else if(header.equals("ll")){
row.createCell((short) 16).setCellValue(t.getLl()+"ll");
}else if(header.equals("mm")){
row.createCell((short) 17).setCellValue(t.getMm()+"mm");
}else if(header.equals("nn")){
row.createCell((short) 18).setCellValue(t.getNn()+"nn");
}else if(header.equals("oo")){
row.createCell((short) 19).setCellValue(t.getOo()+"oo");
}else if(header.equals("pp")){
row.createCell((short) 20).setCellValue(t.getPp()+"pp");
}else if(header.equals("qq")){
row.createCell((short) 21).setCellValue(t.getQq()+"qq");
}else if(header.equals("rr")){
row.createCell((short) 22).setCellValue(t.getRr()+"rr");
}else if(header.equals("ss")){
row.createCell((short) 23).setCellValue(t.getSs()+"ss");
}else if(header.equals("tt")){
row.createCell((short) 24).setCellValue(t.getTt()+"tt");
}
}
}
FileOutputStream fos = new FileOutputStream(file);
try {
wb.write(fos);
fos.close();
System.out.println("*****************追加文件成功!");
} catch (RuntimeException e) {
// TODO Auto-generated catch block
System.out.println("*****************追加文件失败!");
e.printStackTrace();
}
}
/**
* 通过xml文档生成excel文件
* @param argsMap
* @throws Exception
*/
public void buildExcelByXml(Map<String, String> argsMap,File file) throws Exception{
String sql="select count(*) record from t1";
Long totalNum=new ExportExcel().getRecordNumber(sql, "");
System.out.println("totalNum="+totalNum);
Long pageSize=10000l;
Long totalPage=ExportExcel.getTotalPage(totalNum, pageSize);
System.out.println("totalPage="+totalPage);
String sql2="select *from t1 limit ";
ExcelWriter writer=new ExcelWriter();
String contents="";
for (int i = 0; i < totalPage; i++) {
if(i==0){
sql2+=" 0,"+pageSize;
System.out.println("sql2="+sql2);
List list=getAll(sql2);
contents=getXmlExcelContentByList(null, list);
System.out.println("第"+i+"次*************************contents.length()="+contents.length()/(1000*1000)+"mb");
}else{
sql2="select *from t1 limit "+pageSize*i+","+pageSize;
System.out.println("sql2="+sql2);
List list=getAll(sql2);
contents+=getXmlExcelContentByList(null, list);
System.out.println("第"+i+"次**************************contents.length()="+contents.length()/(1000*1000)+"mb");
}
}
if(argsMap==null){
argsMap=new HashMap();
}
argsMap.put(ConstantsExcel.Excel_Main_String_Key, contents);
createXmlExcel(argsMap,file);
}
public void createXmlExcel(Map map,File file) throws Exception{
if(file==null){
file = new File("c:/exprot.xls");
}if(!file.exists()){
file.mkdir();
}
StringBuffer bf = new StringBuffer();
bf.append(ConstantsExcel.Excel_Header_String);
String contents=(String) map.get(ConstantsExcel.Excel_Main_String_Key);
if(contents==null||contents.length()==0){
contents="";
}
bf.append(contents);
bf.append(ConstantsExcel.Excel_Foot_String);
try {
FileWriter write=new FileWriter(file);
//write.append(bf.toString());
write.write(bf.toString());
write.flush();
write.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public String getXmlExcelContentByList(String str,List list) throws Exception{
if(str==null){
str="";
}
if(list==null||list.size()==0){
return null;
}
System.out.println("*****************开始生成content!");
for (Object obj : list) {
T1 t=(T1) obj;
str+="<tr height=19 style='height:14.25pt'> "
+" <td height=19 width=72 style='height:14.25pt;width:54pt'>"+t.getId()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getIda()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getIdb()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getName()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getPwd()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getAa()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getBb()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getCc()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getDd()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getEe()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getFf()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getGg()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getHh()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getIi()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getJj()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getKk()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getLl()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getMm()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getNn()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getOo()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getPp()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getQq()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getRr()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getSs()+"</td> "
+" <td width=72 style='width:54pt'>"+t.getTt()+"</td> "
+"</tr> " ;
}
System.out.println("*****************生成content成功!");
return str;
}
public Map buildMutiSheetExcel(Map argsMap) throws Exception{
String sql="select count(*) record from t1";
Long totalNum=new ExportExcel().getRecordNumber(sql, "");
System.out.println("totalNum="+totalNum);
Long pageSize=10000l;
Long totalPage=ExportExcel.getTotalPage(totalNum, pageSize);
System.out.println("totalPage="+totalPage);
String sql2="select *from t1 limit ";
String excelName="c:/mutisheet2.xls";
HSSFWorkbook book=new HSSFWorkbook();
HSSFRow row=null;
HSSFCell cell=null;
File file=new File("c:/sheets.xls");
FileOutputStream out=null;
BufferedOutputStream buff=null;
if(argsMap==null){
argsMap=new HashMap();
argsMap.put("book", book);
argsMap.put("excelName", excelName);
}
for (int i = 0; i < totalPage; i++) {
if(i==0){
sql2+=" 0,"+pageSize;
System.out.println("sql2="+sql2);
List list=getAll(sql2);
HSSFSheet sheet=book.createSheet();
// argsMap.put("sheet", sheet);
// argsMap.put("list", list);
// buildMutiSheet(argsMap);
int maxRowNum=sheet.getLastRowNum();
for (int j=0;j<list.size();j++) {
T1 t=(T1)list.get(j);
//System.out.println("WriteToExcel="+t);
row=sheet.createRow(maxRowNum++);
int length=excelHeader.length;
for(int k=0;k<5;k++){
String header=excelHeader[k];
//System.out.println("j="+j+",k="+k+",header="+header);
cell=row.createCell((short) k);
if(header.equals("id")){
cell.setCellValue(t.getId());
}else if(header.equals("ida")){
cell.setCellValue(t.getIda()+"ida");
}else if(header.equals("idb")){
cell.setCellValue(t.getIdb()+"idb");
}else if(header.equals("name")){
cell.setCellValue(t.getName()+"name");
}else if(header.equals("pwd")){
cell.setCellValue(t.getPwd()+"pwd");
}
}
}
out=new FileOutputStream(file,true);
buff=new BufferedOutputStream(out);;
book.write(buff);
buff.flush();
buff.close();
buff=null;
cell=null;
row=null;
sheet=null;
}else{
sql2="select *from t1 limit "+pageSize*i+","+pageSize;
System.out.println("sql2="+sql2);
HSSFSheet sheet2=book.createSheet();
// argsMap.put("sheet", sheet);
List list=getAll(sql2);
int maxRowNum=sheet2.getLastRowNum();
for (int j=0;j<list.size();j++) {
T1 t=(T1)list.get(j);
//System.out.println("WriteToExcel="+t);
if(sheet2==null){
sheet2=book.createSheet();
}
if(row==null){
row=sheet2.createRow(maxRowNum++);
}
int length=excelHeader.length;
for(int k=0;k<5;k++){
String header=excelHeader[k];
//System.out.println("j="+j+",k="+k+",header="+header);
cell=row.createCell((short) k);
if(header.equals("id")){
cell.setCellValue(t.getId());
}else if(header.equals("ida")){
cell.setCellValue(t.getIda()+"ida");
}else if(header.equals("idb")){
cell.setCellValue(t.getIdb()+"idb");
}else if(header.equals("name")){
cell.setCellValue(t.getName()+"name");
}else if(header.equals("pwd")){
cell.setCellValue(t.getPwd()+"pwd");
}
}
out=new FileOutputStream(file,true);
buff=new BufferedOutputStream(out);;
book.write(buff);
buff.flush();
buff.close();
buff=null;
cell=null;
row=null;
sheet2=null;
// argsMap.put("list", list);
// buildMutiSheet(argsMap);
}
}
}
return null;
}
public void buildMutiSheet(Map argsMap) throws Exception{
String excelName=(String) argsMap.get("excelName");
excelName=StringUtilCommon.getPropertyValue(excelName);
if(excelName.length()==0){
excelName="c:/mutisheet2.xls";
}
File file=new File(excelName);
if(file==null&&!file.exists()){
file=new File(excelName);
}
HSSFWorkbook book=(HSSFWorkbook) argsMap.get("book");
if(book==null){
book=new HSSFWorkbook();
}
System.out.println("file.size="+file.length()/(1000*1000)+"mb");
FileOutputStream out=new FileOutputStream(file,true);
BufferedOutputStream buffout=new BufferedOutputStream(out);
HSSFSheet sheet=(HSSFSheet) argsMap.get("sheet");
HSSFRow row=null;
HSSFCell cell=null;
for (int i = 0; i < excelHeader.length; i++) {
if(sheet==null){
sheet=book.createSheet();
}
if(row==null){
row=sheet.createRow(0);
}
cell=row.createCell((short) i);
cell.setCellValue(excelHeader[i]);
}
cell=null;
List list=(List) argsMap.get("list");
if(list==null){
return;
}
//int rownum=0;
int maxRowNum=sheet.getLastRowNum();
for (int j=0;j<list.size();j++) {
T1 t=(T1)list.get(j);
//System.out.println("WriteToExcel="+t);
row=sheet.createRow(maxRowNum++);
int length=excelHeader.length;
for(int k=0;k<5;k++){
String header=excelHeader[k];
//System.out.println("j="+j+",k="+k+",header="+header);
cell=row.createCell((short) k);
if(header.equals("id")){
cell.setCellValue(t.getId());
}else if(header.equals("ida")){
cell.setCellValue(t.getIda()+"ida");
}else if(header.equals("idb")){
cell.setCellValue(t.getIdb()+"idb");
}else if(header.equals("name")){
cell.setCellValue(t.getName()+"name");
}else if(header.equals("pwd")){
cell.setCellValue(t.getPwd()+"pwd");
}
/*else if(header.equals("aa")){
cell.setCellValue(t.getAa()+"aa");
}else if(header.equals("bb")){
cell.setCellValue(t.getBb()+"bb");
}else if(header.equals("cc")){
cell.setCellValue(t.getCc()+"cc");
}else if(header.equals("dd")){
cell.setCellValue(t.getDd()+"dd");
}else if(header.equals("ee")){
cell.setCellValue(t.getEe()+"ee");
}else if(header.equals("ff")){
cell.setCellValue(t.getFf()+"ff");
}else if(header.equals("gg")){
cell.setCellValue(t.getGg()+"gg");
}else if(header.equals("hh")){
cell.setCellValue(t.getHh()+"hh");
}else if(header.equals("ii")){
cell.setCellValue(t.getIi()+"ii");
}else if(header.equals("jj")){
cell.setCellValue(t.getJj()+"jj");
}
else if(header.equals("kk")){
cell.setCellValue(t.getKk()+"kk");
}else if(header.equals("ll")){
cell.setCellValue(t.getLl()+"ll");
}else if(header.equals("mm")){
cell.setCellValue(t.getMm()+"mm");
}else if(header.equals("nn")){
cell.setCellValue(t.getNn()+"nn");
}else if(header.equals("oo")){
cell.setCellValue(t.getOo()+"oo");
}else if(header.equals("pp")){
cell.setCellValue(t.getPp()+"pp");
}else if(header.equals("qq")){
cell.setCellValue(t.getQq()+"qq");
}else if(header.equals("rr")){
cell.setCellValue(t.getRr()+"rr");
}else if(header.equals("ss")){
cell.setCellValue(t.getSs()+"ss");
}else if(header.equals("tt")){
cell.setCellValue(t.getTt()+"tt");
}*/
cell=null;
}
}
try {
book.write(buffout);
buffout.flush();
buffout.close();
buffout=null;
list=null;
sheet=null;
row=null;
cell=null;
System.out.println(" 导出Excel：文件[成功] ");
} catch (IOException ex) {
System.out.println(" 导出Excel：文件[失败] ");
ex.printStackTrace();
}
}
public void writeContent(ExcelWriter writer,List list) throws Exception{
int rownum=0;
for (int j=0;j<list.size();j++) {
T1 t=(T1)list.get(j);
//System.out.println("WriteToExcel="+t);
writer.createRow(rownum++);
for(int k=0;k<excelHeader.length;k++){
String header=excelHeader[k];
//System.out.println("j="+j+",k="+k+",header="+header);
if(header.equals("id")){
writer.setCell(0,t.getId());
}else if(header.equals("ida")){
writer.setCell(1,t.getIda()+"ida");
}else if(header.equals("idb")){
writer.setCell(2,t.getIdb()+"idb");
}else if(header.equals("name")){
writer.setCell(3,t.getName()+"name");
}else if(header.equals("pwd")){
writer.setCell(4,t.getPwd()+"pwd");
}else if(header.equals("aa")){
writer.setCell(5,t.getAa()+"aa");
}else if(header.equals("bb")){
writer.setCell(6,t.getBb()+"bb");
}else if(header.equals("cc")){
writer.setCell(7,t.getCc()+"cc");
}else if(header.equals("dd")){
writer.setCell(8,t.getDd()+"dd");
}
else if(header.equals("ee")){
writer.setCell(9,t.getEe()+"ee");
}else if(header.equals("ff")){
writer.setCell(10,t.getFf()+"ff");
}
else if(header.equals("gg")){
writer.setCell(11,t.getGg()+"gg");
}else if(header.equals("hh")){
writer.setCell(12,t.getHh()+"hh");
}
else if(header.equals("ii")){
writer.setCell(13,t.getIi()+"ii");
}
else if(header.equals("jj")){
writer.setCell(14,t.getJj()+"jj");
}else if(header.equals("kk")){
writer.setCell(15,t.getKk()+"kk");
}else if(header.equals("ll")){
writer.setCell(16,t.getLl()+"ll");
}else if(header.equals("mm")){
writer.setCell(17,t.getMm()+"mm");
}else if(header.equals("nn")){
writer.setCell(18,t.getNn()+"nn");
}else if(header.equals("oo")){
writer.setCell(19,t.getOo()+"oo");
}else if(header.equals("pp")){
writer.setCell(20,t.getPp()+"pp");
}else if(header.equals("qq")){
writer.setCell(21,t.getQq()+"qq");
}else if(header.equals("rr")){
writer.setCell(22,t.getRr()+"rr");
}else if(header.equals("ss")){
writer.setCell(23,t.getSs()+"ss");
}else if(header.equals("tt")){
writer.setCell(24,t.getTt()+"tt");
}
}
}
}
}
相关推荐
接下来，我们将创建一个简单的Java程序来演示如何使用POI导出Excel文件。首先，导入必要的POI库： ```java import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import ...
以下是一个简化的示例，演示了如何使用POI导出Excel： ```java import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.io....
"poi导出excel需要的jar"指的是在使用Apache POI进行Excel导出时，你需要包含特定的JAR依赖文件。 首先，要实现POI导出Excel的功能，你需要下载Apache POI相关的JAR文件。这些文件通常包括以下核心组件： 1. **poi...
在“POI导出Excel例子”中，我们首先需要了解几个核心类： 1. **HSSFWorkbook**：这是用于创建Excel 2003（.xls）文件的工作簿对象。如果你需要创建Excel 2007及以上版本的.xlsx文件，你应该使用XSSFWorkbook类。 ...
在这个特定的例子中，我们将讨论如何使用POI库基于一个Excel模板文件循环输出数据行，并将结果导出为新的Excel文件。 首先，我们需要理解POI库的基本概念。POI提供了HSSF（Horizontally SpreadSheet Format）和XSSF...
标题 "POI 生成EXCEL2007【含例子】" 涉及到的知识点主要集中在Apache POI库的使用上，这是一个强大的Java API，用于读取、写入和修改Microsoft Office格式的文件，特别是Excel文件。在这个场景中，重点是创建和操作...
这篇博客文章“Apache POI 导出excel实例”将深入探讨如何使用Apache POI库来生成Excel文件，这对于在Java环境中处理大量数据并需要导出为Excel格式的应用非常有用。 首先，我们需要导入Apache POI库到我们的项目中...
Struts2和Apache POI是Java开发中处理Web应用程序数据导出到Excel的常用工具。...以上就是使用Struts2和Apache POI导出数据到Excel的基本流程和关键知识点，实际应用中可能需要根据具体需求进行调整和优化。
在这个“struts2+poi导出excel表格完整例子”中，我们将深入探讨这两个工具如何协同工作，实现从Web应用导出数据到Excel电子表格的功能。 首先，让我们了解一下Struts2的工作原理。Struts2基于拦截器（Interceptor...
在这个例子中，我们看到如何使用POI库来将数据从数据库导出到Excel文件中。以下是关于这个话题的详细知识点： 1. **Apache POI**: Apache POI 是一个开源项目，它允许开发人员使用Java读取、写入和修改Microsoft ...
java导出excel例子，使用的是poi，可以分sheet导出，可以导出txt
"SpringMVC POI Excel 生成导出" SpringMVC 是一个基于 Java 的 ...这个例子展示了如何使用 SpringMVC 框架和 POI 库来生成 Excel 文件，并将其导出。这是一个非常有用的技术点，广泛应用于商业系统和数据分析等领域。
为了在Java项目中使用Apache POI导出Excel，你需要将这些jar包添加到你的项目的类路径中。如果你使用的是Maven或Gradle，可以在pom.xml或build.gradle文件中声明相应的依赖。在实际编程中，你可以使用POI提供的API来...
本文将深入探讨如何使用Apache POI来导出Excel报表。 首先，我们需要在项目中引入Apache POI的相关依赖。如果是Maven项目，可以在pom.xml文件中添加以下依赖： ```xml <groupId>org.apache.poi <artifactId>poi...
本篇文章将详细探讨如何利用Apache POI库在Java中导出Excel表格。 首先，我们需要理解Excel文件的基本结构。Excel文件是由工作簿（Workbook）组成的，工作簿中包含一个或多个工作表（Sheet）。每个工作表由行（Row...
同样，导出Excel功能可以通过创建一个新的工作簿，填充数据，然后设置样式和写入输出流来实现。以下是一个简单的导出示例： ```java @GetMapping("exportList") public void exportList(HttpServletResponse ...
在本文中，我们将深入探讨如何使用SpringBoot和Apache POI库来导出指定格式的Excel模板。Apache POI是Java领域广泛使用的库，用于读写Microsoft Office格式的文件，其中包括Excel（.xlsx 和 .xls）文件。SpringBoot...