Question
Tech Mahindra Ltd
CA
Last activity: 16 Apr 2018 2:27 EDT
OLEDB not reading Alphanumeric value
Alphanumeric values alone not reading from source excel to Data grid through OLEDB Query
Used below Connection string in OLEDB .
Extended Properties="Excel 12.0 XML;HRD=""YES"";IMEX=1";Persist Security Info=False
ex: C45306
Accepted Solution
Updated: 16 Apr 2018 2:27 EDT
Pegasystems Inc.
IN
Hi Pavan,
Thanks so much for sharing the below resolution!
Below is the code to change the format of particular column.
using System.Data;
using System.Data.OleDb;
using System.IO;
Parameters: string filePath, string Sheetname
Result Type: bool
Hi Pavan,
Thanks so much for sharing the below resolution!
Below is the code to change the format of particular column.
using System.Data;
using System.Data.OleDb;
using System.IO;
Parameters: string filePath, string Sheetname
Result Type: bool
Microsoft.Office.Interop.Excel.Application oApp;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Workbook oBook;
oApp = new Microsoft.Office.Interop.Excel.Application();
oApp.Visible = false;
oApp.DisplayAlerts = false;
oBook = oApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
try
{
for (int i = oBook.Sheets.Count; i > 0 ; i--)
{
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets.get_Item(i);
if (oSheet.Name == Sheetname)
{
//int iRow = 0;
//for(int n = 0; n < oSheet.Rows.Count; n++)
//{
// iRow++;
//oSheet.Cells[1, iRow] = r.RowNum;
oSheet.Range["A2", "A1048576"].NumberFormat = "@";
}
}
// save
oBook.SaveAs(filePath);
oBook.Close();
oApp.Quit();
return true;
}
catch(Exception ex)
{
OpenSpan.Diagnostics.Diagnostic.PublishException(ex, null);
oBook.Close();
oApp.Quit();
return false;
}
Comptech Associates Inc
US
Which version of .NET Framework is running? I encountered some issues with .NET Framework 4.5.2. Some of Pega Robotics features require 4.6.1.
Does the datagrid already exist? Is the field set to string? You should check it before and after you read Excel to see what it is...
Accepted Solution
Updated: 16 Apr 2018 2:27 EDT
Pegasystems Inc.
IN
Hi Pavan,
Thanks so much for sharing the below resolution!
Below is the code to change the format of particular column.
using System.Data;
using System.Data.OleDb;
using System.IO;
Parameters: string filePath, string Sheetname
Result Type: bool
Hi Pavan,
Thanks so much for sharing the below resolution!
Below is the code to change the format of particular column.
using System.Data;
using System.Data.OleDb;
using System.IO;
Parameters: string filePath, string Sheetname
Result Type: bool
Microsoft.Office.Interop.Excel.Application oApp;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Workbook oBook;
oApp = new Microsoft.Office.Interop.Excel.Application();
oApp.Visible = false;
oApp.DisplayAlerts = false;
oBook = oApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
try
{
for (int i = oBook.Sheets.Count; i > 0 ; i--)
{
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets.get_Item(i);
if (oSheet.Name == Sheetname)
{
//int iRow = 0;
//for(int n = 0; n < oSheet.Rows.Count; n++)
//{
// iRow++;
//oSheet.Cells[1, iRow] = r.RowNum;
oSheet.Range["A2", "A1048576"].NumberFormat = "@";
}
}
// save
oBook.SaveAs(filePath);
oBook.Close();
oApp.Quit();
return true;
}
catch(Exception ex)
{
OpenSpan.Diagnostics.Diagnostic.PublishException(ex, null);
oBook.Close();
oApp.Quit();
return false;
}