Hang in ‎workbook_close

[ronaldowww]

LXW_VERSION "1.1.8"

Subject: Hang in _store_array_formula when invoke workboos_close() method.

Greetings,

I am using libxlsxwriter to exporting an excel file which contains some bmp images, but it appears to no return from the workbook_close method.

I am using OS = Windows 10 and libxlsxwriter LXW_VERSION "1.1.8".

Here is some code that demonstrates the problem:

//insert image
if (PathFileExistsA((*iter).ImagePath.c_str()))
{
error = worksheet_insert_image_opt(worksheet3, excelRow, excelCol, (iter).ImagePath.c_str(), &options1);
if (error)
{
const char strerror = lxw_strerror(error);
WriteLog(LOG_FAILURE, "worksheet insert image %s error, %s.", (*iter).ImagePath.c_str(), strerror);
return;
}
}
else
{
WriteLog(LOG_FAILURE, "Cannot find image file %s.", (*iter).ImagePath.c_str());
}

error = workbook_close(workbook);
if (error)
{
const char* strerror = lxw_strerror(error);
WriteLog(LOG_FAILURE, "excel file close error, %s.", strerror);
return;
}

The callstack when hang issue happening is like the pic showing:

Could you give me some suggestion?
Thanks

[jmcnamara]

@ronaldowww You will need to add a small complete program that demonstrates the issue. There is no way to guess just from a call trace like that.

[ronaldowww]

