One and a half problem with Export to Excel

hi support,

i downloaded the export to excel package for Java and everything is ok except two things:

  1. When i open the exported excel file the footer is just below the header.
  2. When i use setSerializableColumns method the server code throws the following error:

java.lang.ArrayIndexOutOfBoundsException: 8
at Export.ExcelXmlParser.optimizeColumns(ExcelXmlParser.java:91)
at Export.ExcelXmlParser.getColumnsInfo(ExcelXmlParser.java:70)
at Export.ExcelWriter.headerPrint(ExcelWriter.java:60)
at Export.ExcelWriter.generate(ExcelWriter.java:34)

to me it makes sense because i have 10 columns and i wanted to miss the last two but seems like it is not working this way.

So any idea how to fix this problems?

anyone has some idea?

  1. Do you mean no data is exported, only header and footer?
  2. You can hide column on the client side with following code:

grid.setColumnHidden(index, true);

And then restore hidden column with following code:
grid.setColumnHidden(index, false);

10x Olga for replying back to my question.

Now get this visual scenario ( visual things are much easier to interpret)

Header: A,B,C,D
Rows : h,j,k,t
Footer : P,Q,R,S

The above is the structrure of the grid:

first goes the HEADER, then the ROWS and finally the FOOTER

but when i export it to MS Excel and open the file i get the following structure:

Header: A,B,C,D
Footer : P,Q,R,S <- this is what makes me feel worried!
Rows : h,j,k,t

that means that the FOOTER is just bellow the header instead of being bellow the rows.

any idea?

Unfortunately we cannot reproduce this issue locally. Are you sure you are using latest version of exporting grid to Excel library?

not sure. Pls send some link for JAVA support because i am doing something 2 days when it only takes 1 min.

Try to update your version of “Grid-to-Excel” package dhtmlx.com/blog/?p=855

If issue still occurs please provide any kind of demo where it can be reconstructed.

Here is the code:

function initGrid()
            {
                mygrid = layOut.cells("b").attachGrid();
                mygrid.setImagePath("/NipoElectronics/jScript/dhtmlXAll/imgs/");
                mygrid.enableLightMouseNavigation(true);

                var geoCodedPlace ="<div id='gp'><a href='javascript:doBeforeReverseGeocoding();'>Населено място/Обект</a></div>";
                var litresH100Column;

                if(gup('stopsControl') == '0') litresH100Column = "<div>Литри/100</div>";
                else if(gup('stopsControl') == '1') litresH100Column = "<div>Литри/100/h</div>";
                else litresH100Column = "<div>Литри/100</div>";

                mygrid.setHeader("-,#cspan,#cspan,#cspan,#cspan,#cspan,#cspan,#cspan,#cspan,#cspan");
                mygrid.attachHeader("<div align='center'>Период</div>,#cspan,Движение,Престой," + geoCodedPlace + ", Разтояние/км,Разход/л," + litresH100Column + ",Следа,#cspan");
                mygrid.setInitWidths("90,90,80,80,*,100,100,80,20,50");
                mygrid.setColAlign("center,center,center,center,left,left,left,left,left,left");
                mygrid.setColTypes("ro,ro,ro,ro,ro,ro,ro,ro,ch,img");
                mygrid.setSkin("dhx_skyblue");
                mygrid.init();

                var urlParameters = "vehicleId="+gup('vehicleId')+"&startDate="+gup('date')+"&stopsControl="+gup('stopsControl')+"&stopPlaces="+gup('stopPlaces')+"&accuracy="+gup('accuracy')+"&firmName="+gup('firmName')+"&type=day_report";
                var gridQString = "/NipoElectronics/RouteReportsServlet?"+urlParameters;

                //dhtmlxError.catchError("LoadXML", ajaxErrorHandler);

                mygrid.loadXML(gridQString);
                mygrid.attachEvent("onXLE",function()
                {
                    layOut.progressOff();
                    buildGridTitle();
                    buildGridFooter();
                    
                    mygrid.setSizes();
                    
                });
                mygrid.objBox.style.overflowX="hidden";
            }

Here is the the final part:

[code]function buildGridTitle()
{
var formatedDate = mygrid.getUserData("", “formatedDate”);
var startWorkTime = mygrid.getUserData("", “startWorkTime”);
var endWorkTime = mygrid.getUserData("", “endWorkTime”);
var vehicleId = mygrid.getUserData("", “vehicleId”);

            var workTime = startWorkTime + " - " + endWorkTime;

            var tableTitle = "Дневен пътен лист за: <span id='s1' style=\"color:red;\">" + formatedDate + "</span> &nbsp; Работно време: <span id='s2' style=\"color:red;\">" + workTime + "</span> &nbsp; Кола: <span id='s3' style=\"color:red;\">" + vehicleId + "</span>";

            var centeredTitle = "<div align='center'>" + tableTitle + "</div>";

            mygrid.setColumnLabel(0,centeredTitle,0);
        }[/code]
