用Java轻松实现数据库数据导出为Excel表格 (java导出数据库数据为excel)

Label excelTitle = new Label(i, 0, title, 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:

*

* 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!=null&&type.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

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

if(totalSize!=0){

pager =new Pager(ncurrentPage,totalSize);

}

}

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

if(condition!=null&&condition.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

jxl.write.Label wlabel0;

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

wsheet.addCell(wlabel0);

}

jxl.write.Label wlabel1;

for(int i=0;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;j

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

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

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

wsheet.addCell(wlabel1);

}

}

}

wbook.write();

response.flushBuffer();

wbook.close();

os.close();

} catch (IOException e) {

// TODO Auto-generated catch block

OutJavaScript.outString(response, “Sorry! Export Excel exception.”);

e.printStackTrace();

} catch (HibernateException e1) {

// TODO Auto-generated catch block

OutJavaScript.outString(response, “Sorry! Database exception.”);

e1.printStackTrace();

} catch (AppSystemException e1) {

// TODO Auto-generated catch block

OutJavaScript.outString(response, “Sorry! System exception.”);

e1.printStackTrace();

} catch (AppBusinessException e1) {

// TODO Auto-generated catch block

OutJavaScript.outString(response, “Sorry! Database exception.”);

e1.printStackTrace();

} catch (RowsExceededException e) {

// TODO Auto-generated catch block

OutJavaScript.outString(response, “Sorry! Export Excel exception.”);

e.printStackTrace();

} catch (WriteException e) {

// TODO Auto-generated catch block

OutJavaScript.outString(response, “Sorry! Export Excel exception.”);

e.printStackTrace();

}

return null;

}

@Override

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

// TODO Auto-generated method stub

return null;

}

}

还有其他很多种 字数限制 无法一一举例方式

这个好像不能实现吧，导出的应该都是文本的！

如何从java输出到excel

用JAVA程序，读取或者写入excel文件戚岁，通过用jxl或者poi，下面是我给你写的例子。分别是用jxl读写excel文件，用poi读写excel文件。希望对你有帮助。（需要下载jxl和poi的jar包仿蚂）

package util.excel;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.format.Colour;

import jxl.format.UnderlineStyle;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

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

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

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;

public class ExcelUtil {

/**

* @param args

* @throws IOException

*/

public static void main(String args) throws IOException {

String outFile = “D:/workspace/JavaStudy/src/util/excel/test.xls”;

ExcelUtil.writeExcelByJXL(outFile, null);

}

/**

*

* @title: readExcelByJXL

* @description: 通过jxl读取excel文件

* @author yu ren tian

* @email

* @param excelFile

* @return

* @throws IOException

*/

private static List readExcelByJXL(String excelFile) throws IOException {

List rtn = new ArrayList();

FileInputStream fileInputStream = null;

try {

fileInputStream = new FileInputStream(excelFile);

Workbook excelWorkBook = Workbook.getWorkbook(fileInputStream);

Sheet sheet = excelWorkBook.getSheet(0);

int m = sheet.getRows();

int n = sheet.getColumns();

for (int i = 1; i

function AllAreaExcel() {

var title;

title=document.getElementsByTagName(“table”).childNodes.item(0).childNodes(0).childNodes(0).innerText;

alert(title);

var oXL = new ActiveXObject(“Excel.Application”);

var oWB = oXL.Workbooks.Add();

var oSheet = oWB.ActiveSheet;

//从excel的第5行开始插入

oSheet.Range(“A5”).select;

oWB .Worksheets(1).Activate;

oSheet.Cells(3,1).Value=title; //在第3行插入报表头

oWB.Worksheets(1).Range(“A3:I3”).merge(); // 合并单元格区域 A3:I3

oWB.Worksheets(1).Range(“亩掘A3:I3”).HorizontalAlignment=3; //旁或居中对齐A3:I3

var sel=document.body.createTextRange();

sel.moveToElementText(table1); //table 的ID值

sel.select();

sel.execCommand(“Copy”);

oSheet.Paste();

oXL.Visible = true;

Java怎样将数据库中数据导出为Excel文件，求完整例子附带数据库。

用POI啊！

感觉挺好使的，刚刚做完一个账目表导出。

下面是我写的一个运用jxl生成Excel文件的方法

该方法接收一个保存MarkesData数据的ArrayList arlist和文件生成路径Path

通过取得arlist里的数据生成Excel文件

感觉应尺知该是你想要的 我没调试过也许有错 你也可以根据你的需要进握穗行修改^_^

import java.io.File;

import java.io.IOException;

import java.util.ArrayList;

import java.util.Iterator;

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

public class WriteExcel {

WritableWorkbook book=null;

public void OutputExcel(ArrayList arlist,String Path){

try{

book = Workbook.createWorkbook(new File(Path));

//设置表名

WritableSheet sheet = book.createSheet(“考试单”,0);

//生成表格题头

Label labe1 = new Label(0, 0, “考生姓名” );

Label labe2 = new Label(1, 0, “地区”);

Label labe3 = new Label(2, 0, “所属院校”);

Label labe4 = new Label(3, 0, “班级”);

Label labe5 = new Label(4, 0, “考试号”);

Label labe6 = new Label(5, 0, “考试时间”);

Label labe7 = new Label(6, 0, “科目名称”);

//将生成的段困卜单元格添加到工作表中

sheet.addCell(labe1);

sheet.addCell(labe2);

sheet.addCell(labe3);

sheet.addCell(labe4);

sheet.addCell(labe5);

sheet.addCell(labe6);

sheet.addCell(labe7);

Iterator it = arlist.iterator();

int i = 1;

while(it.hasNext()){

//通过迭代获得arlist里的MarkesData对象

MarkesData temp = (MarkesData)it.next();

//取得数据生成单元格

Label label1=new Label(0,i,temp.getUser_name());

Label label2=new Label(1,i,temp.getArea_name());

Label label3=new Label(2,i,temp.getCollege_name());

Label label4=new Label(3,i,temp.getClass_name());

Label label5=new Label(4,i,temp.getTest_name());

Label label6=new Label(5,i,temp.getStarttime());

Label label7=new Label(6,i,temp.getSubject_name());

//将生成的单元格添加到工作表中

sheet.addCell(label1);

sheet.addCell(label2);

sheet.addCell(label3);

sheet.addCell(label4);

sheet.addCell(label5);

sheet.addCell(label6);

sheet.addCell(label7);

i++;

}

book.write();

book.close();

} catch (RowsExceededException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

} finally{

try{

if(book!=null)book.close();

}catch(Exception e){

System.out.println(“exception when closing Connection in finally”);

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

}

}

}

}

java导出数据库数据为excel的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于java导出数据库数据为excel,用Java轻松实现数据库数据导出为Excel表格,java导出excel,如何从java输出到excel,Java怎样将数据库中数据导出为Excel文件，求完整例子附带数据库。的信息别忘了在本站进行查找喔。