Excel file damaged

[ghost]

Hello all,

I am trying to create an Excel file with 3 worksheets. Two of the 2 worksheets contain up to 1,000,000 rows. The file is created and seems to contain values, but the file is corrupted and cannot be opened with Excel.

`CompletableFuture cf1 = CompletableFuture.runAsync(() -> { sheet1.value(0, 0, title); sheet1.range(0, 0, 0, 0).style().bold().fontSize(22).set(); sheet1.range(0, 0, 0, 5).style().merge();

            sheet1.value(1, 0, Utility.getStringValue(context, R.string.parameter_label_no_colon));
            sheet1.range(1, 0, 1, 0).style().bold().fontSize(18).set();

            sheet1.value(2, 0, Utility.getStringValue(context, R.string.preasure_logger));
            sheet1.range(2, 0, 2, 0).style().bold().set();
            sheet1.value(2, 1, druloMeasurementZKM.getDruloDeviceName());

            sheet1.value(3, 0, Utility.getStringValue(context, R.string.start_time_label));
            sheet1.range(3, 0, 3, 0).style().bold().set();
            if (measurementChartZoomInfo != null && measurementChartZoomInfo.isZoomActive())
            {
                sheet1.value(3, 1, DateUtils.getInstance().formatDateTime(measurementChartZoomInfo.getStartTime()));
            }
            else
            {
                sheet1.value(3, 1, DateUtils.getInstance().formatDateTime(druloMeasurementZKM.getStartTime()));
            }

            try
            {
                sheet1.flush();
            }
            catch (IOException e)
            {
                //not used
            }
        });
        CompletableFuture<Void> cf2 = CompletableFuture.runAsync(() -> {
            sheet2.value(0, 0, Utility.getStringValue(context, R.string.time_lael_no_colon));
            sheet2.range(0, 0, 0, 0).style().bold().set();
            sheet2.value(0, 1, Utility.getStringValue(context, R.string.excel_worksheet_measurement_pressure) + " / " + pressureUnit);
            sheet2.range(0, 1, 0, 1).style().bold().set();

            //float preassurevalueString;
            int cellIndex = 1;
            int startIndex = 0;
            int endIndex = pressureValueStrings.length - 1;
            if (measurementChartZoomInfo != null && measurementChartZoomInfo.isZoomActive())
            {
                startIndex = measurementChartZoomInfo.getFirstZoomIndex();
                endIndex = measurementChartZoomInfo.getLastZoomIndex();
            }

            for (int i = startIndex; i < endIndex + 1; i++)
            {
                final Date date1 = new Date(dateStrings[i]);
                sheet2.value(cellIndex, 0, date1);//dateTimeString);
                //sheet2.range(cellIndex,0,cellIndex,0).style().format("yyyy-MM-dd HH:mm:ss").set();
                sheet2.range(cellIndex, 0, cellIndex, 0).style().format(dateTimePatternMedium).set();
                sheet2.value(cellIndex, 1, pressureValueStrings[i]);//preassureValueString);
                //sheet2.range(cellIndex,1,cellIndex,1).style().format("#,##0").set();
                cellIndex++;
                try
                {
                    sheet2.flush();
                }
                catch (IOException e)
                {
                    //not used
                }
            }
        });
        if (false)//sheet3 != null)
        {
            CompletableFuture<Void> cf3 = CompletableFuture.runAsync(() -> {
                sheet3.value(0, 0, Utility.getStringValue(context, R.string.time_lael_no_colon));
                sheet3.range(0, 0, 0, 0).style().bold().set();
                sheet3.value(0, 1, Utility.getStringValue(context, R.string.excel_worksheet_measurement_temperature) + " / " + temperatureUnit);
                sheet3.range(0, 1, 0, 1).style().bold().set();

                //float preassurevalueString;
                int cellIndex = 1;
                int startIndex = 0;
                int endIndex = pressureValueStrings.length - 1;
                if (measurementChartZoomInfo != null && measurementChartZoomInfo.isZoomActive())
                {
                    startIndex = measurementChartZoomInfo.getFirstZoomIndex();
                    endIndex = measurementChartZoomInfo.getLastZoomIndex();
                }

                for (int i = startIndex; i < endIndex + 1; i++)
                {
                    final Date date1 = new Date(dateStrings[i]);
                    sheet3.value(cellIndex, 0, date1);
                    sheet3.range(cellIndex, 0, cellIndex, 0).style().format(dateTimePatternMedium).set();
                    sheet3.value(cellIndex, 1, temperatureVales[i]);
                    cellIndex++;
                    try
                    {
                        sheet3.flush();
                    }
                    catch (Exception e)
                    {
                        //not used
                    }
                }
            });
            CompletableFuture.allOf(cf1, cf2, cf3).get();
        }
        else
        {
            CompletableFuture.allOf(cf1, cf2).get();
        }

        book.finish();
        os.close();`

