- String StrConn;
- StrConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
- + Excelpath +";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
- String StrConn;
- StrConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
- + Excelpath +";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
Use this function to convert excel to csv:
- public int ExcelToCVS_Nostro(string Excelpath, string SheetName, string CSVpath, string report_date, string strFileType)
- {
- string StrConn = null;
- OleDbDataAdapter DA = new OleDbDataAdapter();
- DataSet DS = new DataSet();
- OleDbDataReader DR = default(OleDbDataReader);
- string Str = null;
- int ColumnCount = 0;
- int OuterCount = 0;
- int InnerCount = 0;
- int RowCount = 0;
- if (strFileType.Trim() == ".xls")
- {
- StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excelpath +
- ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
- }
- else if (strFileType.Trim() == ".xlsx")
- {
- StrConn =
- "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excelpath +
- "; Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
- }
- OleDbConnection objConn = new OleDbConnection(StrConn);
- try
- {
- if (objConn.State == ConnectionState.Closed)
- {
- objConn.Open();
- }
- else
- {
- }
- }
- catch (Exception ex)
- {
- return 0;
- }
- OleDbCommand objCmd = new OleDbCommand("Select * from [" + SheetName + "$]", objConn);
- objCmd.CommandType = CommandType.Text;
- StreamWriter ObjStreamWriter = default(StreamWriter);
- ObjStreamWriter = new StreamWriter(CSVpath);
- int Count = 0;
- Count = 0;
- try
- {
- DA.SelectCommand = objCmd;
- DA.Fill(DS;
- "XLData");
- }
- catch (Exception ex)
- {
- }
- RowCount = DS.Tables[0].Rows.Count - 4;
- ColumnCount = DS.Tables[0].Columns.Count;
- for (OuterCount = 0; OuterCount <= DS.Tables[0].Rows.Count - 1; OuterCount++)
- {
- Str = "";
- for (InnerCount = 1; InnerCount <= 14; InnerCount++)
- {
- if (DS.Tables[0].Rows[OuterCount][1].ToString().Trim() != string.Empty)
- {
- if (DS.Tables[0].Rows[OuterCount]
- [1].ToString().Trim().Contains("LONDON") == false && DS.Tables[0].Rows[OuterCount]
- [1].ToString().Trim().Contains("QAR") == false)
- {
- if (DS.Tables[0].Rows[OuterCount][2].ToString().Trim() != "INR")
- {
- Str += DS.Tables[0].Rows[OuterCount][InnerCount].ToString().Trim().Replace(
- ",", "").Replace(" 12:00:00 AM", "") + ",";
- }
- else
- break;
- }
- }
- else
- {
- break;
- }
- }
- else
- {
- break;
- }
- if (Str != string.Empty)
- {
- Str += report_date;
- ObjStreamWriter.WriteLine(Str);
- }
- }
- ObjStreamWriter.Close();
- try
- {
- }
- catch (Exception ex)
- {
- objCmd.Dispose();
- objCmd = null;
- objConn.Close();
- objConn.Dispose();
- objConn = null;
- return RowCount;
- }
- }
