For this reason I started messing around with the apache project POI. This project has pure java ports of file formats based on Microsoft’s OLE 2 Compound
Document Format. So it has classes for creating excel spreadsheets without having excel installed on the server, which would make the admins happy. So I created this simple class to create and write rows to an excel file.
package com.clr.excel;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelWriter {
private int curRow;
private HSSFWorkbook curWB;
private HSSFSheet curSheet;
public boolean writeExcelHeader(String[] rowValues) {
try {
this.curRow = 0;
this.curWB = new HSSFWorkbook();
this.curSheet = this.curWB.createSheet(“Sheet1”);
//This will write the header row
return(this.writeToExcel(rowValues));
} catch(Exception ex) {
ex.printStackTrace();
return(false);
}
}
public boolean writeToExcel(String[] rowValues) {
try {
HSSFRow row = this.curSheet.createRow(this.curRow);
for(int i = 0; i < rowValues.length; i++) {
row.createCell(i).setCellValue(new HSSFRichTextString(rowValues[i]));
}
this.curRow++;
} catch(Exception ex) {
ex.printStackTrace();
return(false);
}
return(true);
}
public boolean writeExcelFile(String fileName) {
try {
//Auto fit content
for(int i = 0; i < 20; i++) {
this.curSheet.autoSizeColumn((short)i);
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(fileName);
this.curWB.write(fileOut);
fileOut.close();
} catch(Exception ex) {
ex.printStackTrace();
return(false);
}
return(true);
}
}
Again I used LS2J to use this class from my lotusScript agent.
These declarations are needed to use LS2J
Uselsx “*javacon”
Use “ExcelWriter”
Agent to create excel sheet.
Sub Initialize
On Error Goto ErrorHandler
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim js As JAVASESSION
Dim xlWriterClass As JAVACLASS
Dim xlWriterObject As JavaObject
Dim rowValues(1 To 3) As String
Dim folderName As String
Print “Started Running CreateExcel”
folderName = “C:Temp”
‘Initialize column headers
rowValues(1) = “Title”
rowValues(2) = “Date”
rowValues(3) = “Name”
‘Initialize Excel Writer class
Set js = New JAVASESSION
Set xlWriterClass = js.GetClass(“com.clr.excel.ExcelWriter”)
Set xlWriterObject = xlWriterClass.CreateObject
If(Not(xlWriterObject.writeExcelHeader(rowValues)))Then
Print “Error creating excel sheet!”
Exit Sub
End If
Set db = session.CurrentDatabase
Set view = db.GetView(“MainView”)
Set doc = view.GetFirstDocument
Do While(Not(doc Is Nothing))
rowValues(1) = doc.Title(0)
rowValues(2) = doc.selectedDate(0)
rowValues(3) = doc.Name(0)
‘Write metadata
If(Not(xlWriterObject.writeToExcel(rowValues)))Then
Print “Could not write to excel!”
End If
Set doc = view.GetNextDocument(doc)
Loop
Finished:
xlWriterObject.writeExcelFile(folderName & “workbook.xls”)
Exit Sub
ErrorHandler:
Print “Error ” & Error & ” on line: ” & Erl
Resume Finished
End Sub
Enjoy the code here.
Hi,
I enjoy your code its really works thank you.
All do i´m triyng to do the same with word documents but with no success!!! are doing the same? Will you try it out?
Regards
Filipe
Hi Caldeira,
Are you trying to export data from Lotus Notes to a Word document? Are you getting any specific error?
I will create a simple example of doing this with the Apache POI Project and post soon.
Hi Carlos, Thank you for your understanding and answer, i´m sorry i lost your page so just today i could see it.
Yes i have errors and i can post some of my code that i worked to get the word file, its dificul since i dont program a lot in java just lotuscript, javascript a litle to.
well i onnly can read and create a file .doc/.docx but mt error was to write inside it some string/phrase.
I will post soon, i leave a rest a while because now i´m learning were i work smallworld magik programming its diferrent programming like c++ you know gis?
well for now i will return to the POI Vs word.
Thank you beste regards
import org.apache.poi.hwpf.usermodel.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.*;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.hwpf.usermodel.Paragraph;
import org.apache.poi.hwpf.usermodel.Table;
import org.apache.poi.hwpf.usermodel.Section;
import java.util.Map;
import java.util.HashMap;
import java.util.Calendar;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.io.FileOutputStream;
import lotus.domino.*;
import java.util.*;
import java.text.*;
import java.io.*;
import java.lang.*;
import java.util.Date;
import java.io.PrintWriter;
public class Word
{
/*
openDocument
openParagraph
openBody
openSection
openTitle
openSource
writePlainText
closedocument…
*/
private int curRow;
// private POIDocument(dir, filesystem);
// private HSSFWorkbook curWB;
// private HSSFSheet curSheet;
// PrintWriter pw = getAgentOutput(); +"
" // erro
/* public boolean Range(int start, int end, HWPFDocument doc)
{
try {
} catch(Exception ex)
{
ex.printStackTrace();
return(false);
}
} */
public boolean writeWordHeader(String[] rowValues)
{
try {
this.curRow = 0;
// this.curWB = new HSSFWorkbook();
// this.curSheet = this.curWB.createSheet("Sheet1");
//This will write the header row
System.out.println("estou no header n");
return(this.writeToWord(rowValues));
} catch(Exception ex) {
ex.printStackTrace();
return(false);
}
}
public boolean writeToWord(String[] rowValues) {
System.out.println("valor da linha: ");
System.out.println("valor: " + rowValues);
try {
// HSSFRow row = this.curSheet.createRow(this.curRow);
for(int i = 0; i < rowValues.length; i++) {
// row.createCell(i).setCellValue(new HSSFRichTextString(rowValues[i]));
System.out.println( "linha: " + rowValues[i]);
}
// this.curRow++;
} catch(Exception ex) {
ex.printStackTrace();
return(false);
}
return(true);
}
public boolean writeWordFile(String fileName)
{
System.out.println("filename: " + fileName);
try {
//Auto fit content
for(int i = 0; i < 20; i++) {
// this.curSheet.autoSizeColumn((short)i);
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(fileName);
System.out.println("nome: " + fileOut);
// this.curWB.write(fileOut);
fileOut.close();
} catch(Exception ex) {
ex.printStackTrace();
return(false);
}
return(true);
}
}
this is a earlier code that have various errors i will try to put some good code just a example for you analyse
thanks
HI Carlose,
I need to move all our reports to this Apache POI format.
Can I have some smaple code for editing an exisitng excel file, also adding the new worksheet in excel etc.
Thans in advance,
Annie