Export Datatable into Excel Strat from Specific Column C#

Question

I have a datatable and I want export the data into excel but start exporting from a specific column. Scenario, I have an excel sheet and first 5 columns [A-E] have data and want to export from datatable and should start from column F.

How to achieve this in C# console application?

score 1 · Accepted Answer · answered Feb 03 '22 at 07:37

So, the most obvious approach here would be to iterate over the data table's rows and write each to the Excel sheet manually:

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;

// Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;

// Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

// Write the data. Remember that Excel is 1-indexed
int rowIndex = 1;
foreach (DataRow row in table.Rows) {
    int colIndex = 6;
    foreach (DataColumn col in table.Columns) {
        oSheet.Cells[rowIndex, colIndex] = row[col];
        colIndex++;
    }

    rowIndex++;
}

// Save the Excel file
oXL.Visible = false;
oXL.UserControl = false;
oWB.SaveAs("c:\\test\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
    false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

// Exit Excel
oWB.Close();
oXL.Quit();

There's probably a more elegant solution to this problem but I think this will get the job done, at least. As a reference, my Excel code was modified from this question, so you might be able to find a better match for your specific needs there.

Great. I have modified code a little bit according my requirement. It's working perfectly. — Muhammad Imran Ansari, Feb 03 '22 at 09:15
Glad to hear it. I discovered later that there's an Excel library which is much easier to work with and posted a second answer here. Please check it out if you have the time. — Woody1193, Feb 03 '22 at 10:06

M Deni Firdaus · Answer 2 · 2022-02-03T10:28:00.850

you can use ClosedXML library. example from my code

        var wb = new XLWorkbook();
        var ws = wb.Worksheets.Add("My Sheet 1");

        ws.Cell("A8").Value = "Print by Admin:";
        ws.Cell("B8").Value = $"{GetUserById(userId).name} - {GetUserById(userId).position_name}";
        ws.Range("B8:C8").Merge();

        var range = ws.Cell("B12").InsertTable(data);
        range.Style.Border.TopBorder = XLBorderStyleValues.Thin;
        range.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
        range.Style.Border.RightBorder = XLBorderStyleValues.Thin;
        range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;

        ws.Row(12).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        ws.Row(12).Style.Font.Bold = true;

        MemoryStream stream = (MemoryStream)GetStream(wb);

        return File(stream.ToArray(), ""application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"", "yourfilename.xlsx");

here code for Getstream:

public Stream GetStream(XLWorkbook excelWorkbook)
        {
            Stream fs = new MemoryStream();
            excelWorkbook.SaveAs(fs);
            fs.Position = 0;
            return fs;
        }

I don't see how this addresses the OP's question. Are you missing something here? — Woody1193, Feb 03 '22 at 08:01

score 0 · Answer 3 · answered Feb 03 '22 at 07:59

0

Alternatively, you could try using the ClosedXML library:

XLWorkbook workbook = new XLWorkbook();
DataTable table = GetYourTable();
var sheet = workbook.Worksheets.Add(table);

var firstColumn = sheet.Column(1);
firstColumn.InsertColumnsBefore(5);

This code works by creating a new Excel worksheet from the datatable, getting a pointer to the first column in the worksheet and then inserting five columns (A - E) before it.

answered Feb 03 '22 at 07:59

Woody1193

7,252
5
40
90

Please can you share how to use `ClosedXML` library to export `datagridview` bonded from dataset to excel ..thanks – sam Mar 08 '23 at 08:49

Export Datatable into Excel Strat from Specific Column C#

3 Answers3