function buildGridFooter()
            {
                var titleDiv   = "<div style='color:red;font-weight:bold'>Общо:</div>";
                var allTime    = "<div style='color:red;font-weight:bold'>" + mygrid.getUserData("", "allTime"); + "</div>";
                var movingTime = "<div style='color:red;font-weight:bold'>" + mygrid.getUserData("", "movingTime"); + "</div>";
                var idleTime   = "<div style='color:red;font-weight:bold'>" + mygrid.getUserData("", "idleTime"); + "</div>";
                var distance   = "<div style='color:red;font-weight:bold'>" + mygrid.getUserData("", "distance"); + "</div>";
                var fuel       = "<div style='color:red;font-weight:bold'>" + mygrid.getUserData("", "fuel"); + "</div>";
                var fuel100    = "<div style='color:red;font-weight:bold'>" + mygrid.getUserData("", "fuel100"); + "</div>";

                var startWorkDay    = mygrid.getUserData("", "startWorkDay");
                var startWorkTime   = mygrid.getUserData("", "startWorkTime");
                var endWorkDay      = mygrid.getUserData("", "endWorkDay");
                var endWorkTime     = mygrid.getUserData("", "endWorkTime");
                var dreif           = mygrid.getUserData("", "dreif");
                var vehicleId       = mygrid.getUserData("", "vehicleId");

                var trace = "<div style=\"color:red;font-weight:bold\"><a href=\"javascript:void(0)\" onclick=\"doVisualization('"+ startWorkDay + "'\\,'" + startWorkTime + "'\\,'"+ endWorkDay +"'\\,'"+ endWorkTime +"'\\,'"+ vehicleId +"'\\,'"+ dreif +"'\\,'fullDay')\">\>\>\></a></div>";

                mygrid.attachFooter(titleDiv + "," + allTime + "," + movingTime +"," + idleTime+ ", ," +distance+ "," + fuel + "," + fuel100 + "," + trace + ",#cspan");
            }

and finally the VERY problem is this:

  1. when exporting to Excel it works but puts the footer before the rows.
  2. when hiding some rows it throws IndexOutOfBoundsException on the server.
  3. export to PDF does not work at all ( it throws IndexOutOfBoundsException = -1 ) on the server.

So any suggestions?

To hide your footer from the top find file ExcelXmlParser.java and replace

NodeList n1 = root.getElementsByTagName("columns");

with

NodeList nh = root.getElementsByTagName("head"); Element rt = (Element) nh.item(0); NodeList n1 = rt.getElementsByTagName("columns");
Footer will be hidden. You can’t export footer in last released version of export for Java. But we are going to implement it in new versions.

10x for the fast reply.

Unfortunatelly i am not going to hide it because my boss is going to kill me. I will just leave the way it is or if i have time i will modify the source code.

What about the ability to export to PDF. It throws indexOutOfBoundsException -1 ???

Any idea?

What about the ability to export to PDF. It throws indexOutOfBoundsException -1 ???
Can you please provide stacktrace of this issue?

exporting to pdf error stack trace

java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(String.java:1937)
at Export.PDFWriter.textWrap(PDFWriter.java:291)
at Export.PDFWriter.printRows(PDFWriter.java:176)
at Export.PDFWriter.generate(PDFWriter.java:65)

Issue with exporting footers to Excel fixed. Try to replace original files with attached files
footer_export_fix.zip (2.86 KB)

exporting to pdf error stack trace
To fix this issue do the following:

Find file PDFWriter.java and replace:

if (f.stringWidth(text) <= width) {
return text;
}

with

if ((f.stringWidth(text) <= width)||(text.length() == 0)) {
return text;
}

When i tried to export to excel using the update u provided i got the following problem:

i see only the FIRST column and what is really weird is that the excel file shows column A and then column K which means that B,C,D,E,… and the rest are hidden or something else i have no idea what it is.

any solution?

find file ExcelGenerator.java and replace method like here

public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
String xml = req.getParameter(“grid_xml”);
xml = URLDecoder.decode(xml, “UTF-8”);
resp.getWriter().println(xml);
return;
// (new ExcelWriter()).generate(xml, resp);
}

After that try to export your grid. You will see XML instead of excel. Copy and post it here. It can be usefull to understand problem.
Don’t forget to restore file ExcelGenerator.java.

Sorry for beeing late. Here is the output:

<rows profile='color'><head><columns><column  colspan="10"  width='92' align='center' type='ro' sort='na' color=''><![CDATA[Дневен пътен лист за: 2011-Април-01   Работно време: 07:49:44 - 21:01:53   Кола: 0913]]></column><column  width='92' align='center' type='ro' sort='na' color=''><![CDATA[]]></column><column  width='82' align='center' type='ro' sort='na' color=''><![CDATA[]]></column><column  width='82' align='center' type='ro' sort='na' color=''><![CDATA[]]></column><column  width='744' align='left' type='ro' sort='na' color=''><![CDATA[]]></column><column  width='102' align='left' type='ro' sort='na' color=''><![CDATA[]]></column><column  width='102' align='left' type='ro' sort='na' color=''><![CDATA[]]></column><column  width='82' align='left' type='ro' sort='na' color=''><![CDATA[]]></column><column  width='22' align='left' type='ch' sort='na' color=''><![CDATA[]]></column><column  width='52' align='left' type='img' sort='na' color=''><![CDATA[]]></column></columns>
<columns><column colspan="2" ><![CDATA[Период]]></column><column><![CDATA[]]></column><column><![CDATA[Движение]]></column><column><![CDATA[Престой]]></column><column><![CDATA[Населено място/Обект]]></column><column><![CDATA[ Разтояние/км]]></column><column><![CDATA[Разход/л]]></column><column><![CDATA[Литри/100]]></column><column colspan="2" ><![CDATA[Следа]]></column><column><![CDATA[]]></column></columns></head>
<foot>
<columns>
<column><![CDATA[Общо:]]></column>
<column><![CDATA[13:12:09]]></column>
<column><![CDATA[03:00:39]]></column>
<column><![CDATA[10:11:30]]></column>
<column><![CDATA[ ]]></column>
<column><![CDATA[111.53]]></column>
<column><![CDATA[5.882]]></column>
<column><![CDATA[5.274]]></column>
<column colspan='2' ><![CDATA[>>>]]></column>
<column><![CDATA[]]></column>
</columns>
</foot><row><cell><![CDATA[07:49:44]]></cell><cell><![CDATA[08:16:31]]></cell><cell><![CDATA[00:26:47]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[9.003]]></cell><cell><![CDATA[0.45]]></cell><cell><![CDATA[5]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Следа]]></cell></row>
<row><cell><![CDATA[08:16:31]]></cell><cell><![CDATA[08:16:32]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[00:00:01]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Престой]]></cell></row>
<row><cell><![CDATA[08:16:32]]></cell><cell><![CDATA[08:51:15]]></cell><cell><![CDATA[00:34:43]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[32.49]]></cell><cell><![CDATA[1.737]]></cell><cell><![CDATA[5.345]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Следа]]></cell></row>
<row><cell><![CDATA[08:51:15]]></cell><cell><![CDATA[11:26:44]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[02:35:29]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Престой]]></cell></row>
<row><cell><![CDATA[11:26:44]]></cell><cell><![CDATA[12:11:32]]></cell><cell><![CDATA[00:44:48]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[48.058]]></cell><cell><![CDATA[2.575]]></cell><cell><![CDATA[5.359]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Следа]]></cell></row>
<row><cell><![CDATA[12:11:32]]></cell><cell><![CDATA[12:16:07]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[00:04:35]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Престой]]></cell></row>
<row><cell><![CDATA[12:16:07]]></cell><cell><![CDATA[12:41:21]]></cell><cell><![CDATA[00:25:14]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[0.105]]></cell><cell><![CDATA[0.007]]></cell><cell><![CDATA[6.5]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Следа]]></cell></row>
<row><cell><![CDATA[12:41:21]]></cell><cell><![CDATA[12:48:15]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[00:06:54]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Престой]]></cell></row>
<row><cell><![CDATA[12:48:15]]></cell><cell><![CDATA[13:09:20]]></cell><cell><![CDATA[00:21:05]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[0.052]]></cell><cell><![CDATA[0.003]]></cell><cell><![CDATA[6.5]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Следа]]></cell></row>
<row><cell><![CDATA[13:09:20]]></cell><cell><![CDATA[20:33:51]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[07:24:31]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Престой]]></cell></row>
<row><cell><![CDATA[20:33:51]]></cell><cell><![CDATA[21:01:53]]></cell><cell><![CDATA[00:28:02]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[ ]]></cell><cell><![CDATA[21.822]]></cell><cell><![CDATA[1.11]]></cell><cell><![CDATA[5.085]]></cell><cell><![CDATA[0]]></cell><cell><![CDATA[Следа]]></cell></row></rows>

So when i try to export to excel the footer was just below the header.

When i updated the files with the ones you posted here the footer went to the bottom but all the columns were overlapped.

Locally your xml was exported without any problems. Can you give us more details? It would be usefull to see your generated excel-file.