Posted By

engebaja on 05/08/13


Tagged

csv excel convert xls c#


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

danfinrud55


Convert Excel file (XLS) to CSV


 / Published in: C#
 

URL: XLStoCSV

Convert Excel file (XLS) to CSV

  1. using System;
  2. using System.Data;
  3. using Microsoft.SqlServer.Dts.Runtime;
  4. using System.Windows.Forms;
  5. using Excel = Microsoft.Office.Interop.Excel;
  6. using Microsoft.Office.Interop.Excel;
  7. namespace ST_6dc747ff29cf41c6ac11b7c0bca33d19.csproj
  8. {
  9. [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
  10. public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  11. {
  12. #region VSTA generated code
  13. enum ScriptResults
  14. {
  15. Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
  16. Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
  17. };
  18. #endregion
  19. /*
  20.   The execution engine calls this method when the task executes.
  21.   To access the object model, use the Dts property. Connections, variables, events,
  22.   and logging features are available as members of the Dts property as shown in the following examples.
  23.   To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
  24.   To post a log entry, call Dts.Log("This is my log text", 999, null);
  25.   To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
  26.   To use the connections collection use something like the following:
  27.   ConnectionManager cm = Dts.Connections.Add("OLEDB");
  28.   cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
  29.   Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
  30.  
  31.   To open Help, press F1.
  32.  */
  33. private static Workbook mWorkBook;
  34. private static Sheets mWorkSheets;
  35. private static Worksheet mWSheet1;
  36. private static Excel.Application oXL;
  37. private static string ErrorMessage = string.Empty;
  38. public void Main()
  39. {
  40. try
  41. {
  42. string sourceExcelPathAndName = @"D:\Excel Import\Excel Import.xls";
  43. string targetCSVPathAndName = @"D:\Excel Import\Excel Import.csv";
  44. string excelSheetName = @"Sheet1";
  45. string columnDelimeter = @"|#|";
  46. int headerRowsToSkip = 0;
  47. if (ConvertExcelToCSV(sourceExcelPathAndName, targetCSVPathAndName, excelSheetName, columnDelimeter, headerRowsToSkip) == true)
  48. {
  49. Dts.TaskResult = (int)ScriptResults.Success;
  50. }
  51. else
  52. {
  53. Dts.TaskResult = (int)ScriptResults.Failure;
  54. }
  55. }
  56. catch (Exception ex)
  57. {
  58. Dts.TaskResult = (int)ScriptResults.Failure;
  59. }
  60. }
  61. public static bool ConvertExcelToCSV(string sourceExcelPathAndName, string targetCSVPathAndName, string excelSheetName, string columnDelimeter, int headerRowsToSkip)
  62. {
  63. try
  64. {
  65. oXL = new Excel.Application();
  66. oXL.Visible = false;
  67. oXL.DisplayAlerts = false;
  68. Excel.Workbooks workbooks = oXL.Workbooks;
  69. mWorkBook = workbooks.Open(sourceExcelPathAndName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
  70. //Get all the sheets in the workbook
  71. mWorkSheets = mWorkBook.Worksheets;
  72. //Get the specified sheet
  73. mWSheet1 = (Worksheet)mWorkSheets.get_Item(excelSheetName);
  74. Excel.Range range = mWSheet1.UsedRange;
  75. //deleting the specified number of rows from the top
  76. Excel.Range rngCurrentRow;
  77. for (int i = 0; i < headerRowsToSkip; i++)
  78. {
  79. rngCurrentRow = range.get_Range("A1", Type.Missing).EntireRow;
  80. rngCurrentRow.Delete(XlDeleteShiftDirection.xlShiftUp);
  81. }
  82. //replacing ENTER with a space
  83. range.Replace("\n", " ", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  84. //replacing COMMA with the column delimeter
  85. range.Replace(",", columnDelimeter, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  86. mWorkBook.SaveAs(targetCSVPathAndName, XlFileFormat.xlCSVMSDOS,
  87. Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
  88. Type.Missing, Type.Missing, Type.Missing,
  89. Type.Missing, false);
  90. return true;
  91. }
  92. catch (Exception ex)
  93. {
  94. ErrorMessage = ex.ToString();
  95. return false;
  96. }
  97. finally
  98. {
  99. if (mWSheet1 != null) mWSheet1 = null;
  100. if (mWorkBook != null) mWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
  101. if (mWorkBook != null) mWorkBook = null;
  102. if (oXL != null) oXL.Quit();
  103. System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
  104. if (oXL != null) oXL = null;
  105. GC.WaitForPendingFinalizers();
  106. GC.Collect();
  107. GC.WaitForPendingFinalizers();
  108. GC.Collect();
  109. }
  110. }
  111. }
  112. }

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: shameen on September 13, 2013

I use this tool: http://www.aspose.com/.net/excel-component.aspx to convert my excel files to csv and vice versa and this tool also convert excel files to many other formats.

You need to login to post a comment.