必备资料:
1. jawin.dll(这个文件需要放到win32目录下,系统不需要重启)
2. jawin.jar,jawin-stubs.jar(这两个jar包要copy到自己的web类库中,主要是用来把excel文件格式转换为在web 页面可显示的xml格式)
3. 操作系统必须是windows,并且必须安装了office2003控件
主要功能代码:
package com.topman.util.excel;
import com.topman.util.Util;
import com.topman.util.type.StringUtil;
import com.topman.util.file.FileUtil;
import com.topman.util.web.WebUtil;
import com.topman.util.log.GeneralLogger;
import org.jawin.win32.Ole32;
import org.jawin.DispatchPtr;
import java.io.File;
import java.io.UnsupportedEncodingException;
public class ExcelUtil {
/**
* TODO:下面这个方法,可以直接获取xls文件转换为xml文件的内容,能够直接在web页面以xls形式显示
public static String fetchXmlContentOfXls(String xmlFileAbsPath) throws UnsupportedEncodingException {
if (StringUtil.empty(xmlFileAbsPath)) {
return "";
}
String xml = FileUtil.file2String(xmlFileAbsPath);
String xmlContent = WebUtil.XmltoHtml(xml);
return xmlContent;
}
// TODO: 需要先复制jawin.dll到C:/winnt/system32目录下
// src 为excel文件的路径,dest为输出的xml文件的路径
public static void saveXlsAsXml(String src, String dest) {
final String hintHead = "saveXlsAsXml, ";
GeneralLogger logger = Util.getLogger();
try {
Ole32.CoInitialize();
// String src = "E://work//zsm//jawin_sms//data//2.xls";
// String dest = "E://work//zsm//jawin_sms//data//2.xml";
logger.info(hintHead + "src = " + src + ", dest = " + dest);
logger.info(hintHead + ", check dest existing...");
File f = new File(dest);
if (f.exists()) {
f.delete();
}
logger.info(hintHead + "open excel");
DispatchPtr app = new DispatchPtr("Excel.Application");
logger.info(hintHead + "set visible to false");
app.put("Visible", false);
logger.info(hintHead + "get workbooks");
DispatchPtr books = (DispatchPtr) app.get("Workbooks");
logger.info(hintHead + "open src");
DispatchPtr book = (DispatchPtr) books.invoke("Open", src);
/**
* for the benefit of others, here is the complete list of canstant values for the saveas function :
18=Excel * * *-in
6=Comma-seperated values format
22=Macintosh comma-separated values format
24=MSDOS comma-seperated values format
23=MS Windows comma-separated values format
-4158=O/S boing used comma-separated values format
7=DBase II format
8=DBase III format
11=DBase IV format
9=Data interchange format
16=Excel 2.0 format
27=Excel 2.0 (Far East) format
29=Excel 3.0 format
33=Excel 4.0 format
35=Excel 4.0 Workbook format
39=Excel 5.0 & Excel 97 format
43=Excel 95 & Excel 97 format
44=HTML
26=Excel international add-in
25=Excel international marco
2=symbolic link format
17=template file format
19=Mcintosh test file format
21=MSDOS test file format
36=text printer file (.prn)
20=MS Windows text file format
42=Unicode
45=Web archive format (.mht)
5=Lotus 2.x format
31==Lotus 2.x .all format
30=Lotus 2.x .fmt format
15=Lotus 3.x format
32=Lotus3.x and Lotus 123 for Windows format
38=Lotus 4.0 format
4=MS Works format
-4143=Excel workbook format
28=MS Works (far east) format
34=Quattro Pro for MSDOS format
46=XML format
*/
logger.info(hintHead + "save dest as xml ");
book.invoke("SaveAs", dest, new Integer(46));
logger.info(hintHead + "quit excel");
app.invoke("Quit");
Ole32.CoUninitialize();
} catch (Exception e) {
logger.error(hintHead + "failure, please check the jawin.dll path", e);
}
}
}
public WebUtil{
//TODO:根据需要可以在添加
public static String XmltoHtml(String xml) {
StringBuffer txt = new StringBuffer(xml);
StringBuffer buf = new StringBuffer(100);
for (int i = 0, n = txt.length(); i < n; i++) {
char ch = txt.charAt(i);
if (ch == '&') {
buf.append("&");
} else if (ch == '<') {
buf.append("<");
} else if (ch == '>') {
buf.append(">");
} else if (ch == '/r') {
buf.append(" ");
} else if (ch == '/n') {
buf.append(" ");
} else if (ch == '"') {
buf.append(""");
} else if (ch == '¥') {
buf.append("¥");
} else if (ch == '$') {
buf.append("¥");
} else {
buf.append(ch);
}
}
return new String(buf);
}
}
web页面显示方式:
<object id="Spreadsheet"
classid="CLSID:0002E559-0000-0000-C000-000000000046" width="100%" height="700px">
<param name=DisplayTitleBar value=false>
<param name=Autofit value=true> (注:这个地方如果设置为false,excel显示的宽度可能会出现问题)
<param name=DataType value=XMLData>
<param name=XMLData value="<%=xmlContent%>">
</object>