java导出excel文件相关

方法一：

可以先按照html的规则生成，然后通过stringBuffer相关截取.html和添加.xls

jxl poi开源的两个操作excel的三方组建
csv速度比较快，纯文本，但是对数据的内容有要求
个人新发现用html 的<table><tr><td> 标签也可以转换成excel

举例 a.html
<table>
<tr><td>1</td><td>1</td></tr>
<tr><td>2,000</td><td>2,000</td></tr>
<tr><td>3.1415926</td><td>3.1415926</td></tr>
</table>

再把文件名转换成a.xls就OK了

方法二：

打开excel文件的web.xml配置.txt

<mime-mapping>
<extension>xls</extension>
<mime-type>application/msexcel</mime-type>
</mime-mapping>

FileExportAction.java

===============================================================================================

package com.anyi.erp.oa.action;

import com.anyi.gp.context.ApplusContext;
import com.anyi.gp.core.dao.BaseDao;
import com.opensymphony.webwork.interceptor.ServletRequestAware;
import com.opensymphony.webwork.interceptor.ServletResponseAware;
import com.opensymphony.xwork.ActionSupport;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintStream;
import java.sql.Date;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.PageOrientation;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class FileExportAction extends ActionSupport
implements ServletRequestAware, ServletResponseAware
{
private static final long serialVersionUID = 4498487628145443321L;
private HttpServletResponse response;
private HttpServletRequest request;
private String fieldsName = "";
private String sqlId = "";
private String OA_TITLE = "";
private String parms="";
private String fieldsId="";
private String width="";
private String height="";
private JSONObject json=null;
private BaseDao baseDao;
String[] headersList;
private static final String MAIN_PATH= ApplusContext.getEnvironmentConfig().get("oaReportPath");

public void setFieldsName(String fieldsName) {
this.fieldsName = fieldsName;
}

public void setSqlId(String sqlId) {
this.sqlId = sqlId;
}

public void setTitles(String OA_TITLE) {
this.OA_TITLE = OA_TITLE;
}

public String execute() {
parms = request.getParameter("parms");
json = JSONObject.fromObject(parms);
OA_TITLE = request.getParameter("OA_TITLE");
fieldsName = request.getParameter("fieldsName");
fieldsId = request.getParameter("fieldsId");
width = request.getParameter("width");
height = request.getParameter("height");
System.out.println("width============="+width);
System.out.println("height============="+height);
System.out.println("OA_TITLE>>>>>>>>"+OA_TITLE);
System.out.println("fieldsName>>>>>>>>"+fieldsName);
System.out.println("fieldsId>>>>>>>>"+fieldsId);
System.out.println("MAIN_PATH==========="+MAIN_PATH);
System.out.println("json============="+json.toString());
WritableWorkbook wwb = null;
FileOutputStream os = null;

try
{
this.response.setContentType("text/xml; charset=GBK");
long time = System.currentTimeMillis();
String url = this.request.getRealPath("/");

System.out.println("URL=================="+url);

os = new FileOutputStream(url+"\\oaReport\\"+OA_TITLE+time+".xls");
wwb = Workbook.createWorkbook(os);

WritableSheet wsheet = wwb.createSheet("tb_1", 0);

wsheet.getSettings().setOrientation(
PageOrientation.LANDSCAPE);
this.headersList = this.fieldsId.split(",");
fillHeaders(wsheet, this.headersList);
fillContent(wsheet);
wwb.write();


os.flush();

// String url = this.request.getRequestURL().toString();
// url = url.substring(0,url.indexOf("OA"));


this.response.getWriter().print(OA_TITLE+time+".xls");
System.out.println("MANE==============="+OA_TITLE+time+".xls");
System.out.println("已返回Excel");
}
catch (Exception e) {
System.out.println("生成信息表(Excel格式)时出错：");
e.printStackTrace();
}finally{
try {
wwb.close();
os.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


}
return MAIN_PATH+"\\"+OA_TITLE+System.currentTimeMillis()+".xls";
}

public void fillHeaders(WritableSheet wsheet, String[] headers)
throws RowsExceededException, WriteException
{
String[] widthargs = width.split(",");
for (int i = 0; i < this.headersList.length; ++i) {
wsheet.setColumnView(i, Long.valueOf(widthargs[i]).intValue());

}
wsheet.mergeCells(0, 0, headers.length - 1, 0);
WritableCellFormat format2 = getFormat(new WritableFont(
WritableFont.TIMES, 30, WritableFont.BOLD));
format2.setAlignment(Alignment.CENTRE);
Label label1 = new Label(0, 0, this.OA_TITLE , format2);
WritableCellFormat format1 = getFormat(new WritableFont(
WritableFont.TIMES, 15, WritableFont.BOLD));
format1.setAlignment(Alignment.CENTRE);
wsheet.addCell(label1);
for (int i = 0; i < headers.length; ++i) {
Label label = new Label(i, 1, headers[i], format1);
wsheet.addCell(label);
}
}

public static WritableCellFormat getFormat(WritableFont font)
throws RowsExceededException, WriteException
{
WritableCellFormat format1 = new WritableCellFormat(font);

format1.setVerticalAlignment(VerticalAlignment.CENTRE);

format1.setWrap(true);
format1.setBorder(Border.ALL, BorderLineStyle.THIN);
return format1;
}

public void fillContent(WritableSheet wsheet)
throws RowsExceededException, WriteException, SQLException
{
sqlId = request.getParameter("sqlid");
System.out.println("SQLID>>>>>>>"+sqlId);

List list = baseDao.queryForList(sqlId,json);
System.out.println(list);


String value="";
String[] field = fieldsName.split(",");
for (int i=0;i< list.size(); i++) {
// wsheet.setRowView(i+2, Long.valueOf(height).intValue());

for(int j=0;j<this.headersList.length;j++){

HashMap map =(HashMap)list.get(i);

if(map.get(field[j]) instanceof java.sql.Date) {
java.sql.Dated = (java.sql.Date) map.get(field[j]);
String d2 = new java.util.Date(d.getTime()).toLocaleString();
value =d2.substring(0, d2.indexOf("0:00:00"));

}else{
value = (String)map.get(field[j]);
}
Label label = new Label(j, i+2, value);
wsheet.addCell(label);

}

}

}

public void init() throws ServletException
{
}

public void setServletRequest(HttpServletRequest arg0)
{
this.request = arg0;
}

public void setServletResponse(HttpServletResponse arg0)
{
this.response = arg0;
}

public BaseDao getBaseDao() {
return baseDao;
}

public void setBaseDao(BaseDao baseDao) {
this.baseDao = baseDao;
}

}