java数据库导出excel_java实现数据库导出到文件

本文目录一览：

1、使用java将数据库中的数据导出到excel中
2、java导出excel
3、java怎么导出excel表格
4、java导出数据到excel的几种方法的比较
5、java怎么实现选择导出excel的功能？
6、在Java编程中怎么将从数据库查询出来的数据导成Excel文件？

使用java将数据库中的数据导出到excel中

用jxl来实现

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;public String importEnter(HttpServletRequest request) {

Connection conn = null;

PreparedStatement ps = null;

OpenDbBean db = new OpenDbBean();

String message = "";

try {

ServletInputStream is = request.getInputStream();

byte[] junk = new byte[1024];

int bytesRead = 0;

//去掉浏览器发送的垃圾信息

bytesRead = is.readLine(junk, 0, junk.length);

Workbook workbook = Workbook.getWorkbook(is);

//取得第一个工作录

Sheet sheet = workbook.getSheet(0);

//从第二行开始取数据并处理

Enter enter = new Enter();

//连接数据库

conn = db.getConnection();

String sql =

"insert into haocai_enter(enter_id,enter_time,amount,jsr,type_id,xh)"

+ " values(s_enter_id.nextval,to_date(?,'dd/mm/yyyy'),?,?,?,?)";

ps = conn.prepareStatement(sql);

int okCount = 0;

ArrayList errorRows = new ArrayList();

for (int i = 1; i sheet.getRows(); i++) {

DB mydb = new DB();

Cell[] c = sheet.getRow(i);

String type_id = enter.getTypeIdByPm(c[PM].getContents());

if (type_id != null) {

//设置入库数据

ps.setString(1, c[ENTER_TIME].getContents());

ps.setString(2, c[AMOUNT].getContents());

ps.setString(3, c[JSR].getContents());

ps.setString(4, type_id);

ps.setString(5, c[XH].getContents());

ps.addBatch();

okCount++;

} else {

message += "br第" + i + "行 " + c[PM].getContents() + " 导入失败";

}

//执行该批过程

if (okCount 0) {

ps.executeBatch();

}

message += "br成功导入" + okCount + " 行";

workbook.close();

} catch (Exception e) {

System.out.println(e.getMessage());

message += "br" + e.getMessage();

} finally {

try {

db.CleanConnection(conn, ps, null);

} catch (SQLException e1) {

e1.printStackTrace();

}

return message;

}

用jxl.jar或者poi都可以，poi可以到apache网站上去下。

利用Java 创建和读取Excel文档

为了保证示例程序的运行，必须安装Java 2 sdk1.4.0 和Jakarta POI，Jakarta POI的Web站点是:

示例1将演示如何利用Jakarta POI API 创建Excel 文档。

示例1程序如下：

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFCell;

import java.io.FileOutputStream;

public class CreateXL {

/** Excel 文件要存放的位置，假定在D盘JTest目录下*/

public static String outputFile="D:/JTest/ gongye.xls";

public static void main(String argv[])

{

try

{

// 创建新的Excel 工作簿

HSSFWorkbook workbook = new HSSFWorkbook();

// 在Excel工作簿中建一工作表，其名为缺省值

// 如要新建一名为"效益指标"的工作表，其语句为：

// HSSFSheet sheet = workbook.createSheet("效益指标");

HSSFSheet sheet = workbook.createSheet();

// 在索引0的位置创建行（最顶端的行）

HSSFRow row = sheet.createRow((short)0);

//在索引0的位置创建单元格（左上端）

HSSFCell cell = row.createCell((short) 0);

// 定义单元格为字符串类型

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

// 在单元格中输入一些内容

cell.setCellValue("增加值");

// 新建一输出文件流

FileOutputStream fOut = new FileOutputStream(outputFile);

// 把相应的Excel 工作簿存盘

workbook.write(fOut);

fOut.flush();

// 操作结束，关闭文件

fOut.close();

System.out.println("文件生成...");

}catch(Exception e) {

System.out.println("已运行 xlCreate() : " + e );

}

读取Excel文档中的数据

示例2将演示如何读取Excel文档中的数据。假定在D盘JTest目录下有一个文件名为gongye.xls的Excel文件。

示例2程序如下：

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFCell;

import java.io.FileInputStream;

public class ReadXL {

/** Excel文件的存放位置。注意是正斜线*/

public static String fileToBeRead="D:/JTest/ gongye.xls";

public static void main(String argv[]){

try{

// 创建对Excel工作簿文件的引用

HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));

// 创建对工作表的引用。

// 本例是按名引用（让我们假定那张表有着缺省名"Sheet1"）

HSSFSheet sheet = workbook.getSheet("Sheet1");

// 也可用getSheetAt(int index)按索引引用，

// 在Excel文档中，第一张工作表的缺省索引是0，

// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);

// 读取左上端单元

HSSFRow row = sheet.getRow(0);

HSSFCell cell = row.getCell((short)0);

// 输出单元内容，cell.getStringCellValue()就是取所在单元的值

System.out.println("左上端单元是： " + cell.getStringCellValue());

}catch(Exception e) {

System.out.println("已运行xlRead() : " + e );

}

设置单元格格式

在这里，我们将只介绍一些和格式设置有关的语句，我们假定workbook就是对一个工作簿的引用。在Java

中，第一步要做的就是创建和设置字体和单元格的格式，然后再应用这些格式：

1、创建字体，设置其为红色、粗体：

HSSFFont font = workbook.createFont();

font.setColor(HSSFFont.COLOR_RED);

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

2、创建格式

HSSFCellStyle cellStyle= workbook.createCellStyle();

cellStyle.setFont(font);

3、应用格式

HSSFCell cell = row.createCell((short) 0);

cell.setCellStyle(cellStyle);

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setCellValue("标题 ");

总之，如本篇文章所演示的一样，Java程序员不必担心Excel工作表中的数据了，利用Jakarta POI API，

我们就可以轻易的在程序中存取Excel文档。

java数据库导出excel_java实现数据库导出到文件

java导出excel

java导出Excel

java 代码 /* * Generated by MyEclipse Struts * Template path: templates/java/JavaClass.vtl */ package com.axon.fable.sams.view.action; import java.io.IOException; import java.io.OutputStream; import java.util.List; import javax.serv ...

java导出Excel例举方式

方法一：导出Excel数据的插件jexcelapi

程序实例如下：

public void exportClassroom(OutputStream os) throws PaikeException {

try {

WritableWorkbook wbook = Workbook.createWorkbook(os); //建立excel文件

WritableSheet wsheet = wbook.createSheet("教室信息表", 0); //工作表名称

//设置Excel字体

WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,

WritableFont.BOLD, false,

jxl.format.UnderlineStyle.NO_UNDERLINE,

jxl.format.Colour.BLACK);

WritableCellFormat titleFormat = new WritableCellFormat(wfont);

String[] title = { "教室名", "容量", "类型", "其他说明" };

//设置Excel表头

for (int i = 0; i title.length; i++) {

Label excelTitle = new Label(i, 0, title[i], titleFormat);

wsheet.addCell(excelTitle);

}

int c = 1; //用于循环时Excel的行号

ClassroomService cs = new ClassroomService();

List list = cs.findAllClassroom(); //这个是从数据库中取得要导出的数据

Iterator it = list.iterator();

while (it.hasNext()) {

ClassroomDTO crdto = (ClassroomDTO) it.next();

Label content1 = new Label(0, c, crdto.getRoomname());

Label content2 = new Label(1, c, crdto.getCapicity().toString());

Label content3 = new Label(2, c, crdto.getRoomTypeId()

.toString());

Label content4 = new Label(3, c, crdto.getRemark());

wsheet.addCell(content1);

wsheet.addCell(content2);

wsheet.addCell(content3);

wsheet.addCell(content4);

c++;

}

wbook.write(); //写入文件

wbook.close();

os.close();

} catch (Exception e) {

throw new PaikeException("导出文件出错");

}

方法二：直接用Java代码实现导出Excel报表

* Generated by MyEclipse Struts

* Template path: templates/java/JavaClass.vtl

package com.axon.fable.sams.view.action;

import java.io.IOException;

import java.io.OutputStream;

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

import org.hibernate.HibernateException;

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.Transaction;

import com.axon.fable.empolderpackage.out.OutJavaScript;

import com.axon.fable.empolderpackage.page.Pager;

import com.axon.fable.empolderpackage.string.MyPublic;

import com.axon.fable.sams.common.BaseAction;

import com.axon.fable.sams.exception.AppBusinessException;

import com.axon.fable.sams.exception.AppSystemException;

/**

* MyEclipse Struts

* Creation date: 06-28-2007

* XDoclet definition:

* @struts.action path="/axon" name="axonForm" input="/samspage/zm/axon.jsp" parameter="method" scope="request" validate="true"

* @struts.action-forward name="success" path="/samspage/zm/content.jsp"

public class StshipoperationAction extends BaseAction {

* Generated Methods

private static Session session=null;

private static Transaction ts=null;

private static Query queryC=null;

private static Query queryR=null;

private static Query query=null;

private static List list=null;

private static Integer startRow;

private static Integer ncurrentPage;

private static Integer cell;

private static String property;

private static String sql;

private static String type;

private static String condition ;//是否导出当前页

private static String currentPage;

private static String from ;

private static String pactdata;

private static String voyagename;

private static String voyageno;

private static String dwt ;

private static String hirefrom ;

private static String deliveryposion ;

private static String redeliveryposion ;

private static String sheepowner ;

private static String addr;

private static String addcomm;

private static String rent;

private static String fileName ;

private static OutputStream os;

@Override

public ActionForward findAll(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {

// TODO Auto-generated method stub

return null;

}

@Override

public ActionForward findById(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {

// TODO Auto-generated method stub

return null;

}

@Override

public ActionForward save(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {

// TODO Auto-generated method stub

return null;

}

public static String strNull(Object nullStr,String newStr,Integer cell){

if(nullStr==null||nullStr.equals("")){return newStr;}else{cell+=1;return nullStr+"";}

}

public static String getStr(String str,Integer cell){

if(str==null||str.trim().equals("")){return "";}else{cell+=1;return ","+str;}

}

public static String getExcelTile(String title){

if(title==null)

return "";

if(title.equals("modela.stsid"))

return "编号";

if(title.equals("modelc.pactdata"))

return "合同日期";

if(title.equals("modela.voyagename"))

return "航名";

if(title.equals("modela.voyageno"))

return "航次";

if(title.equals("modelc.dwt"))

return "DWT";

if(title.equals("modelc.hirefrom"))

return "受载期";

if(title.equals("modela.deliveryposion"))

return "交船地点";

if(title.equals("modela.redeliveryposion"))

return "还船地点";

if(title.equals("modelc.sheepowner"))

return "联系人";

if(title.equals("modelc.addr"))

return "经纪人拥金";

if(title.equals("modelc.addcomm"))

return "ADD COMM";

if(title.equals("modelc.rent"))

return "租金";

return "";

}

public ActionForward exporVoyagesInfoToExcel(ActionMapping mapping, ActionForm form,

HttpServletRequest request, HttpServletResponse response) {

list=null;

startRow=0;

ncurrentPage=1;

cell=0;

type =request.getParameter("type");

condition =request.getParameter("condition");//是否导出当前页

currentPage =request.getParameter("currentPage");

from =request.getParameter("from");

pactdata = request.getParameter("modelc.pactdata");

voyagename = request.getParameter("modela.voyagename");

voyageno = request.getParameter("modela.voyageno");

dwt = request.getParameter("modelc.dwt");

hirefrom = request.getParameter("modelc.hirefrom");

deliveryposion = request.getParameter("modela.deliveryposion");

redeliveryposion = request.getParameter("modela.redeliveryposion");

sheepowner = request.getParameter("modelc.sheepowner");

addr = request.getParameter("modelc.addr");

addcomm = request.getParameter("modelc.addcomm");

rent = request.getParameter("modelc.rent");

if(type!=nulltype.trim().equals("1")){

type ="已还船舶--费用未结清";

}else{

type ="已还船舶--费用已结清";

}

property =getStr(pactdata,cell)+getStr(voyagename,cell)+getStr(voyageno,cell)+getStr(dwt,cell)+getStr(hirefrom,cell)

+getStr(deliveryposion,cell)+getStr(redeliveryposion,cell)+getStr(sheepowner,cell)+getStr(addr,cell)+getStr(addcomm,cell)

+getStr(rent,cell);

property = property.substring(1);

String split[] = property.split(",");

// System.out.println("-----------------------------property:"+property);

if(currentPage!=null!currentPage.trim().equals("")){

ncurrentPage =Integer.parseInt(currentPage);

}else{

OutJavaScript.outString(response, "Sorry! Failed to get information of pager.");

return null;

}

try {

session =getServiceLocator().getBaseHibernateDAO().getSession();

sql ="select count(*) "+from;

query =session.createQuery(sql);

list = query.list();

for (int i = 0; i list.size(); i++) {

totalSize =(Integer)list.get(i);

if(totalSize!=0){

pager =new Pager(ncurrentPage,totalSize);

}

query =getServiceLocator().getBaseHibernateDAO().getSession().createQuery("select " +property+from);

if(condition!=nullcondition.trim().equals("1")){//分页数据

startRow = (ncurrentPage - 1)*pager.getPageSize();

query.setFirstResult(startRow);

query.setMaxResults(pager.getPageSize());

// System.out.println("---------------------------------------------------query:"+query);

}

list = query.list();

fileName = "shipInfo";

os = response.getOutputStream();

response.reset();

response.setHeader("Content-disposition",

"attachment; filename=" +fileName + ".xls");

response.setContentType("application/msexcel");

jxl.write.WritableWorkbook wbook = Workbook.createWorkbook(os);

jxl.write.WritableSheet wsheet = wbook.createSheet("the first sheet", 0);

for (int i = 0; i split.length; i++) {

jxl.write.Label wlabel0;

wlabel0 = new jxl.write.Label(i, 0, getExcelTile(split[i]));

wsheet.addCell(wlabel0);

}

jxl.write.Label wlabel1;

for(int i=0;ilist.size();i++) {

if(split.length==1){

Object strval = (Object) list.get(i);

String javaScript=""+MyPublic.toHtmlStr(strval==null?"":strval.toString().trim())+"";

wlabel1 = new jxl.write.Label(0, i+1,strval==null?"":strval.toString().trim() );

wsheet.addCell(wlabel1);

}else{

Object[] strval = (Object[]) list.get(i);

for(int j=0;jstrval.length;j++) {

String javaScript=""+MyPublic.toHtmlStr(strval[j]==null?"":strval[j].toString().trim())+"";

//System.out.println("===================script:"+javaScript);

wlabel1 = new jxl.write.Label(j, i+1,strval[j]==null?"":strval[j].toString().trim() );

wsheet.addCell(wlabel1);

}

wbook.write();

response.flushBuffer();

wbook.close();

os.close();

} catch (IOException e) {