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](http://stackoverflow.com/q/15894628/495455), however oledb can't do much more. Perhaps consider using Excel Interop or ClosedXML — Jeremy Thompson, Apr 29 '17 at 07:01

score 2 · Answer 1 · answered Apr 29 '17 at 08:55

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

score 1 · Answer 2 · answered Apr 29 '17 at 06:58

1

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 '17 at 06:58

Farukh

2,173
2
23
38

I' using `.Net framework 2.0` and really I do not need any other library because I'm not doing something complex! – Inside Man Apr 29 '17 at 07:23

Writing to existing excel table using oledb - C#

2 Answers2