Writing to existing excel table using oledb - C#

Question

Currently I'm using this code:

private void WriteExcelFile()
{
    string connectionString = GetConnectionString();

    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;

        cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
        cmd.ExecuteNonQuery();

        conn.Close();
    }
}

It will work if there is not table1, it will create it then insert the target row. But When I delete the Create table command and use just inserting, it won't work and say me that table1 already exists. How to insert rows to an existing table (sheet) at the end (append row) using oldeb?

I'm pretty sure you can do this using oledb, however oledb can't do much more. Perhaps consider using Excel Interop or ClosedXML — Jeremy Thompson
– Jeremy Thompson, Commented Apr 29, 2017 at 7:01

Vahid Vakily · Accepted Answer · 2017-04-29 08:55:12Z

I suggest NOPI , which is in my opinion better than EPPlus

The Sample code for converting datatable to excel is here

public class ExcelTools
{
    public static byte[] WriteExcel(DataTable dtExport, string[] header = null, string[] excludeColumns = null, bool rtl = true)
    {
        MemoryStream ms = new MemoryStream();
        try
        {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
            ISheet sheet = null;
            //-----------Create Style And Font
            var hFont = xssfWorkbook.CreateFont();
            hFont.FontHeightInPoints = 11;
            hFont.FontName = "Tahoma";
            var defaultStyle = xssfWorkbook.CreateCellStyle();
            defaultStyle.SetFont(hFont);
            var defaultHeaderStyle = xssfWorkbook.CreateCellStyle();
            defaultHeaderStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
            defaultHeaderStyle.SetFont(hFont);
            if (xssfWorkbook.NumberOfSheets == 0)
            {
                sheet = xssfWorkbook.CreateSheet(String.IsNullOrWhiteSpace(dtExport.TableName) ? "ExportReport" : dtExport.TableName);
                sheet.IsRightToLeft = true;
                sheet.CreateRow(0);
            }
            if (header != null)
            {
                for (int index = 0; index < header.Length; index++)
                {
                    var strHeader = header[index];
                    sheet.GetRow(0).CreateCell(index).SetCellValue(header[index]);
                    sheet.GetRow(0).GetCell(index).CellStyle = defaultHeaderStyle;
                }
            }
            else
            {
                int index = 0;
                foreach (DataColumn col in dtExport.Columns)
                {
                    if (excludeColumns != null && excludeColumns.Contains(col.ColumnName))
                        continue;
                    sheet.GetRow(0).CreateCell(index).SetCellValue(col.ColumnName);
                    index++;
                }

            }


            sheet = xssfWorkbook.GetSheetAt(0);
            int indexRow = sheet.LastRowNum + 1;

            for (; indexRow < dtExport.Rows.Count + 1; indexRow++)
            {
                sheet.CreateRow(indexRow);
                int index = 0;
                foreach (DataColumn col in dtExport.Columns)
                {
                    if (excludeColumns != null && excludeColumns.Contains(col.ColumnName))
                        continue;
                    sheet.GetRow(indexRow).CreateCell(index).SetCellValue(dtExport.Rows[indexRow - 1][col.ColumnName].ToStringTD());
                    sheet.GetRow(indexRow).GetCell(index).CellStyle = defaultStyle;
                    index++;
                }
            }
            for (int index = 0; index < sheet.GetRow(0).Cells.Count; index++)
            {
                sheet.AutoSizeColumn(index);
            }
            xssfWorkbook.Write(ms);
            return ms.ToArray();

        }
        catch (Exception ex)
        {
            return null;
        }
    }

Farukh · Accepted Answer · 2017-04-29 06:58:58Z

0

Oledb is not as flexible, you can use EPPlus library instead.

Here is example.

using(var package = new ExcelPackage(new FileInfo(@"c:\temp\tmp.xlsx")))
{
   // calculate all formulas in the workbook
   package.Workbook.Calculate();
   // calculate one worksheet
   package.Workbook.Worksheets["my sheet"].Calculate();
  // calculate a range
  package.Workbook.Worksheets["my sheet"].Cells["A1"].Calculate();
}

answered Apr 29, 2017 at 6:58

Farukh

2,2212 gold badges27 silver badges39 bronze badges

1 Comment

Inside Man Over a year ago

I' using .Net framework 2.0 and really I do not need any other library because I'm not doing something complex!

Collectives™ on Stack Overflow

Writing to existing excel table using oledb - C#

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related