void CCenter::ExportExcelReport(map<string, vector<strMeasure>>& Measures,
	string gName, string sName, string rName, string wId, string sId, string sId, string tId, 
	string curtime, int rowMin, int rowMax, int colMin, int colMax)
{	
	WriteLog(LOG_INFO, "ExportExcelReport begin.");

	if (wId.empty())
		wId = sId;
	map<string, vector<strMeasure>>::iterator it;
	for (it = Measures.begin(); it != Measures.end(); it++)
	{
		string measureName = it->first;
		WriteLog(LOG_INFO, "Export excel report for Measure %s.", measureName.c_str());

		vector<strMeasure> Measures = it->second;

		string fileName;
		string reportFolder;
		if (lId.empty())
		{
			fileName = string_format("E:\\Results\\%s\\%s\\%s_%s_%s_%s_%s_%s.xlsx", rName.c_str(), wId.c_str(),
				sName.c_str(), rName.c_str(), lId.c_str(), wId.c_str(), measureName.c_str(), curtime.c_str());

			reportFolder = string_format("E:\\Results\\%s\\%s", rName.c_str(), wId.c_str());
		}
		else
		{
			fileName = string_format("E:\\Results\\%s\\%s\\%s\\%s_%s_%s_%s_%s_%s.xlsx", rName.c_str(), lId.c_str(), wId.c_str(),
				sName.c_str(), rName.c_str(), lId.c_str(), wId.c_str(), measureName.c_str(), curtime.c_str());

			reportFolder = string_format("E:\\Results\\%s\\%s\\%s", rName.c_str(), lId.c_str(), wId.c_str());
		}

		CString rPath = (wchar_t*)CA2W(reportFolder.c_str());
		if (!PathFileExists(rPath))
		{
			CreateFolder2(rPath);
		}

		WriteLog(LOG_INFO, "Export excel filename %s.", fileName.c_str());
		WriteLog(LOG_INFO, "Export excel result count %d.", Measures.size());

		lxw_workbook* workbook = workbook_new(fileName.c_str());

		lxw_error error = LXW_NO_ERROR;

		//WorkSheet 1
		lxw_worksheet* worksheet = workbook_add_worksheet(workbook, measureName.c_str());
		lxw_format* merge_format_sheet1 = workbook_add_format(workbook);

		format_set_align(merge_format_sheet1, LXW_ALIGN_CENTER);
		format_set_align(merge_format_sheet1, LXW_ALIGN_VERTICAL_CENTER);
		format_set_border(merge_format_sheet1, LXW_BORDER_THIN);

		//table 1
		worksheet_merge_range(worksheet, 1, 1, 1, 2, "rName", merge_format_sheet1);
		worksheet_merge_range(worksheet, 1, 3, 1, 5, rName.c_str(), merge_format_sheet1);
		worksheet_merge_range(worksheet, 2, 1, 2, 2, "W ID", merge_format_sheet1);
		worksheet_merge_range(worksheet, 2, 3, 2, 5, wId.c_str(), merge_format_sheet1);
		worksheet_merge_range(worksheet, 3, 1, 3, 2, "L ID", merge_format_sheet1);
		worksheet_merge_range(worksheet, 3, 3, 3, 5, lId.c_str(), merge_format_sheet1);
		worksheet_merge_range(worksheet, 4, 1, 4, 2, "S ID", merge_format_sheet1);
		worksheet_merge_range(worksheet, 4, 3, 4, 5, sId.c_str(), merge_format_sheet1);
		worksheet_merge_range(worksheet, 5, 1, 5, 2, "T ID", merge_format_sheet1);
		worksheet_merge_range(worksheet, 5, 3, 5, 5, tId.c_str(), merge_format_sheet1);
		worksheet_merge_range(worksheet, 6, 1, 6, 2, "Measure Name", merge_format_sheet1);
		worksheet_merge_range(worksheet, 6, 3, 6, 5, measureName.c_str(), merge_format_sheet1);
		worksheet_merge_range(worksheet, 7, 1, 7, 2, "Create Time", merge_format_sheet1);
		worksheet_merge_range(worksheet, 7, 3, 7, 5, curtime.c_str(), merge_format_sheet1);

		worksheet_set_column(worksheet, 2, 2, 18, NULL);
		worksheet_set_column(worksheet, 3, 5, 15, NULL);

		//table 2
		worksheet_merge_range(worksheet, 9, 1, 11, 1, "No.", merge_format_sheet1);
		worksheet_merge_range(worksheet, 9, 2, 11, 2, "C No.", merge_format_sheet1);

		// TODO 怎么区分
		if (Measures[0].MeasTypeIdx == 13)
		{
			worksheet_merge_range(worksheet, 9, 3, 9, 4, "Measure Name", merge_format_sheet1);
			worksheet_merge_range(worksheet, 10, 3, 10, 4, "DG", merge_format_sheet1);
			worksheet_write_string(worksheet, 11, 3, "Inner", merge_format_sheet1);
			worksheet_write_string(worksheet, 11, 4, "Outer", merge_format_sheet1);
		}
		else
		{
			worksheet_write_string(worksheet, 9, 3, "Measure Name", merge_format_sheet1);
			worksheet_write_string(worksheet, 10, 3, "DG", merge_format_sheet1);
			worksheet_write_string(worksheet, 11, 3, "Point", merge_format_sheet1);
		}

		WriteLog(LOG_INFO, "ExportExcelReport sheet 1 done.");

		vector<double> cValues;
		vector<double> cInners;
		vector<double> cOuters;
		int index = 1;
		for (auto iter = Measures.begin(); iter != Measures.end(); iter++)
		{
			string dRowCol = string_format("%d_%d", (*iter).dRow, (*iter).dCol);
			worksheet_write_number(worksheet, 11 + index, 1, index, merge_format_sheet1);
			worksheet_write_string(worksheet, 11 + index, 2, dRowCol.c_str(), merge_format_sheet1);
			if ((*iter).MeasTypeIdx == 13)
			{
				worksheet_write_number(worksheet, 11 + index, 3, (*iter).cInner, merge_format_sheet1);
				worksheet_write_number(worksheet, 11 + index, 4, (*iter).cOuter, merge_format_sheet1);
				cInners.push_back((*iter).cInner);
				cOuters.push_back((*iter).cOuter);
			}
			else
			{
				worksheet_write_number(worksheet, 11 + index, 3, (*iter).cValue, merge_format_sheet1);
				cValues.push_back((*iter).cValue);
			}
			index++;
		}

                double max = *max_element(cValues.begin(), cValues.end());
                worksheet_merge_range(worksheet, 11 + index, 1, 11 + index, 2, "Maximum", merge_format_sheet1);
                worksheet_write_number(worksheet, 11 + index, 3, max, merge_format_sheet1);
                index++;

                double min = *min_element(cValues.begin(), cValues.end());
                worksheet_merge_range(worksheet, 11 + index, 1, 11 + index, 2, "Minimum", merge_format_sheet1);
                worksheet_write_number(worksheet, 11 + index, 3, min, merge_format_sheet1);
                index++;
        
                int count = cValues.size();
                double sum = accumulate(cValues.begin(), cValues.end(), 0.0);
                double mean = round(sum / count * 1000000) / 1000000;
                worksheet_merge_range(worksheet, 11 + index, 1, 11 + index, 2, "Mean", merge_format_sheet1);
                worksheet_write_number(worksheet, 11 + index, 3, mean, merge_format_sheet1);
                index++;

                worksheet_merge_range(worksheet, 11 + index, 1, 11 + index, 2, "Max-Min", merge_format_sheet1);
                worksheet_write_number(worksheet, 11 + index, 3, max - min, merge_format_sheet1);
                index++;
        
                double accum = 0.0;
                std::for_each(std::begin(cValues), std::end(cValues), [&](const double d) {
                    accum += (d - mean) * (d - mean);
                    });
                double stdev = std::sqrt(accum / (cValues.size() - 1));
                double Sigma3 = round(stdev * 3 * 1000000) / 1000000;
                worksheet_merge_range(worksheet, 11 + index, 1, 11 + index, 2, "3 Sigma", merge_format_sheet1);
                worksheet_write_number(worksheet, 11 + index, 3, stdev * 3, merge_format_sheet1);
                index++;

                worksheet_merge_range(worksheet, 11 + index, 1, 11 + index, 2, "Unit", merge_format_sheet1);
                worksheet_write_string(worksheet, 11 + index, 3, "m", merge_format_sheet1);
        
                //WorkSheet 2
                string sheet2Name = string_format("%s map", measureName.c_str());
                lxw_worksheet* worksheet2 = workbook_add_worksheet(workbook, sheet2Name.c_str());
                lxw_format* merge_format_sheet2_title = workbook_add_format(workbook);
        
                format_set_align(merge_format_sheet2_title, LXW_ALIGN_CENTER);
                format_set_align(merge_format_sheet2_title, LXW_ALIGN_VERTICAL_CENTER);
                format_set_border(merge_format_sheet2_title, LXW_BORDER_THIN);
                format_set_bold(merge_format_sheet2_title);
        
                worksheet_set_column(worksheet2, 1, colMax - colMin + 1, 15, NULL);
                for (int col = colMin; col <= colMax; col++)
                {
                    worksheet_write_number(worksheet2, 0, col - colMin + 1, col, merge_format_sheet2_title);
                }

                for (int row = rowMax; row >= rowMin; row--)
                {
                    worksheet_write_number(worksheet2, rowMax - row + 1, 0, row, merge_format_sheet2_title);
                }
        
                lxw_format* merge_format_sheet2 = workbook_add_format(workbook);
                format_set_align(merge_format_sheet2, LXW_ALIGN_CENTER);
                for (auto iter = Measures.begin(); iter != Measures.end(); iter++)
                {
                    int dRowIndex = (*iter).dRow;
                    int dColIndex = (*iter).dCol;
                    int excelCol = dColIndex - colMin + 1;
                    int excelRow = rowMax - dRowIndex + 1;
                    worksheet_write_number(worksheet2, excelRow, excelCol, (*iter).cValue, merge_format_sheet2);
                }

                worksheet_set_column(worksheet2, colMax - colMin + 2, colMax - colMin + 3, 18, NULL);
                worksheet_write_string(worksheet2, 0, colMax - colMin + 2, "Unit", merge_format_sheet2);
                worksheet_write_string(worksheet2, 0, colMax - colMin + 3, "m", merge_format_sheet2);
                worksheet_write_string(worksheet2, 1, colMax - colMin + 2, "Maximum", merge_format_sheet2);
                worksheet_write_number(worksheet2, 1, colMax - colMin + 3, max, merge_format_sheet2);
                worksheet_write_string(worksheet2, 2, colMax - colMin + 2, "Minimum", merge_format_sheet2);
                worksheet_write_number(worksheet2, 2, colMax - colMin + 3, min, merge_format_sheet2);
                worksheet_write_string(worksheet2, 3, colMax - colMin + 2, "Mean", merge_format_sheet2);
                worksheet_write_number(worksheet2, 3, colMax - colMin + 3, mean, merge_format_sheet2);
                worksheet_write_string(worksheet2, 4, colMax - colMin + 2, "Range", merge_format_sheet2);
                worksheet_write_number(worksheet2, 4, colMax - colMin + 3, 0, merge_format_sheet2);
                worksheet_write_string(worksheet2, 5, colMax - colMin + 2, "3 Sigma", merge_format_sheet2);
                worksheet_write_number(worksheet2, 5, colMax - colMin + 3, stdev * 3, merge_format_sheet2);			

		WriteLog(LOG_INFO, "ExportExcelReport sheet 2 done.");

                //WorkSheet 3
                string sheet3Name = string_format("%s Images", measureName.c_str());
                lxw_worksheet* worksheet3 = workbook_add_worksheet(workbook, sheet3Name.c_str());
                lxw_format* merge_format_sheet3_title = workbook_add_format(workbook);
        
                format_set_align(merge_format_sheet3_title, LXW_ALIGN_CENTER);
                format_set_align(merge_format_sheet3_title, LXW_ALIGN_VERTICAL_CENTER);
                format_set_border(merge_format_sheet3_title, LXW_BORDER_THIN);
                format_set_bold(merge_format_sheet3_title);
                format_set_font_size(merge_format_sheet3_title, 18);
        
                int excelCellW = 220;
                int excelCellH = 220;
                double cellWidth = Measures[0].ImageWidth;
                double cellHeight = Measures[0].ImageHeight;
                worksheet_set_column_pixels(worksheet3, 0, colMax - colMin + 1, excelCellW, NULL);
                for (int col = colMin; col <= colMax; col++)
                {
                    worksheet_write_number(worksheet3, 0, col - colMin + 1, col, merge_format_sheet3_title);
                }
        
                for (int row = rowMax; row >= rowMin; row--)
                {
                    worksheet_set_row_pixels(worksheet3, (rowMax - row) * 2 + 1, excelCellH, NULL);
                    worksheet_write_number(worksheet3, (rowMax - row) * 2 + 1, 0, row, merge_format_sheet3_title);
                }
        
                struct lxw_image_options options1 = {};
                options1.x_scale = excelCellW / cellWidth;
                options1.y_scale = excelCellH / cellHeight;
        
                lxw_format* merge_format_sheet3 = workbook_add_format(workbook);
                format_set_align(merge_format_sheet3, LXW_ALIGN_CENTER);
                for (auto iter = Measures.begin(); iter != Measures.end(); iter++)
                {
                    int dRowIndex = (*iter).dRow;
                    int dColIndex = (*iter).dCol;
                    int excelCol = dColIndex - colMin + 1;
                    int excelRow = (rowMax - dRowIndex) * 2 + 1;
                    int cValueRow = (rowMax - dRowIndex) * 2 + 2;

                    //insert image
                    if (PathFileExistsA((*iter).ImagePath.c_str()))
                    {
                        error = worksheet_insert_image_opt(worksheet3, excelRow, excelCol, (*iter).ImagePath.c_str(), &options1);
                        if (error)
                        {
                            const char* strerror = lxw_strerror(error);
                            WriteLog(LOG_FAILURE, "worksheet insert image %s error, %s.", (*iter).ImagePath.c_str(), strerror);
                            return;
                        }
                    }
                    else
                    {
                        WriteLog(LOG_FAILURE, "Cannot find image file %s.", (*iter).ImagePath.c_str());
                    }
                    worksheet_write_number(worksheet3, excelRow + 1, excelCol, (*iter).cValue, merge_format_sheet3);
		}
		WriteLog(LOG_INFO, "ExportExcelReport sheet 3 done.");

		error = workbook_close(workbook);
		if (error)
		{
			const char* strerror = lxw_strerror(error);
			WriteLog(LOG_FAILURE, "excel file close error, %s.", strerror);
			return;
		}
	}

	WriteLog(LOG_INFO, "ExportExcelReport end.");
}

