Extract Sheet names in Excel

Extract sheet names in a excel and return a String array of Sheet Names.


Params

1. Excel File path

   private String[] GetExcelSheetNames(string excelFile)

        {

            OleDbConnection objConn = null;

            System.Data.DataTable dt = null;

            try

            {

                // Connection String. Change the excel file to the file you  will search.

                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

                    "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";

                // Create connection object by using the preceding connection string.

                objConn = new OleDbConnection(connString);

                // Open connection with the database.

                objConn.Open();

                // Get the data table containg the schema guid.

                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)

                {

                    return null;

                }

                String[] excelSheets = new String[dt.Rows.Count];

                int i = 0;

                // Add the sheet name to the string array.

                foreach (DataRow row in dt.Rows)

                {

                    excelSheets[i] = row["TABLE_NAME"].ToString();

                    i++;

                }

                // Loop through all of the sheets if you want too...

                for (int j = 0; j < excelSheets.Length; j++)

                {

                    // Query each excel sheet.

                }

                return excelSheets;

            }

            catch (Exception ex)

            {

                return null;

                // TODO log the exception some where

            }

            finally

            {

                // Clean up.

                if (objConn != null)

                {

                    objConn.Close();

                    objConn.Dispose();

                }

                if (dt != null)

                {

                    dt.Dispose();

                }

            }

        }

Export to Excel

You display data in a grid with formatting and feel you have completed the task, but find out that you have been asked to export the data to excel!!!???!!! Confused...Want to kill the guy who gave the idea..Even i did had the same idea.


Found out many programs to export to Excel, but poor performance was a pain in the (you know what!!!!) finally came across with the following snippet


Params

1. DataTable
2. Output file path

I've added a Excel Cell format function (kindly bare with my formatting skills)

public static void ExportToExcel(System.Data.DataTable dataTable, string outputPath)

{

// Create the Excel Application object

            Application excelApp = new Application();

// Create a new Excel Workbook

Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

int sheetIndex = 0;

// Copy the DataTable to an object array

object[,] rawData = new object[dataTable.Rows.Count + 1, dataTable.Columns.Count];

// Copy the column names to the first row of the object array

for (int col = 0; col < dataTable.Columns.Count; col++)

{

rawData[0, col] = dataTable.Columns[col].ColumnName;

}

// Copy the values to the object array

for (int col = 0; col < dataTable.Columns.Count; col++)

{

for (int row = 0; row < dataTable.Rows.Count; row++)

{

rawData[row + 1, col] = dataTable.Rows[row].ItemArray[col];

}

}

// Calculate the final column letter

string finalColLetter = string.Empty;

string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

int colCharsetLen = colCharset.Length;

if (dataTable.Columns.Count > colCharsetLen) {

finalColLetter = colCharset.Substring(

(dataTable.Columns.Count - 1) / colCharsetLen - 1, 1);

}

finalColLetter += colCharset.Substring(

(dataTable.Columns.Count - 1) % colCharsetLen, 1);

                

// Create a new Sheet

Worksheet excelSheet = (Worksheet) excelWorkbook.Sheets.Add(

excelWorkbook.Sheets.get_Item(++sheetIndex),

Type.Missing,1, XlSheetType.xlWorksheet);

excelSheet.Name = "Excel details";

                int count = 0;

                foreach (Microsoft.Office.Interop.Excel.Worksheet work in excelWorkbook.Worksheets)

                {                   

                    if (work.Name.ToString() .Contains("Sheet"+count))

                    {

                        work.Delete();

                        //delete worksheet

                    }

                    count++;

                }

// Fast data export to Excel

string excelRange = string.Format("A1:{0}{1}",

finalColLetter, dataTable.Rows.Count + 1);

excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

                excelSheet.get_Range(excelRange, Type.Missing).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                excelSheet.get_Range(excelRange, Type.Missing).Columns.AutoFit();

                setFormat(excelSheet.get_Range(excelRange, Type.Missing).Columns);

                // Mark the first row as BOLD

                ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;

                ((Range)excelSheet.Rows[1, Type.Missing]).Font.Size = 12;

// Save and Close the Workbook

excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

excelWorkbook.Close(true, Type.Missing, Type.Missing);

excelWorkbook = null;

// Release the Application object

excelApp.Quit();

excelApp = null;

// Collect the unreferenced objects

GC.Collect();

GC.WaitForPendingFinalizers();

}

// Set the format for the Excel Cells

 private static void setFormat(Excel.Range range)

        {

            range.Select();

            range.Font.Size = 11;

            range.WrapText = true;

            range.HorizontalAlignment = Excel.Constants.xlLeft;

            range.Interior.ColorIndex = 34;

            range.Borders.Weight = 3;

            range.Borders.LineStyle = Excel.Constants.xlSolid;

            range.Cells.RowHeight = 15;

        }

Load csv data to MySQL using BulkLoader

C#.Net and MySql ????? You don't need to scratch your head, since MySql developer team is kind enough to provide the ADO.NET connector. You can download the connector from the following http://dev.mysql.com/downloads/connector/net/5.1.html

Tats about MySqlConnector, lets come back to the program. 

Params to program

1. The csv/txt file path

 static void Main(string[] args)

        {

            string connStr = "server=localhost;user id=user id;password=password;database=database";

            // MySql Connection Object

            MySqlConnection conn = new MySqlConnection(connStr);

            //  csv file path

            string file = @"filepath";

            // MySQL BulkLoader

            MySqlBulkLoader bl = new MySqlBulkLoader(conn);

            bl.TableName = "tablename";

            bl.FieldTerminator = "|"; This can be {comma,tab,semi colon, or other character}

            bl.LineTerminator = "\n";

            bl.FileName =file; 

            try

            {

                Console.WriteLine("Connecting to MySQL...");

                conn.Open();

                // Upload data from file

                int count = bl.Load();

                Console.WriteLine(count + " lines uploaded.");

                conn.Close();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.ToString());

            }

            Console.WriteLine("Done.");

            Console.ReadLine();

         

        }

Sub-String a String from End

Have you come across a scenario where you need to sub-string a string from the end, where you know a defined set of characters to be removed. Following is the snippet explains you how


private string removeFromEnd(string source, string subString)
        {
            string retVal = string.Empty;
            int sourceLength = source.Length;
            int subStringLength = subString.Length;
            int pos = sourceLength - subStringLength;
            retVal = source.Substring(0, pos);


            return retVal;
        }

Excel Functions - Left Trim, Mid Trim, Right Trim

LeftTrim function extracts a substring from a string, starting from the left-most character


private string LeftTrim(string param, int length)
        {
            //we start at 0 since we want to get the characters starting from the
            //left and with the specified lenght and assign it to a variable
            string result = param.Substring(0, length);
            //return the result of the operation
            return result;
        }


RightTrim function extracts a substring from a string starting from the right-most character


private string RightTrim(string param, int length)
        {
            //start at the index based on the lenght of the sting minus
            //the specified lenght and assign it a variable
            string result = param.Substring(param.Length - length, length);
            //return the result of the operation
            return result;
        }


MidTrim function extracts a substring from a string (starting at any position)

 private string MidTrim(string param, int startIndex, int length)
        {
            //start at the specified index in the string ang get N number of
            //characters depending on the lenght and assign it to a variable
            string result = param.Substring(startIndex, length);
            //return the result of the operation
            return result;
        }