java生成表格代码 java生成xlsx

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

java如何输出xls格式的Excel表格文件

有个开源的东东－jxl.jarjava生成表格代码，可以到下载。

一.读取Excel文件内容

/**读取Excel文件的内容

* @param file 待读取的文件

* @return

public static String readExcel(File file){

StringBuffer sb = new StringBuffer();

Workbook wb = null;

try {

//构造Workbook（工作薄）对象

wb=Workbook.getWorkbook(file);

} catch (BiffException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

if(wb==null)

return null;

//获得java生成表格代码了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了

Sheet[] sheet = wb.getSheets();

if(sheet!=nullsheet.length0){

//对每个工作表进行循环

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

//得到当前工作表的行数

int rowNum = sheet[i].getRows();

for(int j=0;jrowNum;j++){

//得到当前行的所有单元格

Cell[] cells = sheet[i].getRow(j);

if(cells!=nullcells.length0){

//对每个单元格进行循环

for(int k=0;kcells.length;k++){

//读取当前单元格的值

String cellValue = cells[k].getContents();

sb.append(cellValue+" ");

}

sb.append(" ");

}

sb.append(" ");

}

//最后关闭资源，释放内存

wb.close();

return sb.toString();

}

二.写入Excel文件

这里有很多格式了，比如文本内容加粗，加上某些颜色等，可以参考jxl的api，同时还推荐一篇不错的文章java生成表格代码：

/**生成一个Excel文件

* @param fileName 要生成的Excel文件名

public static void writeExcel(String fileName){

WritableWorkbook wwb = null;

try {

//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象

wwb = Workbook.createWorkbook(new File(fileName));

} catch (IOException e) {

e.printStackTrace();

}

if(wwb!=null){

//创建一个可写入的工作表

//Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置

WritableSheet ws = wwb.createSheet("sheet1", 0);

//下面开始添加单元格

for(int i=0;i10;i++){

for(int j=0;j5;j++){

//这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行

Label labelC = new Label(j, i, "这是第"+(i+1)+"行，第"+(j+1)+"列");

try {

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

ws.addCell(labelC);

} catch (RowsExceededException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

}

try {

//从内存中写入文件中

wwb.write();

//关闭资源，释放内存

wwb.close();

} catch (IOException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

}

三.在一个Excel文件中查找是否包含某一个关键字

/**搜索某一个文件中是否包含某个关键字

* @param file 待搜索的文件

* @param keyWord 要搜索的关键字

* @return

public static boolean searchKeyWord(File file,String keyWord){

boolean res = false;

Workbook wb = null;

try {

//构造Workbook（工作薄）对象

wb=Workbook.getWorkbook(file);

} catch (BiffException e) {

return res;

} catch (IOException e) {

return res;

}

if(wb==null)

return res;

//获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了

Sheet[] sheet = wb.getSheets();

boolean breakSheet = false;

if(sheet!=nullsheet.length0){

//对每个工作表进行循环

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

if(breakSheet)

break;

//得到当前工作表的行数

int rowNum = sheet[i].getRows();

boolean breakRow = false;

for(int j=0;jrowNum;j++){

if(breakRow)

break;

//得到当前行的所有单元格

Cell[] cells = sheet[i].getRow(j);

if(cells!=nullcells.length0){

boolean breakCell = false;

//对每个单元格进行循环

for(int k=0;kcells.length;k++){

if(breakCell)

break;

//读取当前单元格的值

String cellValue = cells[k].getContents();

if(cellValue==null)

continue;

if(cellValue.contains(keyWord)){

res = true;

breakCell = true;

breakRow = true;

breakSheet = true;

}

//最后关闭资源，释放内存

wb.close();

return res;

}

四.往Excel中插入图片图标

插入图片的实现很容易，参看以下代码：

/**往Excel中插入图片

* @param dataSheet 待插入的工作表

* @param col 图片从该列开始

* @param row 图片从该行开始

* @param width 图片所占的列数

* @param height 图片所占的行数

* @param imgFile 要插入的图片文件

public static void insertImg(WritableSheet dataSheet, int col, int row, int width,

int height, File imgFile){

WritableImage img = new WritableImage(col, row, width, height, imgFile);

dataSheet.addImage(img);

}

以上代码的注释已经很清楚了，大概也就不用再解释了，我们可以用如下程序验证：

try {

//创建一个工作薄

WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls"));

//待插入的工作表

WritableSheet imgSheet = workbook.createSheet("Images",0);

//要插入的图片文件

File imgFile = new File("D:/1.png");

//图片插入到第二行第一个单元格，长宽各占六个单元格

insertImg(imgSheet,0,1,6,6,imgFile);

workbook.write();

workbook.close();

} catch (IOException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

}

但是jxl只支持png格式的图片，jpg格式和gif格式都不支持

五.插入页眉页脚

一般的页眉页脚都分为三个部分,左,中,右三部分,利用如下代码可实现插入页眉页脚

/**向Excel中加入页眉页脚

* @param dataSheet 待加入页眉的工作表

* @param left

* @param center

* @param right

public static void setHeader(WritableSheet dataSheet,String left,String center,String right){

HeaderFooter hf = new HeaderFooter();

hf.getLeft().append(left);

hf.getCentre().append(center);

hf.getRight().append(right);

//加入页眉

dataSheet.getSettings().setHeader(hf);

//加入页脚

//dataSheet.getSettings().setFooter(hf);

}

我们可以用如下代码测试该方法:

try {

//创建一个工作薄

WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls"));

//待插入的工作表

WritableSheet dataSheet = workbook.createSheet("加入页眉",0);

ExcelUtils.setHeader(dataSheet, "chb", "2007-03-06", "第1页,共3页");

workbook.write();

workbook.close();

} catch (IOException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

}

六偷懒工具设计之sql2Excel

今天在公司陪山东客户调试，远程登录，我在linux下什么工具都没有，用ssh登录服务器，直接用mysql查询数据库，提出记录中的所有汉字全是乱码。哎，可恶的公司，不让我用windows，要不我就可以用putty或者EMS了，我ft!

甚是不爽之下，我决定自己写个工具了，把客户数据库中的数据全部提取并保存到Excel中，这样我不就可以一目了然了嘛，嘿嘿，好吧，那我就写一个工具吧。

第一部分就是谁都会的jdbc操作，连接数据库，提取数据集合。

Connection con;

Statement state;

/**初始化连接

* @param serverIp

* @param dataBase

* @param userName

* @param password

* @throws ClassNotFoundException

* @throws SQLException

public void init(String serverIp,String dataBase,String userName,String password) throws ClassNotFoundException, SQLException{

Class.forName("com.mysql.jdbc.Driver");

//配置数据源

String url="jdbc:mysql://"+serverIp+"/"+dataBase+"?useUnicode=truecharacterEncoding=GB2312";

con=DriverManager.getConnection(url,userName,password);

}

/**得到查询结果集

* @param sql

* @return

* @throws SQLException

public ResultSet getResultSet(String sql) throws SQLException{

state = con.createStatement();

ResultSet res = state.executeQuery(sql);

return res;

}

/**关闭连接

* @throws SQLException

public void close() throws SQLException{

if(con!=null)

con.close();

if(state!=null)

state.close();

}

第二部分就是把ResultSet中的记录写入一个Excel文件

操作Excel，我用的是jxl，不熟的同学可以参考：利用java操作Excel文件

/**将查询结果写入Excel文件中

* @param rs

* @param file

* @throws SQLException

public void writeExcel(ResultSet rs,File file) throws SQLException{

WritableWorkbook wwb = null;

try{

//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象

wwb = Workbook.createWorkbook(file);

} catch (IOException e){

e.printStackTrace();

}

if(wwb!=null){

WritableSheet ws = wwb.createSheet("sheet1", 0);

int i=0;

while(rs.next()){

Label label1 = new Label(0, i, rs.getString("id"));

Label label2 = new Label(1, i, rs.getString("category"));

try {

ws.addCell(label1);

ws.addCell(label2);

} catch (RowsExceededException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

}

i++;

}

try {

//从内存中写入文件中

wwb.write();

//关闭资源，释放内存

wwb.close();

} catch (IOException e) {

e.printStackTrace();

} catch (WriteException e){

e.printStackTrace();

}

测试程序：

Sql2Excel se = new Sql2Excel();

try {

se.init("127.0.0.1","mydabase", "root", "1234");

ResultSet rs = se.getResultSet("select id,category from xx ");

se.writeExcel(rs, new File("/root/sql2excel.xls"));

se.close();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

相关问答

Q1: 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例举方式

方法一java生成表格代码：导出Excel数据java生成表格代码的插件jexcelapi

程序实例如下java生成表格代码：

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 = { "教室名", "容量", "类型", "其java生成表格代码他说明" };

//设置Excel表头

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

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

wsheet.addCell(excelTitle);

}

int c = 1; //用于循环时Exceljava生成表格代码的行号

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) {