This example shows you how to work with Excel templates using J-Integra®. You will create and save an Excel expense template, and then open it, copy it to a new expense workbook, fill it out, save it, and print it out locally or remotely.
You will use J-Integra® to interact with the objects, properties and methods exposed by Microsoft Excel either locally or remotely. There are two sections in this sample: The first section shows how to create and save an Excel expense template--this section is based on he sample code JCreateExpenseTemplate.java, which is included in the installation.The second section shows how to open and copy an existing Excel expense template to a new Excel expense workbook, then fill it out, do calculation, save it, and print it out--this section is based on the code sample JUseExpenseTemplate.java, also included in the installation.
You need one computer running Microsoft Windows and Microsoft Office if you want to run this example locally. You need two computers if you want to run this example remotely--in this case at least one computer has to run Microsoft Windows and Microsoft Office. The computer running Microsoft Windows and Microsoft Office is referred to as the Office server. The computer running this sample is referred to as the Office client. Office server and Office client can be the same computer--in this case, we do not need to configure DCOM.
Install Microsoft Office (Microsoft Office 2000 is recommended.) on the Office server.
Download and install the Java Developers Kit on the Office server and the Office client.
Download and install J-Integra® on the Office server and the Office client.
Read the Microsoft Excel 2000 Java Programmer's Guide.
Ensure that you have installed the J-Integra® license.
This example assumes that you run this example on one Microsoft Windows 2000 platform without configuring DCOM, and install:
JDK under C:\jdk1.3.1\bin
Microsoft Excel is installed under C:\Program Files\Microsoft Office\Office
J-Integra® is installed under C:\jintegra
If the above directories are different from your installation, please modify your code and configuration for running this example.
In this example, you will create an Excel expense template in jintegra\examples\java-excel\JUseExpenseTemplate\Expense.xls, and use it to fill out an Excel expense workbook in jintegra\examples\java-excel\JUseExpenseTemplate\TestExpense.xls.
If necessary, configure the environment for JDK according to the JDK documentation.
Next, set up the path for the J-Integra®. To set the PATH environment variable in the J-Integra® bin directory, type this command at the command prompt:
set PATH=%PATH%;C:\jintegra\bin
set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\jintegra.jar; set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\excel2000.jar;Note: if you have Excel 97 on your computer, replace the second line with:
set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\excel97.jar;if you have Excel XP on your computer, replace the second line with:
set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\excelXP.jar;
Create the file JCreateExpenseTemplate.java by copying and pasting the following code from your Web browser, and save this file in jintegra\examples\java-excel\JCreateExpenseTemplate directory. This file is included in your jintegra\examples\java-excel\JCreateExpenseTemplate directory when you download J-Integra®.
import excel.*;
import java.io.*;
import java.util.*;
public class JCreateExpenseTemplate {
public JCreateExpenseTemplate() {
}
public static void main(String[] args) {
try {
JCreateExpenseTemplate myInstance = new JCreateExpenseTemplate();
String strHostName = "127.0.0.1";
String strUserID = null;
String strPassword = null;
String strMSOfficeHost = "127.0.0.1";
// set command line parameters
if (args.length == 4) {
strHostName = args[0];
strUserID = args[1];
strPassword = args[2];
strMSOfficeHost = args[3];
} else if (args.length > 0) {
System.out.println(
"Usage: java JCreateExpenseTemplate [hostname] [username] [password] [excelhost]");
System.exit(1);
}
String strExcelFilename = "C:\\jintegra\\examples\\java-excel\\JCreateExpenseTemplate";
try {
File f = new File("");
strExcelFilename = f.getAbsolutePath();
} catch (Exception ex) {
}
strExcelFilename += "\\..\\JUseExpenseTemplate\\Expense.xls";
myInstance.createExpenseTemplate(
strHostName,
strUserID,
strPassword,
strMSOfficeHost,
strExcelFilename,
"Template",
"Intrinsyc Software, Inc",
XlSaveAsAccessMode.xlShared,
true,
false
);
} catch(Exception e) {
e.printStackTrace();
} finally {
// Release all remote objects that haven't already been garbage collected.
com.linar.jintegra.Cleaner.releaseAll();
}
}
/**
* This function creates an Excel expense template on either local machine
* or remote machine (By DCOM) which runs Microsoft Office.
* @param strDomainName - [String] the domain name or host name for DCOM
* authentication, null means localhost. You may input host
* name for the machine installing and running Microsoft
* Office. This parameter will be ignored when strUserID is null.
* @param strUserID - [String] the user ID for the domain name or host name
* for DCOM authentication, null means the parameters of
* strDomainName and strUserPassword will be ignored, i.e. the
* sample will run at the same machine as the
* machine installing and running Microsoft Office.
* @param strUserPassword - [String] the password for the domain name or host name
* for DCOM authentication, null means empty password. This
* parameter will be ignored when strUserID is null.
* @param strMSOfficeHost - [String] the host name for the host running
* Microsoft Office in for DCOM authentication,
* null means localhost. This parameter will be ignored when
* strUserID is null.
* @param strWorkbookName - [String] The template file name of the new workbook
* @param strWorksheetName - [String] The worksheet name of template workbook
* @param strCompanyName - [String] The company name displayed on the
* expense tempalte.
* @param iOpeningMode - [int] The access mode when new template file is saved.
* Can be one of the following constants: XlSaveAsAccessMode.xlShared
* (shared list), XlSaveAsAccessMode.xlExclusive (exclusive mode),
* or XlSaveAsAccessMode.xlNoChange (don't change the access mode).
* @param bExcelAppVisible - [boolean] Indicates if showing the running Microsoft
* Office as visible window when this application is running. If it
* is false, Microsoft Office will run in the background.
* @param bExcelAppDisplayAlerts - [boolean] Indicates if display alert
* dialog box when necessary in running this application. If this
* is true, the alert dialog box will be displayed and
* require user's interaction.
*/
public void createExpenseTemplate(
String strDomainName,
String strUserID,
String strUserPassword,
String strMSOfficeHost,
String strWorkbookName,
String strWorksheetName,
String strCompanyName,
int iOpeningMode,
boolean bExcelAppVisible,
boolean bExcelAppDisplayAlerts) {
try {
int i = 0;
int iTemp = 0;
Integer iTemp2 = null;
double dTemp = 0;
Double dTemp2 = null;
Application xlApp = null;
Workbooks workbooks = null;
Workbook wb = null;
Sheets worksheets = null;
Worksheet ws = null;
Range rg = null;
Font ft = null;
Borders bds = null;
Border bd = null;
//////////////////////////////////////////////
// DCOM authentication
if( strUserID != null ) {
if (strDomainName == null) {
strDomainName = "127.0.0.1";
}
if (strUserPassword == null) {
strUserPassword = "";
}
com.linar.jintegra.AuthInfo.setDefault(
strDomainName,
strUserID,
strUserPassword);
}
//////////////////////////////////////////////
// operating Application object
if (strMSOfficeHost != null) {
xlApp = new Application( strMSOfficeHost );
} else {
xlApp = new Application();
}
xlApp.setVisible( bExcelAppVisible );
xlApp.setDisplayAlerts( bExcelAppDisplayAlerts );
/////////////////////////////////////////////
// operating Workbook object
workbooks = xlApp.getWorkbooks();
wb=workbooks.add( null );
/////////////////////////////////////////////
// Operating Worksheets and Worksheet objects
worksheets = wb.getWorksheets();
iTemp = worksheets.getCount();
iTemp2 = new Integer( 1 );
while( iTemp > 1 ) {
ws = new Worksheet( worksheets.getItem( iTemp2 ) );
ws.delete();
iTemp--;
}
ws = new Worksheet( worksheets.getItem( iTemp2 ) );
ws.setName( strWorksheetName );
/////////////////////////////////////////////
// Operating Range object
// basic width
rg = ws.getRange( "B:B", null );
rg.clearContents();
dTemp2 = (Double)rg.getColumnWidth();
dTemp = dTemp2.doubleValue();
dTemp *= 4;
dTemp2 = new Double( dTemp );
rg.setColumnWidth( dTemp2 );
// title
rg = ws.getRange( "D1", null );
drawTitle(
"EXPENSE REPORT",
rg,
1.2,
true);
// light grids
rg = ws.getRange( "A11:J28,F29:J29", null );
bds = rg.getBorders();
bds.setLineStyle( new Integer( XlLineStyle.xlContinuous ) );
// heavy borders
rg = ws.getRange( "A10:J10,C29:J29,J30:J32", null );
bds = rg.getBorders();
bds.setLineStyle( new Integer( XlLineStyle.xlContinuous ) );
Integer iWeight = (Integer)bds.getWeight();
i = iWeight.intValue();
i++;
iWeight = new Integer( i );
bds.setWeight( iWeight );
rg = ws.getRange( "A11:A28", null );
bds = rg.getBorders();
bds.setLineStyle( new Integer( XlLineStyle.xlContinuous ) );
bd = bds.getItem( XlBordersIndex.xlEdgeLeft );
bd.setWeight( iWeight );
rg = ws.getRange( "J11:J28", null );
bds = rg.getBorders();
bds.setLineStyle( new Integer( XlLineStyle.xlContinuous ) );
bd = bds.getItem( XlBordersIndex.xlEdgeRight );
bd.setWeight( iWeight );
rg = ws.getRange( "A28:B28", null );
bds = rg.getBorders();
bds.setLineStyle( new Integer( XlLineStyle.xlContinuous ) );
bd = bds.getItem( XlBordersIndex.xlEdgeBottom );
bd.setWeight( iWeight );
// basic values
rg = ws.getRange( "A10:J10", null );
rg.setHorizontalAlignment( new Integer( XlHAlign.xlHAlignCenter ) );
Object[] newValue = {
"Date", "Description", "Entertain", "Air",
"Hotel", "Meals", "Transport", "Fuel", "Other", "Total"
};
rg.setValue( newValue ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "B29", null );
rg.setHorizontalAlignment( new Integer( XlHAlign.xlHAlignRight ) );
rg.setValue( "TOTALS" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "I30:I32", null );
rg.setHorizontalAlignment( new Integer( XlHAlign.xlHAlignRight ) );
Object[][] newValue2 = {
{ "Total" },
{ "Cash received from Company (ADVANCE)" },
{ "Balance owing (to)/from Company" }
};
rg.setValue( newValue2 ); //Use setValue2 for Excel XP/2003
// formula
setExpenseTotalFormula2(
ws,
'C',
'I' );
setExpenseTotalFormula(
ws,
11,
28);
rg = ws.getRange( "J29", null );
rg.setFormula( "=SUM(C29:I29)" );
rg = ws.getRange( "J30", null );
rg.setFormula( "=SUM(C29:I29)" );
rg = ws.getRange( "J32", null );
rg.setFormula( "=J30-J31" );
// Other information
rg = ws.getRange( "A2", null );
rg.setValue( strCompanyName ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A4", null );
rg.setValue( "DATE" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "C4", null );
rg.setValue( "PERIOD COVERED" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "F4", null );
rg.setValue( "TO" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "I4", null );
rg.setValue( "CURRENCY" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A6", null );
rg.setValue( "NAME" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "E6", null );
rg.setValue( "EMPLOYEE NUMBER" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "H6", null );
rg.setValue( "DEPARTMENT" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A8", null );
rg.setValue( "PURPOSE OF TRIP & DESTINATION" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A30", null );
rg.setValue( "[SIGNATURE]" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A31", null );
rg.setValue( "Employee" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A33", null );
rg.setValue( "Dept" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A35", null );
rg.setValue( "Acct Dept" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A2", null );
ft = rg.getFont();
ft.setItalic( new Boolean( true ) );
rg = ws.getRange( "A4,C4,F4,I4,A6,E6,H6,A8,A30,A31,A33,A35", null );
ft = rg.getFont();
ft.setBold( new Boolean( true ) );
rg = ws.getRange( "F4", null );
rg.setHorizontalAlignment( new Integer( XlHAlign.xlHAlignCenter ) );
rg = ws.getRange( "I4,E6,H6", null );
rg.setHorizontalAlignment( new Integer( XlHAlign.xlHAlignRight ) );
rg = ws.getRange( "B4,E4,G4,J4,B6,F6,I6,J6,C8:J8,B31,B33,B35", null );
drawBottomBorders(
rg,
1);
rg.setHorizontalAlignment( new Integer( XlHAlign.xlHAlignLeft ) );
/////////////////////////////////////////////////////////
// print setting
PageSetup ps=ws.getPageSetup();
ps.setOrientation(XlPageOrientation.xlLandscape);
/////////////////////////////////////////////////////////
// save
wb.setSaved( true );
wb.saveAs(
strWorkbookName,
null,
null,
null,
null,
null,
iOpeningMode,
null,
null,
null,
null);
// clear up
wb.close( new Boolean( true ), null, null );
workbooks.close();
xlApp.quit();
} catch(Exception e) {
e.printStackTrace();
} finally {
}
}
/**
* Helper function to create the title of Expense template.
*
* @param strTitle - [String] title text string
* @param rgTarget - [Range] the Range object where you will write
* the title string.
* @param dAddedRowHeightFactor - [double] The distance between
* default row height and expected row height.
* @param bSetBold - [boolean] indicates if set title
* text string to bold.
*/
public static void drawTitle(
String strTitle,
Range rgTarget,
double dAddedRowHeightFactor,
boolean bSetBold) {
try {
double dTemp = 0;
Double dTemp2 = null;
rgTarget.clearContents();
dTemp2 = ( Double )rgTarget.getRowHeight();
dTemp = dTemp2.doubleValue();
dTemp *= dAddedRowHeightFactor;
dTemp2 = new Double( dTemp );
rgTarget.setRowHeight( dTemp2 );
Font ft = rgTarget.getFont();
ft.setBold( new Boolean( bSetBold ) );
dTemp2 = ( Double )ft.getSize();
dTemp = dTemp2.doubleValue();
dTemp++;
dTemp2 = new Double( dTemp );
ft.setSize( dTemp2 );
rgTarget.setValue( strTitle ); //Use setValue2 for Excel XP/2003
} catch(Exception e) {
e.printStackTrace();
} finally {
}
}
/**
* Helper function to draw the bottom borders of Expense template.
*
* @param rgTarget - [Range] The Range object represents the area
* where the bottom borders will be drawn.
* @param iAddedWeight - [int] The distance between default line
* weight and expected line weight.
*/
public static void drawBottomBorders(
Range rgTarget,
int iAddedWeight) {
try {
Borders bds = rgTarget.getBorders();
Integer iLineStyle = new Integer( XlLineStyle.xlContinuous );
Integer iWeight = (Integer)bds.getWeight();
int i = iWeight.intValue();
i += iAddedWeight;
iWeight = new Integer(i);
Border bd = bds.getItem( XlBordersIndex.xlEdgeBottom );
bd.setLineStyle( iLineStyle );
bd.setWeight( iWeight );
} catch(Exception e) {
e.printStackTrace();
} finally {
}
}
/**
* Helper function to set up formula for a group of ranges
*
* @param ws - [Worksheet] The worksheet object
* @param iStartRow - [int] starting raw for setting up formula
* @param iEndRow - [int] ending raw for setting up formula
*/
public static void setExpenseTotalFormula(
Worksheet ws,
int iStartRow,
int iEndRow) {
try {
String strRange = null;
String strFormula = null;
Range rgTarget = null;
int i = 0;
for( i = iStartRow; i <= iEndRow; i++ ) {
strRange = "J" + i;
strFormula = "=SUM(C" + i + ":I" + i + ")";
rgTarget = ws.getRange( strRange, null );
rgTarget.setFormula( strFormula );
}
} catch( Exception e ) {
e.printStackTrace();
} finally {
}
}
/**
* Helper function to set up formula for a group of ranges
*
* @param ws - [Worksheet] The worksheet object
* @param cStartColumn - [char] starting column for setting up formula
* @param cEndColumn - [char] ending column for setting up formula
*/
public static void setExpenseTotalFormula2(
Worksheet ws,
char cStartColumn,
char cEndColumn) {
try {
String strRange = null;
String strFormula = null;
Range rgTarget = null;
char i = 0;
for( i = cStartColumn; i <= cEndColumn; i++ ) {
strRange = i + "29";
strFormula = "=SUM(" + i + "11:" + i + "28)";
rgTarget = ws.getRange( strRange, null );
rgTarget.setFormula( strFormula );
}
} catch(Exception e) {
e.printStackTrace();
} finally {
}
}
}
|
To compile the Java sample code, type the command
javac JCreateExpenseTemplate.java
The Java sample code will create the JCreateExpenseTemplate.class in the C:\jintegra\examples\java-excel\JCreateExpenseTemplate directory.
Ensure you have configured DCOM for remote access.
Go to jintegra\examples\JCreateExpenseTemplate and run the example by typing this command at the command prompt:
java JCreateExpenseTemplate
Microsoft Excel opens automatically, and an empty worksheet is filled in with the template data.
In jintegra\examples\java-excel\JUseExpenseTemplate, you will find Expense.xls, which is the expense template created by JCreateExpenseTemplate. Double-click the file to open it.
If you get an error like "AutomationException: 0x5 - Access is denied," then make sure that the bin directory is in your PATH so that it can use native code to pick up your login identity.
If you don't want J-Integra® to ascertain your login identity using native code, or if you are not running the Java code under Windows, then see the section on configuring DCOM access to Excel in this document: Remote Access to the Excel Workbook.
Create the file JUseExpenseTemplate.java by copying and pasting the following code from your Web browser, and save this file in jintegra\examples\java-excel\JUseExpenseTemplate directory. This file is included in your jintegra\examples\java-excel\JUseExpenseTemplate directory when you download J-Integra®.
import excel.*;
import java.io.*;
import java.util.*;
public class JUseExpenseTemplate {
public JUseExpenseTemplate() {
}
public static void main(String[] args) {
try {
JUseExpenseTemplate myInstance = new JUseExpenseTemplate();
String strHostName = "127.0.0.1";
String strUserID = null;
String strPassword = null;
String strMSOfficeHost = "127.0.0.1";
// set command line parameters
if (args.length == 4) {
strHostName = args[0];
strUserID = args[1];
strPassword = args[2];
strMSOfficeHost = args[3];
} else if (args.length > 0) {
System.out.println(
"Usage: java JUseExpenseTemplate [hostname] [username] [password] [excelhost]");
System.exit(1);
}
String strDefaultPath = "C:\\jintegra\\examples\\java-excel\\JUseExpenseTemplate";
try {
File f = new File("");
strDefaultPath = f.getAbsolutePath();
} catch (Exception ex) {
}
myInstance.testExpenseTemplate(
strHostName,
strUserID,
strPassword,
strMSOfficeHost,
strDefaultPath+ "\\Expense.xls",
"Template",
strDefaultPath + "\\TestExpense.xls",
"MyExpense",
XlSaveAsAccessMode.xlShared,
true,
false);
} catch(Exception e) {
e.printStackTrace();
} finally {
// Release all remote objects that haven't already been garbage collected.
com.linar.jintegra.Cleaner.releaseAll();
}
}
/**
* This function opens an Excel expense template on either local machine
* or remote machine (By DCOM) which runs Microsoft Office,
* fills it out, saves it and prints it out.
* @param strDomainName - [String] the domain name or host name for DCOM
* authentication, null means localhost. You may input host
* name for the machine installing and running Microsoft
* Office. This parameter will be ignored when strUserID is null.
* @param strUserID - [String] the user ID for the domain name or host name
* for DCOM authentication, null means the parameters of
* strDomainName and strUserPassword will be ignored, i.e. the
* sample will run at the same machine as the
* machine installing and running Microsoft Office.
* @param strUserPassword - [String] the password for the domain name or host name
* for DCOM authentication, null means empty password. This
* parameter will be ignored when strUserID is null.
* @param strMSOfficeHost - [String] the host name for the host running
* Microsoft Office in for DCOM authentication,
* null means localhost. This parameter will be ignored when
* strUserID is null.
* @param strTemplateWorkbookFilePath - [String] The full path (directory plus
* file name ) of the template file.
* @param strTemplateWorksheetName - [String] The target worksheet in the
* template workbook.
* @param strWorksheetName - [String] The worksheet name of template workbook
* @param strResultWorkbookFilePath - [String] The full path (directory plus
* file name ) of result workbook.
* @param strResultWorksheetName - [String] The worksheet name of result workbook
* @param iOpeningMode - [int] The access mode when new template file is saved.
* Can be one of the following constants: XlSaveAsAccessMode.xlShared
* (shared list), XlSaveAsAccessMode.xlExclusive (exclusive mode),
* or XlSaveAsAccessMode.xlNoChange (don't change the access mode).
* @param bExcelAppVisible - [boolean] Indicates if showing the running Microsoft
* Office as visible window when this application is running. If it
* is false, Microsoft Office will run in the background.
* @param bExcelAppDisplayAlerts - [boolean] Indicates if display alert
* dialog box when necessary in running this application. If this
* is true, the alert dialog box will be displayed and
* require user's interaction.
*/
public static void testExpenseTemplate(
String strDomainName,
String strUserID,
String strUserPassword,
String strMSOfficeHost,
String strTemplateWorkbookFilePath,
String strTemplateWorksheetName,
String strResultWorkbookFilePath,
String strResultWorksheetName,
int iOpeningMode,
boolean bExcelAppVisible,
boolean bExcelAppDisplayAlerts) {
try {
Application xlApp = null;
Workbooks workbooks = null;
Workbook wb = null;
int iTemp = 0;
Integer iTemp2 = null;
Sheets worksheets = null;
Worksheet ws = null;
String strTemp = null;
Range rg = null;
//////////////////////////////////////////////
// DCOM authentication
if (strUserID != null) {
if(strDomainName == null) {
strDomainName = "127.0.0.1";
}
if (strUserPassword == null) {
strUserPassword = "";
}
com.linar.jintegra.AuthInfo.setDefault(
strDomainName,
strUserID,
strUserPassword);
}
//////////////////////////////////////////////
// operating Application object
if (strMSOfficeHost != null) {
xlApp = new Application(strMSOfficeHost);
} else {
xlApp = new Application();
}
xlApp.setVisible( bExcelAppVisible );
xlApp.setDisplayAlerts( bExcelAppDisplayAlerts );
// copy template to objective workbook rather than work directly
// on template workbook, which may increase efficiency in multiple
// user situation.
workbooks = xlApp.getWorkbooks();
wb = workbooks.open(
strTemplateWorkbookFilePath,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null);
wb.saveAs(
strResultWorkbookFilePath,
null,
null,
null,
null,
null,
iOpeningMode,
null,
null,
null,
null);
wb.close( new Boolean( true ), null, null );
workbooks.close();
// open objective workbook
workbooks=xlApp.getWorkbooks();
wb=workbooks.open(
strResultWorkbookFilePath,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null);
/////////////////////////////////////////////
// Operating Worksheets and Worksheet objects
worksheets = wb.getWorksheets();
iTemp = worksheets.getCount();
while( iTemp > 0 ) {
iTemp2 = new Integer( iTemp );
ws = new Worksheet( worksheets.getItem( iTemp2 ) );
strTemp = ws.getName();
if( strTemplateWorksheetName.equals( strTemp ) ) {
break;
}
iTemp--;
}
ws = new Worksheet( worksheets.getItem( iTemp2 ) );
ws.setName( strResultWorksheetName );
////////////////////////////////////////////
// filling data
rg = ws.getRange( "C11:I16", null );
// New contents for the range -- notice the standard Java types
double[][] dValue = {
{ 11.01, 541.02, 356, 34.04, 123.11, 3.09, 34.65 },
{ 56.9, 788.02, 454, 76.04, 209, 0, 0 },
{ 0, 788.02, 454, 45, 63.7, 46.76, 0 },
{ 0, 0, 0, 0, 0, 0, 280.08 },
{ 0, 0, 0, 0, 0, 0, 34.08 },
{ 0, 0, 0, 0, 0, 0, 450.08 },
};
rg.setValue( dValue ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "A11:B16", null );
String[][] strValue = {
{ "1/7/2001", "e-business meeting in Redmond, CA" },
{ "1/11/2001", "e-commerce conference in San Jose, CA" },
{ "2/4/2001", "Java2C# meeting in Redmond, CA" },
{ "3/8/2001", "buy books" },
{ "4/18/2001", "buy anti-virus software" },
{ "5/22/2001", "buy C# for Unix software" },
};
rg.setValue( strValue ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "B4", null );
rg.setValue( "7/24/2001" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "E4", null );
rg.setValue( "1/7/2001"); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "G4", null );
rg.setValue( "5/22/2001"); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "J4", null );
rg.setValue( "USD" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "B6", null );
rg.setValue( "Bell Gates" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "F6", null );
rg.setValue( "001" ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "I6", null );
rg.setValue( "System Arch." ); //Use setValue2 for Excel XP/2003
rg = ws.getRange( "C8", null );
rg.setValue( "Business trips and buying tech resources." ); //Use setValue2 for Excel XP/2003
/////////////////////////////////////////////////////////
// print setting
PageSetup ps = ws.getPageSetup();
ps.setOrientation( XlPageOrientation.xlLandscape );
// shrink the worksheet size to 90% in printing. Default is 100%
ps.setZoom(new Integer(90));
/////////////////////////////////////////////////////////
// print out
ws.printOut(null,null,null,null,null,null,null,null);
////////////////////////////////////////////
// saving
wb.setSaved( true );
wb.save();
/////////////////////////////////////////////////////////
// Clearup
wb.close( new Boolean( true ), null, null );
workbooks.close();
xlApp.quit();
} catch(Exception e) {
e.printStackTrace();
} finally {
}
}
}
|
Go to jintegra\examples\java-excel\JUseExpenseTemplate Use the following command to compile the sample:
javac -J-mx32m -J-ms32m JUseExpenseTemplate.java
The -J parameters make more memory available to the compiler than it would normally have, since there are a lot of generated files in this example.
The Java sample code will create JUseExpenseTemplate.class in the C:\jintegra\examples\java-excel\JUseExpenseTemplate directory.
Ensure you have configured DCOM for remote access.
Run the example by typing this command at the command prompt:
java JUseExpenseTemplate
Microsoft Excel opens automatically. The expense template generated in the previous section, Expense.xls, will be opened, data is filled in, and a new worksheet is generated.
To look at the results, find TestExpense.xls in the C:\jintegra\examples\java-excel\JUseExpenseTemplate directory. This is a sample expense workbook generated from the expense template created by JCreateExpenseTemplate.
If a printer has been set up on the computer, TestExpense.xls will be printed out when the application is running. You can also open TestExpense.xls manually by double-clicking it.
If you get an error like "AutomationException: 0x5 - Access is denied," then make sure that the bin directory is in your PATH so that it can use native code to pick up your login identity.
If you don't want J-Integra® to ascertain your login identity using native code, or if you are not running the Java code under Windows, then see the section on configuring DCOM access to Excel in this document: Remote Access to the Excel Workbook.
When running under Windows, J-Integra® can use native code to pick up your current login identity. If you would rather that J-Integra® did not do this, or if your Java client is on another remote machine of any OS, then you must configure DCOM access to Excel or to other COM components, and you must specify the NT domain, user and password to be used by J-Integra®.
Proceed to Configuring DCOM for Remote Access
For example, to run the JCreateExpenseTemplate.java on a remote UNIX box: