locked
How to insert data into open office excel from sql server using c# RRS feed

  • Question

  • User1667252885 posted

    question1 )How to insert data into open office excel from sql server using c# 

    question 2)How to insert data into sqlserver from open office excel using c#

    Monday, October 28, 2019 12:32 PM

All replies

  • User-719153870 posted

    Hi bobinscaria,

    question1 )How to insert data into open office excel from sql server using c# 

    Is this "open office excel" an existed excel file you mean?

    If not, please refer to Export data from SQL Server to Excel in ASP.Net using C# and VB.Net, in this article, you will learn how to import data from sql server to a new created excel file with the ClosedXML.

    If it's existed excel file, you could refer to Append to excel file with ClosedXML and also below demo:

    using ClosedXML.Excel;
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;

    protected void Sql2Excel_Click(object sender, EventArgs e) { string constr = ConfigurationManager.ConnectionStrings["DConString"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.Connection = con; sda.SelectCommand = cmd; using (DataTable dt = new DataTable()) { sda.Fill(dt); XLWorkbook Workbook = new XLWorkbook(@"D:\Test\sql2excel.xlsx"); IXLWorksheet Worksheet = Workbook.Worksheet("Sheet1"); //check if this excel file in new created with no used rows try { int NumberOfLastRow = Worksheet.LastRowUsed().RowNumber(); IXLCell CellForNewData = Worksheet.Cell(NumberOfLastRow + 1, 1); CellForNewData.InsertTable(dt); } catch//if this excel file is new created, select the first cell { Worksheet.Cell(1, 1).InsertTable(dt); } Workbook.SaveAs(@"D:\Test\sql2excel.xlsx"); } } } } }

    question 2)How to insert data into sqlserver from open office excel using c#

    As for this question, you can refer to Import Data from Excel to SQL Server, in this document, you will learn how to import excel data into sql server with the SqlBulkCopy.

    Hope it could help.

    Best Regard,

    Yang Shen

    Tuesday, October 29, 2019 3:17 AM