Java uses jcom to manipulate Office objects _jsp programming

By using COM technology, we can build a lot of application extensions with Microsoft Office applications, but Java developers can't enjoy the convenience they have-unless they have a convenient Java access to COM (java-to-com Bridge). With jcom, you can control almost all COM objects in Java, and it has some powerful helper classes for Excel.

Every time you write an application that uses HTML table styles or Java table objects to display data, you typically need to have the export to Excel feature. Then the problem of headaches arises. How do you implement this function? Can the display in HTML be handled in Office 2003? Not so good! You must also support Office 97!

You can only find a tool that fits your current needs, but then you get more requests. "Can this be done in Word?" Can PowerPoint do that? Can I dial the modem to the remote server and publish the data? What does Java mean by not being able to implement these functions? Java can implement any functionality. "

Thanks to the framework components of Java and COM bridges, it allows you to answer "Yes" when you encounter these situations. The Java-com bridge allows you to operate Windows components as you want--formerly the domain of VB, C + +, and. NET developers. You can move away from end-to-end (end-to-end) COM systems by implementing a Java front-end (front end) that talks to the DCOM backend. At the end of this article, you can use one of the java-to-com bridges: it can be named jcom.

　 Basic knowledge of Excel

Before you start, you need to download the API from the SourceForge Web site first. It contains all the source code for the Java class used by jcom, C + + code, and the compiled DLL for configuring Java and COM jcom. Put this DLL under the/bin/directory of your Java home directory, otherwise there will be a problem. Also, in order to avoid problems, set the JAVA_HOME environment variables correctly. Most of Jcom's documents are currently written in Japanese, but translation work is in progress, so there will be some improvements later.

After the download and installation process is complete, try the code in Listing 1. This code creates a jcom interface to Excel and writes "Hello World" to the first cell. You can see the results as shown in Figure 1. Although jcom is a common COM class library, there are a number of auxiliary classes for Excel, because Excel may be the most common automation COM application. These helper classes can save us a lot of time, and they make jcom a better class library.

Listing 1: Starting with jcom and Excel

Import jp.ne.so_net.ga2.no_ji.jcom.excel8.*;
Import jp.ne.so_net.ga2.no_ji.jcom.*;

public class TestSimple
{
public static void Main (string[] args) throws Exception {
Releasemanager rm = new Releasemanager ();
try {
System.out.println ("EXCEL is starting ...");
ExcelApplication Excel = new ExcelApplication (RM);
Excel. Visible (TRUE);
Excelworkbooks xlbooks = Excel. Workbooks ();
ExcelWorkbook xlbook = Xlbooks.add ();
Excelworksheets xlsheets = Xlbook.worksheets ();
Excelworksheet xlsheet = Xlsheets.item (1);
Excelrange xlrange = Xlsheet.cells ();
Xlrange.item (1,1). Value ("Hello, world!");
}
catch (Exception e) {e.printstacktrace ();}
finally {rm.release ();}
}
}

Figure 1:java The first COM automation using Excel

If you've ever used VB or VBA to automate Excel, you should be familiar with the code in Listing 1. The ExcelApplication class exposes the workbooks () property, which enumerates the workbook (workbook) that is currently open in Excel, and allows you to add or remove workbooks from the running Excel copy. Use. The Add () method increases the workbook, which returns a reference to the workbook.

The workbook is used to save the input data in Excel. The workbook is the basis of the XLS file. A workbook is made up of multiple worksheets (worksheet, with three worksheets in Figure 1, called Sheet1, Sheet2, and Sheet3, respectively). The worksheet is enumerated by the Item property. You can use this property to access a specific worksheet. In Figure 1, Sheet 1 is the first worksheet, so you can use Xlsheets.item (1) to get a reference to it.

Once you have a worksheet, you can use range to manipulate the data on it. Range is a cell or multiple cells. For example, cell A1 can be referenced using range (1,1), and then you can use the Value property to load the data into a cell.

　A more meaningful example

Suppose you want Excel to be the presentation layer for some data in your database. In Java you want to get data through JDBC and display the data in front of Excel. This assumption is good because use cases in complex business logic are already displayed in front of Excel, not to mention the display and interaction of other data items in the workflow. Now that you don't need to understand business logic or analytics, you can use the original spreadsheet and use Automation in Java to "populate the rift between them."