Best Regrads Tilo

[rzymek]

That's because you're writing to the output stream from several threads at once (using CompletableFuture). Fastexcel is not thread-safe.

[ghost]

Thanks for the quick reply and the tip. I have now tried it in the following way:

`final String title = Utility.getStringValue(context, R.string.zkm_measurement) + ": " + druloMeasurementZKM.getMeasurementName(); book = new org.dhatim.fastexcel.Workbook(os, MyApplication.getAppPackageName(), null); final org.dhatim.fastexcel.Worksheet sheet1 = book.newWorksheet(Utility.getStringValue(context, R.string.excel_worksheet_measurement_parameter)); final org.dhatim.fastexcel.Worksheet sheet2 = book.newWorksheet(Utility.getStringValue(context, R.string.excel_worksheet_measurement_pressure)); final org.dhatim.fastexcel.Worksheet sheet3; if (druloMeasurementZKM.isShowTemperatureCurve()) { sheet3 = book.newWorksheet(Utility.getStringValue(context, R.string.excel_worksheet_measurement_temperature)); } else { sheet3 = null; }

        sheet1.value(0, 0, title);
        sheet1.range(0, 0, 0, 0).style().bold().fontSize(22).set();
        sheet1.range(0, 0, 0, 5).style().merge();

        sheet1.value(1, 0, Utility.getStringValue(context, R.string.parameter_label_no_colon));
        sheet1.range(1, 0, 1, 0).style().bold().fontSize(18).set();

        sheet1.value(2, 0, Utility.getStringValue(context, R.string.preasure_logger));
        sheet1.range(2, 0, 2, 0).style().bold().set();
        sheet1.value(2, 1, druloMeasurementZKM.getDruloDeviceName());

        sheet1.finish();

        sheet2.value(0, 0, Utility.getStringValue(context, R.string.time_lael_no_colon));
        sheet2.range(0, 0, 0, 0).style().bold().set();
        sheet2.value(0, 1, Utility.getStringValue(context, R.string.excel_worksheet_measurement_pressure) + " / " + pressureUnit);
        sheet2.range(0, 1, 0, 1).style().bold().set();
        //float preassurevalueString;
        int cellIndex = 1;
        int startIndex = 0;
        int endIndex = pressureValueStrings.length - 1;
        if (measurementChartZoomInfo != null && measurementChartZoomInfo.isZoomActive())
        {
            startIndex = measurementChartZoomInfo.getFirstZoomIndex();
            endIndex = measurementChartZoomInfo.getLastZoomIndex();
        }

        for (int i = startIndex; i < endIndex + 1; i++)
        {
            final Date date1 = new Date(dateStrings[i]);
            sheet2.value(cellIndex, 0, date1);//dateTimeString);
            //sheet2.range(cellIndex,0,cellIndex,0).style().format("yyyy-MM-dd HH:mm:ss").set();
            sheet2.range(cellIndex, 0, cellIndex, 0).style().format(dateTimePatternMedium).set();
            sheet2.value(cellIndex, 1, pressureValueStrings[i]);//preassureValueString);
            //sheet2.range(cellIndex,1,cellIndex,1).style().format("#,##0").set();
            sheet2.flush();
            if (sheet3 != null)
            {
                sheet3.value(cellIndex, 0, date1);
                sheet3.range(cellIndex, 0, cellIndex, 0).style().format(dateTimePatternMedium).set();
                sheet3.value(cellIndex, 1, temperatureVales[i]);
                sheet3.flush();
            }
            cellIndex++;
        }

        book.finish();
        os.close();`

