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?

Answer 1

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

Answer 2

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