I'll give you a simple example of how this can be done: the content contained in this download is a simple MySQL database that establishes a comparison of sales behavior. It has three tables:

· Sales is the details of the sale, including sales items, quantity, sales price, who sold and the sales area.

· People contains the name and commission of the salesperson.

· Districts contains the name and tax rate of the sales area.

When you generate a report, you need to use the following business logic to figure out the real value:

· Gross sales equals sales items multiplied by sales price plus local tax amount.

· Pure sales equals gross sales minus the pre-tax commission that the salesperson extracts.

These are simple, straightforward computations that can be implemented in Java, but I use them in this article to demonstrate how to write this data to an Excel spreadsheet and have Excel calculate it automatically. For more complex situations (using more complex Excel formulas), the principle is the same.

In addition, the download contains the spreadsheet shown in Figure 2. It is a spreadsheet that is used as a "template" for you to populate the appropriate data.

Figure 2: Excel template for front-end padding of data

With jcom, the following SQL is required to query all sales data in the database:

SELECT sales.id, Sales.description, sales.quantity, Sales.price, Districts.districtname,districts.salestax, People.name,people.commission
From ' Sales ', ' districts ', ' people '
WHERE (sales.district = districts.id) and (Sales.SalesPerson = people.id)

Then use automation (Automation) to write each record to the appropriate column in the Excel spreadsheet. The downloaded content contains the complete code (shown in Listing 2). In the following sections, I will explain to you the Automation section of the code that uses jcom.

Listing 2: Getting the data from the database and loading the code in Excel

First build a reference to Excel
ExcelApplication Excel = new ExcelApplication (RM);
Make it visible
Excel. Visible (TRUE);
Then open the template workbook we'll use
Excelworkbooks xlbooks = Excel. Workbooks ();
ExcelWorkbook xlbook = Xlbooks.open ("C:\sales.xls");
Then get the reference to the scope we will modify
Excelworksheets xlsheets = Xlbook.worksheets ();
Excelworksheet xlsheet = Xlsheets.item (1);
Excelrange xlrange = Xlsheet.cells ();

...
Place the database link and query build code here
...

if (Stmt.execute (strSQL))
{
rs = Stmt.getresultset ();
}
int ncolcount = Rs.getmetadata (). getColumnCount ();
int nrow=1;
int ncol=0;
Looping in a record set
while (Rs.next ())
{
Each row in the recordset is a row in the spreadsheet
nrow++;
for (int i=1;i<=ncolcount;i++)
{
Match DB columns and spreadsheet columns
Ncol = Getexcelcolumn (i);
Find the cell that corresponds to the row and column and set it to the appropriate recordset field
Xlrange.item (Nrow,ncol). Value (Rs.getobject (i). toString ());
}
Fill in the formula
Xlrange.item (nrow,7). Value ("=rc[-2]*rc[-1]");
Xlrange.item (nrow,9). Value ("=rc[-2]*rc[-1]/100");
Xlrange.item (nrow,10). Value ("=rc[-3]+rc[-1]");
Xlrange.item (nrow,11). Value ("=rc[-4]* (100-rc[2)/100)");
}


In the first part of Listing 2, the goal is to get control of the cells that you want to modify. This will cost a certain amount of money.

· First you have to get the objects that represent Excel itself, and with the help of the Jcom helper class, this step is fairly straightforward.

ExcelApplication Excel = new ExcelApplication (RM);
　　
· Next, you want to gain access to the workbook collection. You want to open your own template workbook (the template in this example is in C:sales.xls) and open it in the workbook collection.

Excelworkbooks xlbooks = Excel. Workbooks ();

· Next, you want to open your workbook and get a reference to the workbook collection.

ExcelWorkbook xlbook = Xlbooks.open ("C:\sales.xls");
Excelworksheets xlsheets = Xlbook.worksheets ();

· Finally, you want to get the first worksheet in the collection and define the work scope as the entire worksheet.

Excelworksheet xlsheet = Xlsheets.item (1);
Excelrange xlrange = Xlsheet.cells ();

