Excel文件毒写实例(将数据库中的数据写到Excel文件中)

http://code.google.com/p/simplejee/source/browse/trunk/simplejee/src/com/yuqiaotech/simplejee/misc/Excel.java

 

留个地址,备用,呵呵!!

 

最近项目中,要用到导出到Excel文件的功能,用到了Apatch的开源组件POI,感觉挺方便的,和大家一起分享下:

 

用POI把数据导出为Excel

<!--相关信息-->

代码
package com.javaeye.yongsky;

import java.sql.*;
import java.util.*;

import com.microsoft.jdbc.*;

public class SheetDataSource {

private static Connection con ;

private static final String DatabaseName = "POIDEMO";

private static final String userName = "sa";

private static final String password = "123";

public SheetDataSource()
{
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
try {
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName="+DatabaseName,userName,password);
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

public static ResultSet selectAllDataFromDB() throws SQLException
{
new SheetDataSource();
Statement stmt = con.createStatement();
return stmt.executeQuery("select * from COMPANY");
}

}

<script type="text/javascript">render_code();</script>
上面部分代码主要是从数据库里面取出数据，作为Excel的数据源．

（二）PoiDemo．java

代码
package com.javaeye.yongsky;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PoiDemo {

//表头
public static final String[] tableHeader = {"企业中文名","所属国家","企业英文名","2003年排名","2004年排名","2005年排名",
"2006年排名","2007年排名","主要业务","2003年营业额","2004年营业额","2005年营业额","2006年营业额","2007年营业额","企业编号","名次升降",
"图片","状况"};
//创建工作本
public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();
//创建表
public static HSSFSheet demoSheet = demoWorkBook.createSheet("The World's 500 Enterprises");
//表头的单元格个数目
public static final short cellNumber = (short)tableHeader.length;
//数据库表的列数
public static final int columNumber = 18;
/**
* 创建表头
* @return
*/
public static void createTableHeader()
{
HSSFHeader header = demoSheet.getHeader();
header.setCenter("世界五百强企业名次表");
HSSFRow headerRow = demoSheet.createRow((short) 0);
for(int i = 0;i < cellNumber;i++)
{
HSSFCell headerCell = headerRow.createCell((short) i);
headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
headerCell.setCellValue(tableHeader[i]);
}
}
/**
* 创建行
* @param cells
* @param rowIndex
*/
public static void createTableRow(List<string> cells,</string>short rowIndex)
{
//创建第rowIndex行
HSSFRow row = demoSheet.createRow((short) rowIndex);
for(short i = 0;i < cells.size();i++)
{
//创建第i个单元格
HSSFCell cell = row.createCell((short) i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(cells.get(i));
}
}

/**
* 创建整个Excel表
* @throws SQLException
*
*/
public static void createExcelSheeet() throws SQLException
{
createTableHeader();
ResultSet rs = SheetDataSource.selectAllDataFromDB();
int rowIndex = 1;
while(rs.next())
{
List<string> list = new ArrayList<string>(); </string></string>
for(int i = 1;i <= columNumber;i++)
{
list.add(rs.getString(i));
}
createTableRow(list,(short)rowIndex);
rowIndex++;
}
}
/**
* 导出表格
* @param sheet
* @param os
* @throws IOException
*/
public void exportExcel(HSSFSheet sheet,OutputStream os) throws IOException
{
sheet.setGridsPrinted(true);
HSSFFooter footer = sheet.getFooter();
footer.setRight("Page " + HSSFFooter.page() + " of " +
HSSFFooter.numPages());
demoWorkBook.write(os);
}

public static void main(String[] args) {
String fileName = "D:\\世界五百强企业名次表.xls";
FileOutputStream fos = null;
try {
PoiDemo pd = new PoiDemo();
pd.createExcelSheeet();
fos = new FileOutputStream(fileName);
pd.exportExcel(demoSheet,fos);
JOptionPane.showMessageDialog(null, "表格已成功导出到 : "+fileName);
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "表格导出出错，错误信息 ："+e+"\n错误原因可能是表格已经打开。");
e.printStackTrace();
} finally {
try {
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}

本人,已经成功将sqlserver2005中的数据导入到Excel中,感觉确实用的挺方便的,大家可以试试!!

还有一个问题就是,POI对中文的处理不是很好,需要进行设置一下: 如在创建Excel表头的时候

// 创建行
 public static void createTableRow(List<String> cells, short rowIndex) {

  // 创建第rowIndex行
  HSSFRow row = demoSheet.createRow((short) rowIndex);
  for (short i = 0; i < cells.size(); i++) {
   // 创建第i个单元格
   HSSFCell cell = row.createCell((short) i);
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue(cells.get(i));
  }
 }

注意上面的红色代码,可以很好的解决POI导出Excel乱码的问题,已经测试,加入了这句后,可以使用POI完美的支持中文了,哈哈!!