C#: Create an Excel file without Excel already installed on the machine

Scenario:

How can I create an Excel Spreadsheet with C# without requiring Excel to be installed on the machine that’s running the code?

Solution:

You can use a library called ExcelLibrary. It’s a free, open source library posted on Google Code:

ExcelLibrary

This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.

It’s very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.

ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.

You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files).

There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.

Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)

Here are a couple links for quick reference:
ExcelLibraryGNU Lesser GPL
EPPlusGNU Library General Public License (LGPL)

Here some example code for ExcelLibrary:

Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:

//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");

//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();

//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();

//Add the table to the data set
ds.Tables.Add(dt);

//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.

C#: Save SQL query result as CSV file with Column Headers

Scenario:

I want to write SQL SELECT query in c# pages then save the query result in excel file with column headers

Solution – Working code: 

Add the following #namespaces
/*extra namespaces*/
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
/* MAIN FUNCTION*/
public void Main()
{
bool useHeaders = true;
bool firstCounter = true;
String strToday = DateTime.Now.ToString(“yyyyMMdd_HHmm”);
/*Name of the new file name*/
String fileName = “D:\\MAhmed\\SocialCareFolders\\batch output\\2015_Batch_Trace_Output_” + strToday + “.csv”;
/*connect to the sql server and get the result set*/

/*Gets or sets the connection string used to establish a connection to a data source.*/

ConnectionManager cm;
SqlConnection sqlCon = new SqlConnection();
cm = (ConnectionManager) Dts.Connections[“ADO.NET_OutComesStats”];

/*When you call the AcquireConnection method of a connection manager, the connection manager connects to the data source. Associate the connections with the active transaction by using the Transaction property*/
sqlCon=(SqlConnection)cm.AcquireConnection(Dts.Transaction);
/*sqlCon = (SqlConnection)Dts.Connections[“Dev01_OutComesStats”].AcquireConnection(Dts.Transaction);*/

/*String sqlQuery = “SELECT * FROM [OutComesStats].[temp].[SocialCare_BatchTraceOutput]”;*/
if(sqlCon.State!=ConnectionState.Open)
{
sqlCon.Close();
sqlCon.Open();
}

using(SqlCommand sqlCom = new SqlCommand(“SELECT * FROM [OutComesStats].[temp].[SocialCare_BatchTraceOutput]”, sqlCon))
using(SqlDataReader reader = sqlCom.ExecuteReader())
/*read the sql result set using SqlDataReader, read header line from the reader, read one record at a time and write to the fileName file*/

/*You can’t change reference once you used in using clause. It means you cannot change the value of that variable.*/
using (StreamWriter writer = new StreamWriter(fileName))
{
/*get the ColumnNames from the SqlDataReader*/
if (useHeaders)
{
List<String> columnNames = getColumnNames(reader);
String line=””;
foreach(String columnName in columnNames)
{
if (firstCounter == true)
{
line += string.Format(“{0}”, columnName);
firstCounter = false;
}
else
line += string.Format(“,{0}”, columnName);
}
writer.WriteLine(line);

}
/*read a record at a time and then write to the fileName file*/
while (reader.Read())
{
writer.WriteLine(
“{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}”,

reader[“RecordType”]
,reader[“LocalPatientID”]
,reader[“DateOfBirth”]
,reader[“Col4”]
,reader[“Col5”]
,reader[“NHSNumber”]
,reader[“Surname”]
,reader[“Col8”]
,reader[“FirstName”]
,reader[“Col10”]
,reader[“Sex”]
,reader[“Col12”]
,reader[“Col13”]
,reader[“Col14”]
,reader[“Col15”]
,reader[“Col16”]
,reader[“PostCode”]);
}
}
sqlCon.Close();
cm.Dispose();

}

 

 

1st example:

Add the following #namespaces

/* extra name spaces
*add Microsoft.CSharp
*add a COM reference in your project to the “Microsoft Excel 11.0 Object Library”
*/
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Marshal = System.Runtime.InteropServices.Marshal; /*to release the resources consumed by excel app*/
using XlPlatform=Microsoft.Office.Interop.Excel.XlPlatform;

 