Thanks for your quick response.
I have upload the complete method code that invoke the libxlsxwriter.
When the hang issue happened, from the printed log, I can see "ExportExcelReport sheet 3 done.", but no "ExportExcelReport end".
Please help check.
Thanks in advance!

[jmcnamara]

This still isn't a small complete example that demonstrates the issue, so there is no way for me to figure out what the issue is.

However the program shouldn't be calling workbook_close() in a loop since you can only close the file once. Restructure the code so that workbook_close() is outside the loop.

[ronaldowww]

workbook_close corresponds to the workbook_new one by one, though they are in a loop, I don't think this should be a problem.

The code I posted is the full method that is related to libxlsxwriter. And the hang issue doesn't happen every time.

Thanks!

[jmcnamara]

workbook_close corresponds to the workbook_new one by one, though they are in a loop, I don't think this should be a problem.

Correct. That shouldn't be a problem.

The code I posted is the full method that is related to libxlsxwriter. And the hang issue doesn't happen every time.

If you can't reduce the code to a minimal runnable example that I can compile and run then I can't help you.

Instead I suggest:

• Make sure that you aren't writing to the same filename twice in a row.
• Make sure you aren't calling CCenter::ExportExcelReport() from a thread since libxlsxwriter isn't thread safe.
• Reduce the code down to the minimal that will reproduce the issue. For example does it happen without the worksheet_insert_image_opt() code or the worksheet_merge_range() code.

[ronaldowww]

Thanks for your response. I'll update you if I can reproduce the issue in a runnable example.