Telerik et excel

De Banane Atomic
Aller à la navigationAller à la recherche

Liens

Code

Csharp.svg
XlsxFormatProvider formatProvider = new XlsxFormatProvider();

bytes[] file = File.ReadAllBytes(filePath);
FileStream file = File.OpenRead(filePath);
Workbook workbook = formatProvider.Import(file);

Worksheet myWorksheet = this.workbook.Sheets.GetByName("My Worksheet") as Worksheet;

// read cell content
CellSelection selection = worksheet.Cells[row, column];
ICellValue value = selection.GetValue().Value;
CellValueFormat format = selection.GetFormat().Value;
CellValueFormatResult formatResult = format.GetFormatResult(value);
string result = formatResult.InfosText;

private static DataTable ExtractDataIntoTable(Worksheet worksheet, int rowOffset, int columnOffset)
{
    var table = new DataTable();

    for (var i = columnOffset; i < worksheet.UsedCellRange.ColumnCount; i++)
    {
        var selection = worksheet.Cells[rowOffset, i];
        var columnName = selection.GetValue().Value.RawValue;
        if (string.IsNullOrWhiteSpace(columnName))
        {
            break;
        }

        table.Columns.Add(columnName);
    }

    for (var i = rowOffset + 1; i < worksheet.UsedCellRange.RowCount; i++)
    {
        var values = new object[table.Columns.Count];

        for (var j = 0; j < table.Columns.Count; j++)
        {
            var selection = worksheet.Cells[i, columnOffset + j];
            var value = selection.GetValue().Value;
            var format = selection.GetFormat().Value;
            var formatResult = format.GetFormatResult(value);
            var result = formatResult.InfosText;
            values[j] = result;
        }

        if (values.All(AllValuesAreEmptyStrings))
        {
            break;
        }

        table.Rows.Add(values);
    }

    return table;
}