Business requirements are as follows:
1. Convert Excel uploaded by users to HTML for page display (Excel document format is optional)
2. Convert Excel uploaded by users to PDF for download (no page change, only on one PDF)
In response to the above requirements, you may ask, why not use POI to manipulate office documents?
Answer: Because the operation is very troublesome. (Unless you format Excel and don't need it)
In fact, through demand discovery, this is not the "save as" function of office software? Is it? So is there anything that can directly invoke the existing "save as" function of office software? That's true. That's the com component of office. After a search, it was found that Java could use jacob to implement com component calls. In an instant, the function becomes extraordinarily simple. What? You don't know what a com component is? Just ask Du Niang. There is no explanation here.
Although there are many similar articles on the Internet, jacob is a long-standing project. Sweat. But most articles are very single, just tell you the most basic implementation. Say nothing. I don't know how to customize it, and I don't know where the constants are coming from (PS: The code will give the official API). That's why I have this article. One is for your own use. The second is to tell you what you need.
There are some limitations in using COM components. Please note that:
1. The server must be a windows system and install office software. PS: How does the software call its functions?
2. Different versions of Jacob need to correspond to different jdk versions, otherwise the JVM will collapse directly. Give it a try
jacob download address: https://sourceforge.net/projects/jacob-project/
dll files can be placed in the project root directory or JDK bin directory or WindowsSystem32 or WindowsSysWOW64 target or tomcat/bin directory
The author's development environment is as follows:
Server: Server 2008
office Version: office 2007
Development environment: JDK 1.8
The jacob version is: 1.9
Code implementation is as follows: specific call parameters can refer to microsoft's official network, although it is vba -!
Official API: https://docs.microsoft.com/zh-cn/office/vba/api/overview/excel/object-model
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
/**
* @author Code Rookie (qqq: 969422014)
**/
public class JacobUtil
{
//Constant definition API:https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlfileformat
public static final int EXCEL_HTML = 44;
public static final int EXCEL_PDF = 57;
public static void main(String [] args)
{
excelToPdf("D:\\1.xlsx","D:\\1.pdf");
}
/**
* EXCEL Turn pdf
* @param xlsfile EXCEL File full path
* @param pdffile pdf storage path after conversion
*/
public static void excelToPdf(String xlsfile, String pdffile) {
// Start excel
ActiveXComponent app = new ActiveXComponent("Excel.Application");
try {
// Setting excel invisible
app.setProperty("Visible", new Variant(false));
// Disable macros
app.setProperty("AutomationSecurity", new Variant(3));
// Get a worksheet
Dispatch excels = app.getProperty("Workbooks").toDispatch();
// Open excel file
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbooks.open.
Dispatch excel = Dispatch.invoke(excels,"Open",Dispatch.Method,new Object[] {
xlsfile,
new Variant(false),
new Variant(false)
},new int[9]).toDispatch();
// Get the sheet set in the excel table
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.worksheets
Dispatch sheets = Dispatch.call(excel, "Worksheets").toDispatch();
// Get the first sheet in the sheet collection
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.worksheets.item s.
Dispatch sheet = Dispatch.call(sheets, "Item", new Integer(1)).toDispatch();
// Get the page setup object for the first sheet
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.pagesetup
Dispatch pageSetup = Dispatch.call(sheet, "PageSetup").toDispatch();
// Set the excel table to the size of A3
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.pagesetup.papersize
Dispatch.put(pageSetup, "PaperSize", new Integer(8));//A3 is 8, A4 is 9, A5 is 11, etc.
// Scale to 100 or false
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.pagesetup.zoom
Dispatch.put(pageSetup, "Zoom", false);
// All listed as one page (1 or false)
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.pagesetup.fittopageswide
Dispatch.put(pageSetup, "FitToPagesWide", 1);
// Set up the upper margin
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.pagesetup.topmargin
Dispatch.put(pageSetup, "TopMargin", 0);
// margin-right
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.pagesetup.rightmargin
Dispatch.put(pageSetup, "RightMargin", 0);
// Set the left margin
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.pagesetup.leftmargin
Dispatch.put(pageSetup, "LeftMargin", 0);
// Save to temporary file as pdf format
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbook.exportasfixed format
Dispatch.invoke(excel,"ExportAsFixedFormat",Dispatch.Method,new Object[]{
new Variant(0), // PDF format = 0 constant description: https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlfixed format type
pdffile,
new Variant(0) // 0 = standard (the generated PDF image will not blur) 1 = minimum file (the generated PDF image is messy)
},new int[1]);
// Close excel
Dispatch.call(excel, "Close", new Variant(false));
} catch (Exception e) {
e.printStackTrace();
} finally {
// Close excel program
app.invoke("Quit", new Variant[] {});
}
}
/**
* EXCEL Translating HTML
* @param xlsfile EXCEL File full path
* @param htmlfile Transformed HTML Storage Path
*/
public static void excelToHtml(String xlsfile, String htmlfile) {
// Start excel
ActiveXComponent app = new ActiveXComponent("Excel.Application");
try {
// Setting excel invisible
app.setProperty("Visible", new Variant(false));
Dispatch excels = app.getProperty("Workbooks").toDispatch();
// Open excel file
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbooks.open.
Dispatch excel = Dispatch.invoke(
excels,
"Open",
Dispatch.Method,
new Object[] { xlsfile, new Variant(false),
new Variant(true) }, new int[1]).toDispatch();
// Save to temporary file as html format
// API information: https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbook.saveas
Dispatch.invoke(excel, "SaveAs", Dispatch.Method, new Object[] {
htmlfile,
new Variant(EXCEL_HTML) // Type constants: https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlfileformat
}, new int[1]);
Variant f = new Variant(false);
// Close excel documents
Dispatch.call(excel, "Close", f);
} catch (Exception e) {
e.printStackTrace();
} finally {
// Exit excel program
app.invoke("Quit", new Variant[] {});
}
}
}
For other requirements, please refer to: https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlfileformat
Then modify the following code:
Dispatch.invoke(excel, "SaveAs", Dispatch.Method, new Object[] {
htmlfile,
new Variant(EXCEL_HTML)// Constants needed here
}, new int[1]);