string separator = ";";
string fieldDelimiter = "";
bool useHeaders = true;

string connectionString = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

using (SqlConnection conn = new SqlConnection(connectionString))
{
     using (SqlCommand cmd = conn.CreateCommand())
     {
          conn.Open();
          string query = @"SELECT whatever";

          cmd.CommandText = query;

          using (SqlDataReader reader = cmd.ExecuteReader())
          {
                if (!reader.Read())
                {
                     return;
                }

                List<string> columnNames = GetColumnNames(reader);

                // Write headers if required
                if (useHeaders)
                {
                     first = true;
                     foreach (string columnName in columnNames)
                     {
                          response.Write(first ? string.Empty : separator);
                          line = string.Format("{0}{1}{2}", fieldDelimiter, columnName, fieldDelimiter);
                          response.Write(line);
                          first = false;
                     }

                     response.Write("\n");
                }

                // Write all records
                do
                {
                     first = true;
                     foreach (string columnName in columnNames)
                     {
                          response.Write(first ? string.Empty : separator);
                          string value = reader[columnName] == null ? string.Empty : reader[columnName].ToString();
                          line = string.Format("{0}{1}{2}", fieldDelimiter, value, fieldDelimiter);
                          response.Write(line);
                          first = false;
                     }

                     response.Write("\n");
                }
                while (reader.Read());
          }
     }
}

And you need to have a function GetColumnNames:

List<string> GetColumnNames(IDataReader reader)
{
    List<string> columnNames = new List<string>();
    for (int i = 0; i < reader.FieldCount; i++)
    {
         columnNames.Add(reader.GetName(i));
    }

    return columnNames;
}

2nd Example:
using(SqlConnection conn = new SqlConnection(connectionString))
using(SqlCommand cmd = conn.CreateCommand())
{
  conn.Open();
  cmd.CommandText = QueryLoader.ReadQueryFromFileWithBdateEdate(
    @"Resources\qrs\qryssysblo.q", newdate, newdate);

  using(SqlDataReader reader = cmd.ExecuteReader())
  using(StreamWriter writer = new StreamWriter("c:\temp\file.txt"))
  {
    while(reader.Read())
    {
      // Using Name and Phone as example columns.
      writer.WriteLine("Name: {0}, Phone : {1}", 
        reader["Name"], reader["Phone"]);
    }
  }
}
 

C#: Create new excel workbook using Office Interop COM object/ OpenXml

Scenario:

I need to create an csv file programmatically.

Solution:

Resources:

Add the following #namespaces

/* extra name spaces
*add a Assembly reference Microsoft.CSharp
*add a reference – COM reference in your project to the “Microsoft Excel 11.0 Object Library
*/
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Marshal = System.Runtime.InteropServices.Marshal; /*Marshal is a class that operate and release the resources consumed by excel app*/

Namespace:   System.Runtime.InteropServices
Assembly:  mscorlib (in mscorlib.dll)
using XlPlatform=Microsoft.Office.Interop.Excel.XlPlatform;

Code:

public void Main()
{
String batchFileName = Dts.Variables[“User::batchFileName”].Value.ToString();
String batchWorksheetName = Dts.Variables[“User::batchWorksheetName”].Value.ToString();
InsertWorkSheet2(batchFileName, batchWorksheetName);
}

private void InsertWorkSheet2(String fileLoc, String batchWorksheetName)
{
Excel.Application excelApp=null;

/*Application is a .NET interface derived from a COM coclass that is required by managed code for interoperability with the corresponding COM object.

Use this derived interface to access all method, property, and event members of the COM object. */
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;

try
{
FileInfo file = new FileInfo(fileLoc);

excelApp = new Excel.Application();
/*allow the excel operations including opening and saving file
excelApp.Visible = true;*/
excelApp.Visible = true;
excelApp.DisplayAlerts = false;
//workbook = excelApp.Workbooks.Open(fileLoc, ReadOnly: false, Editable:true);
workbook = (Excel.Workbook)excelApp.Workbooks.Add(Type.Missing);
/*activate the workbook
to remove ambiguity between method _Workbook.Activate warning type cast*/
((Microsoft.Office.Interop.Excel._Workbook)workbook).Activate();

sheets = workbook.Sheets;
/*add the worksheet at the end of the other worksheet*/
//newSheet = (Excel.Worksheet)sheets.Add(After: workbook.Sheets[workbook.Sheets.Count]);
//String strSheetName = “My New Sheet” + (sheets.Count-1) ;
newSheet = workbook.Worksheets[1];
try
{
newSheet.Name = batchWorksheetName;
}
catch
{
newSheet.Name = batchWorksheetName + ” ” +(sheets.Count – 1);
}

((Microsoft.Office.Interop.Excel._Worksheet)newSheet).Activate();
//newSheet.Cells[5, 6] = “BOO hoo!”;
/*save as xls file works – it creates new worksheet, give a name of the new worksheet, and save changes*/
/*workbook.SaveAs(fileLoc, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);*/
/*Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared mode stops user from changing*/

/*Excel.XlFileFormat.xlWorkbookNormal Specifies the file format when saving the spreadsheet*/
workbook.SaveAs(fileLoc, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

/*check all the existing worksheet in sheets
foreach (Excel.Worksheet ws in sheets)
{
MessageBox.Show(ws.Name);
}
*/

workbook.Close();
excelApp.Quit();
}
finally
{
/*Release the resources. If you do not release the resources properly.
* File will be still opened by the process and it stops the user from modifying and even from opening*/
Marshal.ReleaseComObject(newSheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);

newSheet = null;
sheets = null;
workbook = null;
excelApp = null;

GC.WaitForPendingFinalizers();
GC.Collect();
}
}

 

############################################################
############################################################

Below is the console application which I have made and it can create an simple excel file which cells are set like this:

using System;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
 
namespace OpenXmlExcelGenerateStyle
{
    class Program
    {
        static void Main(string[] args)
        {
            string sFile = "ExcelAndStyles.xlsx";
            if (File.Exists(sFile))
            {
                File.Delete(sFile);
            }
            BuildWorkbook(sFile);
        }
 
        private static void BuildWorkbook(string sFile)
        {
            try
            {
                using (SpreadsheetDocument xl = SpreadsheetDocument.Create(sFile, SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart wbp = xl.AddWorkbookPart();
                    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                    Workbook wb = new Workbook();
                    FileVersion fv = new FileVersion();
                    fv.ApplicationName = "Microsoft Office Excel";
 
                    Worksheet ws = new Worksheet();
                    WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
                    // add styles to sheet
                    wbsp.Stylesheet = CreateStylesheet();
                    wbsp.Stylesheet.Save();
                    
                    // generate rows
                    SheetData sd = CreateSheetData();
                    ws.Append(sd);
                    wsp.Worksheet = ws;
                    wsp.Worksheet.Save();
                    Sheets sheets = new Sheets();
                    Sheet sheet = new Sheet();
                    sheet.Name = "Sheet1";
                    sheet.SheetId = 1;
                    sheet.Id = wbp.GetIdOfPart(wsp);
                    sheets.Append(sheet);
                    wb.Append(fv);
                    wb.Append(sheets);
 
                    xl.WorkbookPart.Workbook = wb;
                    xl.WorkbookPart.Workbook.Save();
                    xl.Close();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                Console.ReadLine();
            }
        }
        private static SheetData CreateSheetData()
        {
            SheetData sheetData1 = new SheetData();
            Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
            Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)1U };
 
            row1.Append(cell1);
 
            Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
            Cell cell2 = new Cell() { CellReference = "B2", StyleIndex = (UInt32Value)2U };
 
            row2.Append(cell2);
 
            Row row3 = new Row() { RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
            Cell cell3 = new Cell() { CellReference = "C3", StyleIndex = (UInt32Value)3U };
 
            row3.Append(cell3);
 
            sheetData1.Append(row1);
            sheetData1.Append(row2);
            sheetData1.Append(row3);
 
            return sheetData1;
        }
 
        private static Stylesheet CreateStylesheet()
        {
            Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
 
            Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
 
            Font font1 = new Font();
            FontSize fontSize1 = new FontSize() { Val = 11D };
            Color color1 = new Color() { Theme = (UInt32Value)1U };
            FontName fontName1 = new FontName() { Val = "Calibri" };
            FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
            FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
 
            font1.Append(fontSize1);
            font1.Append(color1);
            font1.Append(fontName1);
            font1.Append(fontFamilyNumbering1);
            font1.Append(fontScheme1);
 
            fonts1.Append(font1);
 
            Fills fills1 = new Fills() { Count = (UInt32Value)5U };
 
            // FillId = 0
            Fill fill1 = new Fill();
            PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
            fill1.Append(patternFill1);
 
            // FillId = 1
            Fill fill2 = new Fill();
            PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
            fill2.Append(patternFill2);
 
            // FillId = 2,RED
            Fill fill3 = new Fill();
            PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFFF0000" };
            BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill3.Append(foregroundColor1);
            patternFill3.Append(backgroundColor1);
            fill3.Append(patternFill3);
 
            // FillId = 3,BLUE
            Fill fill4 = new Fill();
            PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = "FF0070C0" };
            BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill4.Append(foregroundColor2);
            patternFill4.Append(backgroundColor2);
            fill4.Append(patternFill4);
 
            // FillId = 4,YELLO
            Fill fill5 = new Fill();
            PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor3 = new ForegroundColor() { Rgb = "FFFFFF00" };
            BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill5.Append(foregroundColor3);
            patternFill5.Append(backgroundColor3);
            fill5.Append(patternFill5);
 
            fills1.Append(fill1);
            fills1.Append(fill2);
            fills1.Append(fill3);
            fills1.Append(fill4);
            fills1.Append(fill5);
 
            Borders borders1 = new Borders() { Count = (UInt32Value)1U };
 
            Border border1 = new Border();
            LeftBorder leftBorder1 = new LeftBorder();
            RightBorder rightBorder1 = new RightBorder();
            TopBorder topBorder1 = new TopBorder();
            BottomBorder bottomBorder1 = new BottomBorder();
            DiagonalBorder diagonalBorder1 = new DiagonalBorder();
 
            border1.Append(leftBorder1);
            border1.Append(rightBorder1);
            border1.Append(topBorder1);
            border1.Append(bottomBorder1);
            border1.Append(diagonalBorder1);
 
            borders1.Append(border1);
 
            CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
            CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
 
            cellStyleFormats1.Append(cellFormat1);
 
            CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
            CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
            CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
            CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
            CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
 
            cellFormats1.Append(cellFormat2);
            cellFormats1.Append(cellFormat3);
            cellFormats1.Append(cellFormat4);
            cellFormats1.Append(cellFormat5);
 
            CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
            CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
 
            cellStyles1.Append(cellStyle1);
            DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
            TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
 
            StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
 
            StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
            stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
 
            stylesheetExtension1.Append(slicerStyles1);
 
            stylesheetExtensionList1.Append(stylesheetExtension1);
 
            stylesheet1.Append(fonts1);
            stylesheet1.Append(fills1);
            stylesheet1.Append(borders1);
            stylesheet1.Append(cellStyleFormats1);
            stylesheet1.Append(cellFormats1);
            stylesheet1.Append(cellStyles1);
            stylesheet1.Append(differentialFormats1);
            stylesheet1.Append(tableStyles1);
            stylesheet1.Append(stylesheetExtensionList1);
            return stylesheet1;
        }
    }
}

Hope this can help you and just feel free to follow up after you have tried.

Best Regards,

Troubleshooting:

Excel cannot open the file “Report.xlsx” because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

I want to export to excel 2007

Keep in mind, .xlsx is not a simple spreadsheet. Starting with Office 2007, the file formats are actually zipped files that contain files within them. If you have it working exporting to XLS format, don’t try to change it to xlsx format since just using a different MIME type and a different file extension don’t work.

C#: Insert a worksheet in existing excel file

Scenario:

I have an input csv file. I have to insert an worksheet into that csv file.

Solution:

Add the following #namespaces

/* extra name spaces
*add Microsoft.CSharp
*add a COM reference in your project to the “Microsoft Excel 11.0 Object Library”
*/
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Marshal = System.Runtime.InteropServices.Marshal; //to release the resources consumed by excel app
using XlPlatform=Microsoft.Office.Interop.Excel.XlPlatform;

 

Code:

public void Main()
{
String fileName = Dts.Variables[“User::rawFileName”].Value.ToString();
InsertWorkSheet2(fileName);
}

private void InsertWorkSheet2(String fileLoc)
{
Excel.Application excelApp=null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;

try
{
FileInfo file = new FileInfo(fileLoc);
if (file.Exists)
{
excelApp = new Excel.Application();
/*display the excel operations including opening and saving file
excelApp.Visible = true;*/
excelApp.DisplayAlerts = false;
workbook = excelApp.Workbooks.Open(fileLoc, ReadOnly: false, Editable:true);
/*activate the workbook
to remove ambiguity between method _Workbook.Activate warning type cast*/
((Microsoft.Office.Interop.Excel._Workbook)workbook).Activate();

sheets = workbook.Sheets;
/*add the worksheet at the end of the other worksheet*/
newSheet = (Excel.Worksheet)sheets.Add(After: workbook.Sheets[workbook.Sheets.Count]);
String strSheetName = “My New Sheet” + (sheets.Count-1) ;
newSheet.Name = strSheetName;
((Microsoft.Office.Interop.Excel._Worksheet)newSheet).Activate();
newSheet.Cells[5, 6] = “BOO hoo!”;
/*save as xls file works – it creates new worksheet, give a name of the new worksheet, and save changes*/
/*workbook.SaveAs(fileLoc, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);*/
/*Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared mode stops user from changing*/
/*Excel.XlFileFormat.xlWorkbookNormal Specifies the file format when saving the spreadsheet. If you do not choose the right format you will see error while opening the file in windows.*/

/* xlCSVMSDOS format can have only one worksheet*/

workbook.SaveAs(fileLoc, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

/*check all the existing worksheet in sheets
foreach (Excel.Worksheet ws in sheets)
{
MessageBox.Show(ws.Name);
}
*/

workbook.Close();
excelApp.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
finally
{
/*Release the resources. If you do not release the resources properly.
* File will be still opened by the process and it stops the user from modifying and even from opening*/
Marshal.ReleaseComObject(newSheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);

newSheet = null;
sheets = null;
workbook = null;
excelApp = null;

GC.Collect();
}
}

SSIS C#: Load an CSV file – handle any change in the number or name of the columns

Load Excel File Dynamically Into Database Using SQLBulkCopy and GetOleDbSchemaTable in C#

Previously, I have posted some SSIS solutions which would handle dynamic column mapping in data loading. This time, I was wondering how to handle dynamic column mapping when the source is an excel file. I was quick to realize that it would be entirely a C# solution and the program should be able to handle any change in the number or name of columns. In simple words, you give any excel file and the program will load it into database. I used GetOleDbSchemaTable in order to fetch the schema information from the excel file, then built a similar table in the database. Excel data types and SQL data types do not match. Sometimes, they make me panic while loading files using SSIS. In my program also, I had to do some change in the data types; like WChar becomes NVARCHAR, Double becomes INT. Yes, I could use an XML file/database table to save a detailed mapping of this conversion of excel data types to SQL data types to make my program more capable and versatile. But, the conversion is hard-coded at the moment. Another challenge was to get all the data loaded to SQL table without using any column names or hard coded column mapping. SQLBulkCopy came to my rescue. By the way, I had to use a dataview over the table returned by GetOleDbSchemaTable because it was returning the column names in alphabetical order. So, I had to sort the rows using “ORDINAL_POSITION”

using System;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Data;

using System.Windows;

namespace ConsoleApplication2

{

class Program

{

public static void Main()

{

try

{

///*****************Establish Excel Connection*****************///

String strExcelConn = “Provider=Microsoft.ACE.OLEDB.12.0;”

+ “Data Source=C:\\files\\Book1.xlsx;”

+ “Extended Properties=’Excel 8.0;HDR=Yes’”;

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbCommand cmdExcel = new OleDbCommand();

cmdExcel.Connection = connExcel;

///*****Get schema info and Read Sheet Name and save it as TableName *******//

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string TableName = dtExcelSchema.Rows[0][“TABLE_NAME”].ToString();

string Query = “CREATE TABLE “ + TableName + “(“;

/*****Get schema info and read the column names and build Query to Create similar SQL table*****/

//

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] {null, null, TableName, null });

connExcel.Close();

string FieldNames = “”;

DataView dv = new DataView(dtExcelSchema);

dv.Sort = “ORDINAL_POSITION”;

foreach (DataRowView rowView in dv)

{

DataRow row = rowView.Row;

Query += row[“COLUMN_NAME”].ToString() + ” “ + ((OleDbType)row[“DATA_TYPE”]).ToString() +“,”;

FieldNames += row[“COLUMN_NAME”].ToString() + “,”;

}

/////******************Replace data types************/////

Query = Query.TrimEnd(“,”.ToCharArray()) + “)”;

Query = Query.Replace(“WChar”, “NVARCHAR(500)”);

Query = Query.Replace(“Double”, “INT”);

FieldNames = FieldNames.TrimEnd(“,”.ToCharArray()) + “”;

/******************read records from excel file************/

cmdExcel.CommandText = “Select “ + FieldNames + ” from [“ + TableName + “]”;

DataSet ds = new DataSet();

OleDbDataAdapter da = new OleDbDataAdapter();

connExcel.Open();

da.SelectCommand = cmdExcel;

da.Fill(ds);

connExcel.Close();

//******** Establish SQL Server connection and create table*****//

SqlConnection conn = new SqlConnection();

string sqlConn = “Data Source=YourServerName;Initial Catalog = YourDBName; Integrated Security = SSPI;Persist Security Info=False;”;

conn.ConnectionString = sqlConn;

SqlCommand cmd = new SqlCommand(Query);

cmd.Connection = conn;

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

//*****************Do bulk copy to table*****************//

DataTable dt = new DataTable();

dt = ds.Tables[0];

SqlBulkCopy sbc = new SqlBulkCopy(conn);

sbc.DestinationTableName = TableName;

conn.Open();

sbc.WriteToServer(dt);

conn.Close();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

}

}

}

 

SSIS C#: Load Text /CSV files with same or less columns than destination table by using Script Task

Scenario: Download Script

We get multiple text of csv files from different clients those we need to load to our dbo.Customer table. Not a problem, we can create an SSIS Package that can load multiple text files to SQL Server table and we have already did by using thispost.But there is little problem. Each client will not send exact number of columns what we have for our table.
Create table dbo.Customer(
Id INT,
Name VARCHAR(100),
Dob Date)




Think about that client A sends always file with id, name and dob columns.
Client B can send file with Id and Name. Also Client B can send some files with three columns id,name and dob.
Client C can also send the file with only column Name and Dob or any other combination.
One thing to noticed here, the files can have same columns or less but will never have more columns than SQL Server table. We need to create an SSIS Package that should be able to load these type of files.

This package should be like template, that we can use for any type of related requirement and can change destination table name,Source folder, Archive Folder, File Extension and File Delimiter etc. by using SSIS Configuration.

Sample data files using for my demo. As you can see that I have three files each one has different combination of columns but all matches with columns  of SQL Server table.

Solution:

We will be using Script Task in SSIS Package to load all the text files to SQL Server table with same or less columns than table definition.
Step 1: Create New SSIS Package and Variables 
Open SSDT ( SQL Server Data Tools) and create new SSIS Package. After that go ahead and create variables so we can use them in configuration to pass values anytime our requirement change.
ArchiveFolder: Provide the folder path where you would like to move files after loading. Datetime part will be added to file name.
DestinationTable : Provide the Destination Table name with Schema as shown where you would like to load flat files( text files or csv files)
FileDelimiter : Provide the delimiter which is used in your txt or csv files.
FileExtension : Provide the Extension of files you would like to load from folder.
LogFolder : Provide the folder path where you would like to create log file in case of error in script task
SourceFolder: Provide the source folder path where text files or csv files are places for import process.
Create Variable in SSIS Package to Load and Archive flat files by using Script Task -SSIS tutorial

Step 2:

Click in Connection Manager Pane and then Create ADO.NET Connection by providing Server Name and database Name. After creating I have renamed it to DBConn.
Create ADO.NET Connection so we can use in Script Task to load the data to Destination Table
 
Step 3: Add Variables to Script Task to use from SSIS Package
Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.
Add variables to Script Task so we can load multiple Text files or csv files to SQL Server Table in SSIS Package
Step 4: Add Script to Script task Editor in SSIS Package to import multiple CSV files or Text Files to SQL Server Table
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO;
using System.Data.SqlClient;
Under public void Main() {
I have added below code.

 

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {

                //Declare Variables
            string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();
            string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
            string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
            string TableName = Dts.Variables["User::DestinationTable"].Value.ToString();
            string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
                //string ColumnList = "";

                //Reading file names one by one
           string SourceDirectory = SourceFolderPath;
           string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
                foreach (string fileName in fileEntries)
                {

           SqlConnection myADONETConnection = new SqlConnection();
           myADONETConnection = (SqlConnection)
           (Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);

                    //Writing Data of File Into Table
                    int counter = 0;
                    string line;
                    string ColumnList = "";
                    //MessageBox.Show(fileName);

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        if (counter == 0)
                        {
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                        }
                        else
                        {
                      string query = "Insert into " + TableName + " (" + ColumnList + ") ";
                      query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

                            // MessageBox.Show(query.ToString());
                        SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                            myCommand1.ExecuteNonQuery();
                        }

                        counter++;
                    }

                    SourceFile.Close();
                    //move the file to archive folder after adding datetime to it
                    File.Move(fileName, ArchiveFolder + "\\" + 
                        (fileName.Replace(SourceFolderPath,"")).Replace(FileExtension,"") 
                        + "_" + datetime+FileExtension);
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
            }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString()
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

            }

 

 

 

Step 5: Save Script and Run SSIS Package
Save the script in Script Editor and execute your SSIS Package, It should load the files from Source folder to SQL Server table and move the file to archive folder after loading it.
Here are records loaded by package in dbo.Customer table from my sample files.
Data load to Table from Multiple Text files with less or same Columns as Table definition in SSIS Package by using Script Task
 Files moved to Archive Folder after datetime added to them as shown below.
How to load CSV files with same or less columns to SQL server Table in SSIS Package

SSIS: Loop over folder and Archiving files

Scenario:

I want to move all the files saved in a certain folder.

Solution:

I can achieve this using the

  1. Foreach loop container
  2. File system task/Script task

Script task code:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;//added
using System.Text.RegularExpressions;//added
#endregion
public void MoveFile()
{
/*rename file and move*/
string package_name = Dts.Variables[“User::package_name”].Value.ToString().Trim();
string file_diretory_name = Dts.Variables[“User::package_associated_filename”].Value.ToString().Trim();
string archive_directory = Dts.Variables[“User::package_archive_directory”].Value.ToString().Trim();
string file_name = “”;
Regex regex = new Regex(@”\w*[.]+\w*”);
Match match = regex.Match(file_diretory_name);
if (match.Success)
{
file_name = match.Value;
}

int pos = file_name.IndexOf(‘.’);
string filename_without_extension = file_name.Substring(0, pos);
string file_extension = file_name.Substring(pos);

MessageBox.Show(filename_without_extension.ToString());
string date_now = DateTime.Now.ToString(“_yyyyMMdd_HHmmss”).Trim();
string source_file = file_diretory_name;
string destination_file = archive_directory + filename_without_extension + date_now + file_extension;
System.IO.File.Copy(source_file, destination_file, true);
}