Sir,

The translator we use to import old .xls files cannot import charts. We want to find from code behind where the range in first and second columns end, and add new chart on forth column showing the range. Kindly help me in this regard.

Hi,

You can import the xlsx file produced by Office Binary Translator by using the ExcelImporter class:


Code (]var workbook = new Workbook();
var importer = new ExcelImporter();
importer.Import(xlsxFile, workbook);[/code):

The worksheets in the workbook can be accessed through the Worksheets collection by index or by name. For example, to obtain the first worksheet in the workbook, use the following code: [code]var worksheet = workbook.Worksheets[0];

To find the range of cells in the first two columns, which contain data, you can traverse the worksheet cells and inspect their Column and Data properties:


Code (]int maxRow = -1;
foreach (var cell in worksheet.Cells)
{
     if ((cell.Column == 0 || cell.Column == 1) && cell.Data != null)
           maxRow = Math.Max(maxRow, cell.Row);
}

if (maxRow != -1)
{
     var range = worksheet.CellRanges[0, 0, 1, maxRow):

; }

To add a new chart in the worksheet, call the AddChart method of the Drawing object of the worksheet. You can supply the origin column and row of the chart as parameters:


Code (]var chart = worksheet.Drawing.AddChart(3, 0);[/code):

To specify the data displayed in the chart, call the SetDataSource method: [code]chart.SetDataSource(range.ToString(), PlotBy.Column, null, null);

After you are done, use an ExcelExporter object to save the modified workbook back to the .xlsx file:

[code]var exporter = new ExcelExporter();
exporter.Export(workbook, xlsxFile);[/code]
Below you can find an image illustrating the result:

Regards,
Meppy

---