Export Datatable into Excel Strat from Specific Column C#-CodePudding

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 coloumn-F. How to achieve this in C# console application.

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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;
        }