Tuesday, 28 April 2020

Split XLSX To CSV using C#


string path="your_xlsx_file_path";
string[] strTabs = new[] { "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10"};
SplitExcelToAllCSV(strTabs, path);


private void SplitExcelToAllCSV(string[] SheetNames, string InputFilePath)
{
try
{
XL.Application xlApp = null;
XL.Workbook xlWorkbook = null;
XL.Worksheet xlWorksheet = null;
XL.Range xlRange = null;
xlApp = new XL.Application();
xlWorkbook = xlApp.Workbooks.Open(InputFilePath);
foreach (var SheetName in SheetNames)
{
xlWorksheet = xlWorkbook.Sheets[SheetName];
xlRange = xlWorksheet.UsedRange;
int lastUsedColumn = xlRange.Columns.Count;
int lastUsedRow = 4;//customize this according to requirment
try
{
lastUsedRow = xlWorksheet.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
XL.XlSearchOrder.xlByRows, XL.XlSearchDirection.xlPrevious,
false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
}
catch { }
object[,] objdata = xlRange.Value2;
StringBuilder sb = new StringBuilder();
for (int xlr = 4; xlr <= lastUsedRow; xlr++)
{
for (int xlc = 1; xlc <= lastUsedColumn; xlc++)
{
sb.Append(objdata[xlr, xlc] + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine();
}
File.Delete(Application.StartupPath + @"\" + SheetName + ".csv");
File.WriteAllText(Application.StartupPath + @"\" + SheetName + ".csv", sb.ToString());
Marshal.ReleaseComObject(xlWorksheet);
}
xlWorkbook.Close(false, System.Type.Missing, System.Type.Missing);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
}
catch
{
}
}

No comments:

Post a Comment