Overview
There are two possible causes for this error. Either a forward-only result set is being used on multiple rows or an empty forward-only result set is being used in a version before 6.0.2.
Cause 1 – Reusing data markers on different rows
If you use a forward-only cursor when accessing your data source (a DataReader in .NET, or a forward-only ResultSet in Java), all the data markers with the same data source must be on the same row in a single worksheet. ExcelTemplate populates a spreadsheet row-by-row. When it encounters a row containing data markers from a particular data source, it will fill in the data from the entire result set. Then it will look for data markers in the next row (which may have been pushed down by the new inserted rows of data).
In the case of a forward-only result set, if ExcelTemplate encounters data markers for that data source on another row, there is no way to rewind. If you need to populate many different parts of a spreadsheet with fields from the same datasource, consider either using a scrollable cursor or copying your data to a 2-dimensional array.
- Solution 1 - Fetch data into scrollable objects
- (.Net) In .Net use an ADO.Net Dataset
- (Java) In Java create a Scrollable ResultSet:
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select title_id,title from pubs.dbo.titles");
Solution 2 - Copy your ResultSet or DataReader to an Array
Java example:
// ExcelTemplate expects [col][row], ExcelApplication expects [row][col]
// @param bColRow If true, [col][row] layout. [row][col] if false.
public static Object[][] copyResultSetToArray(ResultSet rs, boolean bColRow)
throws SQLException
{
ArrayList al = new ArrayList();
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
int rowCount = 0;
while(rs.next())
{
Object[] row = new Object[colCount];
for(int i = 1; i <= colCount; i++)
row[i-1] = rs.getObject(i);
al.add(row);
rowCount++;
}
Object[][] data = null;
if(bColRow)
data = new Object[colCount][rowCount];
else
data = new Object[rowCount][colCount];
int iRow = 0;
for(Iterator iter = al.iterator(); iter.hasNext();)
{
Object[] row = (Object[])iter.next();
for(int iCol = 0; iCol < colCount; iCol++)
{
if(bColRow)
data[iCol][iRow] = row[iCol];
else
data[iRow][iCol] = row[iCol];
}
iRow++;
}
return data;
}
Cause 2 – Empty Forward Only DataReader/ResultSet in pre 6.0.2
In earlier versions of ExcelWriter this error would also be caused by passing an empty forward-only DataReader/ResultSet to SetDataSource(). Forward-only DataReaders/ResultSets are always returned with the cursor positioned before the first row. You must call next() to access the first row. ExcelWriter calls next() internally. If the DataReader or ResultSet is empty (containing only column names but not data), when next() is called, the behavior is the same as for a forward-only DataReader/ResultSet whose records have already been read (See Cause 1 above). This behavior has been fixed in version 6.0.2.
Solution 1 – Upgrade to the latest version of ExcelWriter Solution 2 – Copy results to array (See Solution 2 for Cause 1 above) Solution 3 – Check to see there is data before using DataReader/ResultSet
By doing a seperate query to determine the count you can make sure that there are results before using the DataReader/ResultSet.
Java example:
ResultSet rsCount = stmt.executeQuery("select count(*) from pubs.dbo.titles where title = 'Good Book'");
rsCount.next();
int x = rsCount.getInt(1);
if(x > 0){
ResultSet rs = stmt.executeQuery("select title_id,title from pubs.dbo.titles where title = 'Good Book' ");
template.setDataSource(rs,"RS");
}
|
