javaexceltohtml
1. 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!=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 < 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!=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 < 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;i<list.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;j<strval.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) {
// 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;
}
}
還有其他很多種 字數限制 無法一一舉例方式
2. java 導出html 到 Excel 問題
最後調用一下 CollectGarbage();
3. java 通過jacob完成excel轉成html格式時報錯：
你的錯誤沒有貼出來，可能是tomcat裡面的jar包和你運行報錯的那一段的jar包裡面有沖突。需要調整jar包的載入順序。
4. javaPOI把excel轉換成html 中怎麼去掉序號列
excelToHtmlConverter.setOutputColumnHeaders(false);
excelToHtmlConverter.setOutputRowNumbers(false);
就可以了。
5. 如何用JAVA導出Excel（使用POI）
// 創建臨時文件(excel為Workbook對象)
response.reset();
response.setContentType("application/download");
response.setHeader("Content-Disposition",
"attachment;filename=" + excel.getFileName());
excel.getExcel().write(response.getOutputStream());
response.flushBuffer();
6. java excel怎麼讀取到html
一般都是用jacob的，這里不能發地址，自己搜一下，要下1.9的版本
1、我們解開下載的jacob_1.9.zip,在文件夾中找到jacob.dll和jacob.jar兩個文件
2、將壓縮包解壓後，Jacob.jar添加到Libraries中；
3、將Jacob.dll放至「WINDOWS\SYSTEM32」下面。
需要注意的是：
【使用IDE啟動Web伺服器時，系統讀取不到Jacob.dll，例如用MyEclipse啟動Tomcat，就需要將dll文件到MyEclipse安裝目錄的「jre\bin」下面。
一般系統沒有載入到Jacob.dll文件時，報錯信息為：「java.lang.UnsatisfiedLinkError: no jacob in java.library.path」】
使用Jacob轉換Word,Excel為HTML
JAVA代碼：
Java代碼
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
public class TransformFiletoHtml
{
int WORD_HTML = 8;
int WORD_TXT = 7;
int EXCEL_HTML = 44;
/**
* WORD轉HTML
* @param docfile WORD文件全路徑
* @param htmlfile 轉換後HTML存放路徑
*/
public void wordToHtml(String docfile, String htmlfile)
{
ActiveXComponent app = new ActiveXComponent("Word.Application"); // 啟動word
try
{
app.setProperty("Visible", new Variant(false));
Dispatch docs = app.getProperty("Documents").toDispatch();
Dispatch doc = Dispatch.invoke(docs,"Open",Dispatch.Method,new Object[] { docfile, new Variant(false),new Variant(true) }, new int[1]).toDispatch();
Dispatch.invoke(doc, "SaveAs", Dispatch.Method, new Object[] {htmlfile, new Variant(WORD_HTML) }, new int[1]);
Variant f = new Variant(false);
Dispatch.call(doc, "Close", f);
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
app.invoke("Quit", new Variant[] {});
}
}
/**
* EXCEL轉HTML
* @param xlsfile EXCEL文件全路徑
* @param htmlfile 轉換後HTML存放路徑
*/
public void excelToHtml(String xlsfile, String htmlfile)
{
ActiveXComponent app = new ActiveXComponent("Excel.Application"); // 啟動excel
try
{
app.setProperty("Visible", new Variant(false));
Dispatch excels = app.getProperty("Workbooks").toDispatch();
Dispatch excel = Dispatch.invoke(excels,"Open",Dispatch.Method,new Object[] { xlsfile, new Variant(false),new Variant(true) }, new int[1]).toDispatch();
Dispatch.invoke(excel, "SaveAs", Dispatch.Method, new Object[] {htmlfile, new Variant(EXCEL_HTML) }, new int[1]);
Variant f = new Variant(false);
Dispatch.call(excel, "Close", f);
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
app.invoke("Quit", new Variant[] {});
}
}
/**
* /刪除指定文件夾
* @param folderPath 文件夾全路徑
* @param htmlfile 轉換後HTML存放路徑
*/
public void delFolder(String folderPath)
{
try
{
delAllFile(folderPath); //刪除完裡面所有內容
String filePath = folderPath;
filePath = filePath.toString();
java.io.File myFilePath = new java.io.File(filePath);
myFilePath.delete(); //刪除空文件夾
} catch (Exception e) {e.printStackTrace();}
}
/**
* /刪除指定文件夾下所有文件
* @param path 文件全路徑
*/
public boolean delAllFile(String path)
{
boolean flag = false;
File file = new File(path);
if (!file.exists())
{
return flag;
}
if (!file.isDirectory())
{
return flag;
}
String[] tempList = file.list();
File temp = null;
for (int i = 0; i < tempList.length; i++)
{
if (path.endsWith(File.separator))
{
temp = new File(path + tempList[i]);
}
else
{
temp = new File(path + File.separator + tempList[i]);
}
if (temp.isFile())
{
temp.delete();
}
if (temp.isDirectory())
{
delAllFile(path + "/" + tempList[i]);//先刪除文件夾裡面的文件
delFolder(path + "/" + tempList[i]);//再刪除空文件夾
flag = true;
}
}
return flag;
}
}
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
public class TransformFiletoHtml
{
int WORD_HTML = 8;
int WORD_TXT = 7;
int EXCEL_HTML = 44;
/**
* WORD轉HTML
* @param docfile WORD文件全路徑
* @param htmlfile 轉換後HTML存放路徑
*/
public void wordToHtml(String docfile, String htmlfile)
{
ActiveXComponent app = new ActiveXComponent("Word.Application"); // 啟動word
try
{
app.setProperty("Visible", new Variant(false));
Dispatch docs = app.getProperty("Documents").toDispatch();
Dispatch doc = Dispatch.invoke(docs,"Open",Dispatch.Method,new Object[] { docfile, new Variant(false),new Variant(true) }, new int[1]).toDispatch();
Dispatch.invoke(doc, "SaveAs", Dispatch.Method, new Object[] {htmlfile, new Variant(WORD_HTML) }, new int[1]);
Variant f = new Variant(false);
Dispatch.call(doc, "Close", f);
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
app.invoke("Quit", new Variant[] {});
}
}
/**
* EXCEL轉HTML
* @param xlsfile EXCEL文件全路徑
* @param htmlfile 轉換後HTML存放路徑
*/
public void excelToHtml(String xlsfile, String htmlfile)
{
ActiveXComponent app = new ActiveXComponent("Excel.Application"); // 啟動excel
try
{
app.setProperty("Visible", new Variant(false));
Dispatch excels = app.getProperty("Workbooks").toDispatch();
Dispatch excel = Dispatch.invoke(excels,"Open",Dispatch.Method,new Object[] { xlsfile, new Variant(false),new Variant(true) }, new int[1]).toDispatch();
Dispatch.invoke(excel, "SaveAs", Dispatch.Method, new Object[] {htmlfile, new Variant(EXCEL_HTML) }, new int[1]);
Variant f = new Variant(false);
Dispatch.call(excel, "Close", f);
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
app.invoke("Quit", new Variant[] {});
}
}
/**
* /刪除指定文件夾
* @param folderPath 文件夾全路徑
* @param htmlfile 轉換後HTML存放路徑
*/
public void delFolder(String folderPath)
{
try
{
delAllFile(folderPath); //刪除完裡面所有內容
String filePath = folderPath;
filePath = filePath.toString();
java.io.File myFilePath = new java.io.File(filePath);
myFilePath.delete(); //刪除空文件夾
} catch (Exception e) {e.printStackTrace();}
}
/**
* /刪除指定文件夾下所有文件
* @param path 文件全路徑
*/
public boolean delAllFile(String path)
{
boolean flag = false;
File file = new File(path);
if (!file.exists())
{
return flag;
}
if (!file.isDirectory())
{
return flag;
}
String[] tempList = file.list();
File temp = null;
for (int i = 0; i < tempList.length; i++)
{
if (path.endsWith(File.separator))
{
temp = new File(path + tempList[i]);
}
else
{
temp = new File(path + File.separator + tempList[i]);
}
if (temp.isFile())
{
temp.delete();
}
if (temp.isDirectory())
{
delAllFile(path + "/" + tempList[i]);//先刪除文件夾裡面的文件
delFolder(path + "/" + tempList[i]);//再刪除空文件夾
flag = true;
}
}
return flag;
}
}調用JAVA代碼：
Java代碼
public class Test1 {
public static void main(String[] args) {
// TODO Auto-generated method stub
TransformFiletoHtml trans = new TransformFiletoHtml();
trans.wordToHtml("D:\\sinye.doc", "D:\\sinye.html");
}
}
public class Test1 {
public static void main(String[] args) {
// TODO Auto-generated method stub
TransformFiletoHtml trans = new TransformFiletoHtml();
trans.wordToHtml("D:\\sinye.doc", "D:\\sinye.html");
}
} 只寫了一個測試word轉html的，excel轉html的同理，在TransformFiletoHtml類中，寫了兩個方法，一個是刪除文件夾的方法(delFolder())，一個是刪除文件夾下所有文件的方法(delAllFile())。
7. java怎麼講excel轉為html
用POI解析Excel，解析好後轉成xml，在輸出到html就可以了
類似這樣，sheet是工作區，column是行，cell是單元格
不過這樣這樣的話merge過的單元格可能比較難表示
<sheet>
<column>
<cell />
<cell />
</column>
</sheet>
8. 如何用java來將excel文件轉化為html文件問題
可以導入spire.xls.jar（只需導入這一個jar即可），直接下載jar包，然後解壓將lib文件夾下的jar文件導入java程序；如果創建的是Maven程序的話，需要在pom.xml文件中配置maven路徑並指定Free Spire.XLS for Java的依賴，導入，如下配置：
<repositories>
<repository>
<id>com.e-iceblue</id>
<url>http://repo.e-iceblue.cn/repository/maven-public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId> e-iceblue </groupId>
<artifactId>spire.xls</artifactId>
<version>2.2.0</version>
</dependency></dependencies>
完成導入後，參考如下代碼將excel轉為html：
import com.spire.xls.*;
public class ExcelToHtml {
public static void main(String[] args) {
//載入Excel工作表
Workbook wb = new Workbook();
wb.loadFromFile("test.xlsx");
//獲取工作表
Worksheet sheet = wb.getWorksheets().get(0);
//調用方法將Excel保存為HTML
sheet.saveToHtml("ToHtml.html");
}
}
9. java 通過jacob完成excel轉成html格式時報錯：com.jacob.com.ComFailException: Invoke of: SaveAs
建議你參考下PageOffice。