Here, too, the Excel file is defective. Is it possible that I have to edit each WorkSheet one after the other and not two WorkSheets at once as shown in the loop?

[ochedru]

Indeed, we should support multithreaded generation (as described in the readme), provided each sheet is generated by a different thread.

[inkman97]

Thanks for the quick reply and the tip. I have now tried it in the following way:

`final String title = Utility.getStringValue(context, R.string.zkm_measurement) + ": " + druloMeasurementZKM.getMeasurementName(); book = new org.dhatim.fastexcel.Workbook(os, MyApplication.getAppPackageName(), null); final org.dhatim.fastexcel.Worksheet sheet1 = book.newWorksheet(Utility.getStringValue(context, R.string.excel_worksheet_measurement_parameter)); final org.dhatim.fastexcel.Worksheet sheet2 = book.newWorksheet(Utility.getStringValue(context, R.string.excel_worksheet_measurement_pressure)); final org.dhatim.fastexcel.Worksheet sheet3; if (druloMeasurementZKM.isShowTemperatureCurve()) { sheet3 = book.newWorksheet(Utility.getStringValue(context, R.string.excel_worksheet_measurement_temperature)); } else { sheet3 = null; }

        sheet1.value(0, 0, title);
        sheet1.range(0, 0, 0, 0).style().bold().fontSize(22).set();
        sheet1.range(0, 0, 0, 5).style().merge();

        sheet1.value(1, 0, Utility.getStringValue(context, R.string.parameter_label_no_colon));
        sheet1.range(1, 0, 1, 0).style().bold().fontSize(18).set();

        sheet1.value(2, 0, Utility.getStringValue(context, R.string.preasure_logger));
        sheet1.range(2, 0, 2, 0).style().bold().set();
        sheet1.value(2, 1, druloMeasurementZKM.getDruloDeviceName());

        sheet1.finish();

        sheet2.value(0, 0, Utility.getStringValue(context, R.string.time_lael_no_colon));
        sheet2.range(0, 0, 0, 0).style().bold().set();
        sheet2.value(0, 1, Utility.getStringValue(context, R.string.excel_worksheet_measurement_pressure) + " / " + pressureUnit);
        sheet2.range(0, 1, 0, 1).style().bold().set();
        //float preassurevalueString;
        int cellIndex = 1;
        int startIndex = 0;
        int endIndex = pressureValueStrings.length - 1;
        if (measurementChartZoomInfo != null && measurementChartZoomInfo.isZoomActive())
        {
            startIndex = measurementChartZoomInfo.getFirstZoomIndex();
            endIndex = measurementChartZoomInfo.getLastZoomIndex();
        }

        for (int i = startIndex; i < endIndex + 1; i++)
        {
            final Date date1 = new Date(dateStrings[i]);
            sheet2.value(cellIndex, 0, date1);//dateTimeString);
            //sheet2.range(cellIndex,0,cellIndex,0).style().format("yyyy-MM-dd HH:mm:ss").set();
            sheet2.range(cellIndex, 0, cellIndex, 0).style().format(dateTimePatternMedium).set();
            sheet2.value(cellIndex, 1, pressureValueStrings[i]);//preassureValueString);
            //sheet2.range(cellIndex,1,cellIndex,1).style().format("#,##0").set();
            sheet2.flush();
            if (sheet3 != null)
            {
                sheet3.value(cellIndex, 0, date1);
                sheet3.range(cellIndex, 0, cellIndex, 0).style().format(dateTimePatternMedium).set();
                sheet3.value(cellIndex, 1, temperatureVales[i]);
                sheet3.flush();
            }
            cellIndex++;
        }

        book.finish();
        os.close();`

Here, too, the Excel file is defective. Is it possible that I have to edit each WorkSheet one after the other and not two WorkSheets at once as shown in the loop?

Did you find any solution for this? I'm having the same problem, also without multi thread and writing one sheet each time