急急急java poi 怎么导出execl 怎么用java poi导出execl文件 小弟刚学很菜 那位大侠给全部源码包括js里的调用 谢谢很急 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 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"); } } 数据源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"); } }下面是导出生成EXCEL代码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,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>(); 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(); } } }} //创建工作薄 HSSFWorkbook wb = new HSSFWorkbook(); //创建工作表 HSSFSheet sheet =wb.createSheet("公司受理案件统计"); //列宽 sheet.setColumnWidth((short)0, 5000); sheet.setColumnWidth((short)1, 5000); sheet.setColumnWidth((short)2, 5000); sheet.setColumnWidth((short)3, 5000); sheet.setColumnWidth((short)4, 5000); sheet.setColumnWidth((short)5, 5000); sheet.setColumnWidth((short)6, 5000); sheet.setColumnWidth((short)7, 5000); sheet.setColumnWidth((short)8, 5000); //在表中建行 HSSFRow row = sheet.createRow(0); HSSFCell cell[] = new HSSFCell[9]; for(short i=0; i<9; i++){ cell[i]=row.createCell(i); } 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("合计"); //将数据以Excel导出 OutputStream sos = response.getOutputStream(); wb.write(sos); sos.close();你可以在页面上定义一个按钮,然后让它跳转到你的action里的方法,方法里可以写如何导出excel,将你查出来的数据分别写入到你创建的Excel的单元格里。就OK了。 [email protected]可以详细请教下吗 刚学很菜 关于spring data jpa 一个小问题 hibernate 问题! ssh中遇到的问题 急急急急 在suse上安装tomcat的问题 关于ANT现在使用的广泛性问题? 有没有人用过Eclipse的CORBA插件 Hibernate高手请进:急求:Hibernate语句; Schema与XML jboss3.2.2开发Class重新部署后服务器结果返回没有改变问题 java如何获取sap中所有可用的bapi名称 使用html ajax提交session丢失了,改为jsp就行了 JSP向数据库插入数据的问题
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");
}
}
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");
}
}
下面是导出生成EXCEL代码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,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>();
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();
}
}
}
}
HSSFWorkbook wb = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet =wb.createSheet("公司受理案件统计");
//列宽
sheet.setColumnWidth((short)0, 5000);
sheet.setColumnWidth((short)1, 5000);
sheet.setColumnWidth((short)2, 5000);
sheet.setColumnWidth((short)3, 5000);
sheet.setColumnWidth((short)4, 5000);
sheet.setColumnWidth((short)5, 5000);
sheet.setColumnWidth((short)6, 5000);
sheet.setColumnWidth((short)7, 5000);
sheet.setColumnWidth((short)8, 5000);
//在表中建行
HSSFRow row = sheet.createRow(0);
HSSFCell cell[] = new HSSFCell[9];
for(short i=0; i<9; i++){
cell[i]=row.createCell(i);
}
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("合计"); //将数据以Excel导出
OutputStream sos = response.getOutputStream();
wb.write(sos);
sos.close();你可以在页面上定义一个按钮,然后让它跳转到你的action里的方法,方法里可以写如何导出excel,将你查出来的数据分别写入到你创建的Excel的单元格里。就OK了。