/*If you are facing issues with saving large data into excel
* u may get oledbexception like
* "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data"
* below is the solution for insert and updation of data
*/
private void btnSaveExcel_Click(object sender, EventArgs e)
{
string path = @"C:\TEMP\Testing.xls";
oXL =
new Microsoft.Office.Interop.Excel.Application();oXL.Visible =
false;oXL.DisplayAlerts =
false;mWorkBook = oXL.Workbooks.Open(path, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook mWorkSheets = mWorkBook.Worksheets;
//Get the sheet which was already existsmWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("FAS");Microsoft.Office.Interop.Excel.
Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;mWSheet1.Cells[rowCount + 1, 1] = txtId.Text;
mWSheet1.Cells[rowCount + 1, 2] = txtName.Text;
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.
XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//cleanup workbook and sheet
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);mWSheet1 =
null;mWorkBook =
null;oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private void btnUpdateExcel_Click(object sender, EventArgs e){
string path = @"C:\TEMP\Testing.xls";
oXL =
new Microsoft.Office.Interop.Excel.Application();oXL.Visible =
false;oXL.DisplayAlerts =
false;mWorkBook = oXL.Workbooks.Open(path, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook mWorkSheets = mWorkBook.Worksheets;
//Get the sheet which was already existsmWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("FAS");Microsoft.Office.Interop.Excel.
Range range = mWSheet1.get_Range("A1").Find(txtId.Text);
if (range != null){
//assume unique records are available for column A1int rowCount = range.Rows.Row;mWSheet1.Cells[rowCount, 2] = txtName.Text;
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.
XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//cleanup workbook and sheet
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);mWSheet1 =
null;mWorkBook =
null;oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
Solution for the QlikView, Biztalk, DotNet and MSBI real time development problems
Search This Blog
Wednesday, September 10, 2014
Subscribe to:
Posts (Atom)
Popular Posts
-
Error : Could not store transport type data for Receive Location 'EAISolutionReceiveRequestLocation' to config store. Prima...
-
I am getting below error while executing stored procedure from C# (Ado.net, entity framework) String[2]: the Size property has an invalid ...
-
Migration from Visual Studio 2008 to 2013 Without any code changes I successfully migrated from VS 2008 to 2013 but when running website f...
-
Exception : Microsoft.WindowsAzure.StorageClient.StorageClientException was unhandled Message=One of the request inputs is out of ran...
-
Review the permissions of the user that you're trying to impersonate. In my situation, I was only getting the error on my development ...