After you have done this, your Xlrange object will allow you to put values and formulas in the unit. Use Xlrange.item (Nrow,ncol) by looping through the previous recordset (returned from the SQL command submitted to the database). Value ("Whatever") syntax, in which the values in the recordset are inserted into the spreadsheet line by column (whatever from the Recordset). By using Rs.getobject (i), ToString can get the value of column I in the current row. By using Rs.movenext (), the current line moves backwards until the end of the recordset.

You may notice a strange function call in Listing 2:

Ncol = Getexcelcolumn (i);

This is a simple auxiliary function that matches the column number in the recordset to the location where the data resides in Excel. For example, if you look at the above SQL statement again, the commission rate is returned to the 8th column. In a spreadsheet, the column that holds it is the 13th column. This function is used to handle transitions between the two. More complex applications can use named (named) scopes in Excel to match columns, but this is beyond the scope of this article.

Finally, the application enters the formula into the appropriate unit. It is implemented using the R1C1 symbol for Excel, so the corresponding string is used to describe the formula. In Excel, suppose you want to add the value of column A in row 1th to the value of column B, and then put it in column C, and you need to enter the formula "=A1+B1" in column C. If you want to do this in the second line, you can copy and paste the formula and it will automatically be updated to "=A2+B2".

Getting these values from the sample program requires some string manipulation, but it is very straightforward to use "R1C1" related transformations. In the example of this article, when you enter a formula in C1, you do not need to give a "=a1+b1" reference, but instead give a "=rc[-2]+rc[-1]" reference, which means to add the value of the forward two columns to the value of the forward column.

When you move other rows, the value of this formula does not change, so it makes it very easy for us to generate the desired value for the program. This is how I calculate total sales, sales taxes, gross sales, and pure sales, as follows:

Xlrange.item (nrow,7). Value ("=rc[-2]*rc[-1]");
Xlrange.item (nrow,9). Value ("=rc[-2]*rc[-1]/100");
Xlrange.item (nrow,10). Value ("=rc[-3]+rc[-1]");
Xlrange.item (nrow,11). Value ("=rc[-4]* (100-rc[2)/100)");

Running this code will get the spreadsheet shown in Figure 3.

Figure 3: Formula: Excel displays the output information for a database query

Note that this is a good engineering experience not to include formulas in your application because formulas can change at any time, and you definitely don't want to change your code when you change the formula. This approach makes each new data row a copy of the existing data row that contains the formula. The template spreadsheet (Sales.xls) contains fake data and contains formulas in the second column (the first column contains column headings). Therefore, when you populate each row, copy the contents of the line at the front of the database value that you fill in. In this way, the formula is copied into the new data row, and you don't need to include any business logic in the Java code.

Let's take a look at the contents of the Salesreport2.java file (which is also in the download), and you can see that we didn't use the formula described earlier, but instead used the following code at the top of the while loop:

if (nrow>1)
{
String strdest= "A" + (nrow+1);
Excelrange xlrange2copy = Xlsheet.range ("a2:m2");
Xlrange2copy.copy (Xlsheet.range (strdest));
}

In addition to the first line (the first line does not need to be processed, such information is already contained in Sales.xls), this code processes the cells between A2 and M2 and copies them to the nrow+1 identified data row (Nrow is the number of the current row). The reason for adding 1 is that the column headings are computed (the column headings are in the first row).

　　 Beyond Excel

Keep in mind that jcom is used for COM and not just for Excel. You can use this tool to control almost all COM objects in Java. Excel is the most common controlled object, because there are a lot of good helper classes to make it simpler, but all COM components are available. Therefore, it is also straightforward to control other applications, such as Word and PowerPoint.

Here is an example of using Word:

IDispatch wdapp = new IDispatch (rm, "Word.Application");
Wdapp.put ("Visible", New Boolean (true));

Because there is no direct auxiliary class for Word, each part must be implemented by using the IDispatch object (which is also true for the Downlevel Helper class for Excel). The code above will be loaded with a copy of Microsoft Word and made available by the Wdapp object to automate the operation. You can then use word like "put" (set property) or "method" (Invoke method). For example, the above code sets the Visible property to true so that the word application is visible.

If you have to use Windows, there are a large number of COM components that can expand your horizons, which is much more than what Java currently offers. Hardware control via serial and parallel ports can also be implemented in Java, which is just the beginning. After you have jcom in your toolkit, there is no limit.