We are working on developing an web application in C#(VS 2010) which will read an excel file and extract the data and insert that data in SQL server. The application will read multiple excel files, and before it reads the next file we are closing the older file. For reading the file we are making the object of excel file as :
1)Excel._Application excelApp = null;
2)Excel.Workbook excelWorkbook = null;
3)Excel.Sheets excelsheets = null;
4)excelApp = new Excel.Application();
5)excelWorkbook = excelApp.Workbooks.Open(filePath, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
6)excelsheets = excelWorkbook.Worksheets;
But we are encountering following(screenshot) COM Exception at line no 6, which is saying that application is busy, but there is no excel application running when we are making the object.
Can anyone help us regarding resolving this issue, If some other things have to be done in order to read excel file.
Actually use of interop.excel is going to slow your execution. I think to read an excel it’s better to use Oledb Connection.
I have written a function to read excel try this out, hope it’s going to work for you.
Another thing if you want to stick to the interop then you can close the excel and then implement IDisposable interface, and in the dispose() release the excel objects you have created by using System.Runtime.InteropServices.Marshal.ReleaseComObject(excelobject);
excelobject = null;
GC.Collect();
//Function to read Excel file using OLEDB connection.
public DataTable ReadExcelSheet(string strFilePath, string strSqlQuery)
{
System.IO.FileInfo objFileInfo = new System.IO.FileInfo(strFilePath);
string strConnString = string.Empty;
DataTable dtResultExcelTable = null;
if (objFileInfo.Extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
strConnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFilePath + "; Extended Properties=Excel 8.0;";
}
else
{
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFilePath + "; Extended Properties=Excel 8.0;";
}
using (OleDbConnection objConnection = new OleDbConnection(strConnString))
{
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(strSqlQuery, objConnection);
dtResultExcelTable = new DataTable();
objConnection.Open();
objDataAdapter.Fill(dtResultExcelTable);
objConnection.Close();
}
return dtResultExcelTable;
}
//Calling that ReadExcelSheet function.
string strFilePath = @"C:\Test\TestExcelSheet.xlsx";
string strSqlQuery = "SELECT * FROM [Sheet1$] WHERE ID='1'";
DataTable dtExcelSheet = ReadExcelSheet(strFilePath, strSqlQuery);
ANSWER:-2I am not sure in which module your using that code.
I am sending you the code which is useful for you to open an excel ,save an excel format, read excel, insert picture on excel, set background to excel etc.
The below code might be help full to you.
Sorry to spam your mails everyone. This might help full to you alsoJ
using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WorkingwithExcelSheets
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnAddExcel_Click(object sender, EventArgs e)
{
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorksheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Add(misValue);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
xlWorksheet.Cells[1, 1] = "chandrasekhar";
xlWorkbook.SaveAs("testexcelfile.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkbook.Close(true, misValue, misValue);
xlApp.Quit();
releaseobject(xlWorksheet);
releaseobject(xlWorkbook);
releaseobject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\testexcelfile.xls");
}
catch (Exception x)
{
MessageBox.Show("Error Message:"+x.Message);
}
}
private void btnOpenExcel_Click(object sender, EventArgs e)
{
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorksheet;
object misvalue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Open("testexcelfile.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
MessageBox.Show(xlWorksheet.get_Range("A1","A1").Value2.ToString());
xlWorkbook.Close(true, misvalue, misvalue);
xlApp.Quit();
releaseobject(xlWorksheet);
releaseobject(xlWorkbook);
releaseobject(xlApp);
}
catch (Exception x)
{
MessageBox.Show("Error Message:"+x.Message);
}
}
private void btnReadExcel_Click(object sender, EventArgs e)
{
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt = 0;
int cCnt = 0;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open("testexcelfile.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
MessageBox.Show(str);
}
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
releaseobject(xlWorkSheet);
releaseobject(xlWorkBook);
releaseobject(xlApp);
}
catch (Exception x)
{
MessageBox.Show("Error Message:"+x.Message);
}
}
private void btnFormatExcel_Click(object sender, EventArgs e)
{
try{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Excel.Range chartRange;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
---------
xlWorkSheet.get_Range("b2", "e3").Merge(false);
chartRange = xlWorkSheet.get_Range("b2", "e3");
chartRange.FormulaR1C1 = "MARK LIST";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
chartRange = xlWorkSheet.get_Range("b4", "e4");
chartRange.Font.Bold = true;
chartRange = xlWorkSheet.get_Range("b9", "e9");
chartRange.Font.Bold = true;
chartRange = xlWorkSheet.get_Range("b2", "e9");
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkBook.SaveAs("testexcelfile.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseobject(xlApp);
releaseobject(xlWorkBook);
releaseobject(xlWorkSheet);
MessageBox.Show("File created !");
}
catch (Exception x)
{
MessageBox.Show("Error Message:"+x.Message);
}
}
private void btnInsertPicture_Click(object sender, EventArgs e)
{
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add some text
xlWorkSheet.Cells[1, 1] = "http://csharp.net-informations.com";
xlWorkSheet.Cells[2, 1] = "Adding picture in Excel File";
try
{
xlWorkSheet.Shapes.AddPicture(@"C:\Users\chandra.j.vssekhar\Documents\Accenture.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 50, 396, 127);
}
catch (Exception x)
{ MessageBox.Show("error message:" + x.Message); }
xlWorkBook.SaveAs("testexcelfile.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseobject(xlApp);
releaseobject(xlWorkBook);
releaseobject(xlWorkSheet);
MessageBox.Show("File created !");
}
catch(Exception x)
{
MessageBox.Show("Error Message"+x.Message);
}
}
private void btnBackgroundPicture_Click(object sender, EventArgs e)
{
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.SetBackgroundPicture(@"C:\Users\chandra.j.vssekhar\Documents\Accenture.jpg");
//add some text
xlWorkSheet.Cells[1, 1] = "http://csharp.net-informations.com";
xlWorkSheet.Cells[3, 1] = "Adding background in Excel File";
xlWorkBook.SaveAs("testexcelfile.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseobject(xlApp);
releaseobject(xlWorkBook);
releaseobject(xlWorkSheet);
MessageBox.Show("File created !");
}
catch(Exception x)
{
MessageBox.Show("Error Message:"+x.Message);
}
}
private void btnCreateExcelChart_Click(object sender, EventArgs e)
{
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
------
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
xlWorkBook.SaveAs("testexcelfile.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseobject(xlWorkSheet);
releaseobject(xlWorkBook);
releaseobject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\testexcelfile.xls");
}
catch(Exception x)
{
MessageBox.Show("Error Message :"+x.Message);
}
}
private void releaseobject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
by
Sekar
ANSWER :-3
You can use the following code to read the excel file. It uses OleDb.
class Program
{
private string excelFileName;
public Program(string excelFileName)
{
this.excelFileName = excelFileName;
}
private DbConnection GetConnection()
{
return new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0;"
+ ";Data Source=" + excelFileName
+ ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=Yes;\""
);
}
//Use this if there are multiple sheets in a workbook
public string[] GetExcelSheetName()
{
System.Data.OleDb.OleDbConnection dbConn = null;
try
{
dbConn = (System.Data.OleDb.OleDbConnection)GetConnection();
dbConn.Open();
System.Data.DataTable dt = dbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelFile = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelFile[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelFile;
}
catch (Exception e)
{
return null;
}
finally
{
if (dbConn != null)
{
dbConn.Close();
dbConn.Dispose();
}
}
}
// This method will give specific (column,row) data
public string GetData(int rows, int cols, string tableName)
{
string data = null;
DbCommand cmd = GetConnection().CreateCommand();
cmd.CommandText = "SELECT * FROM " + tableName;
try
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
int rowNum = rows;
int colNum = cols;
int row = 0;
while (reader.Read() && row < rowNum)
{
for (int col = 0; col <= colNum; col++)
{
if (reader.IsDBNull(colNum))
{
data = null;
}
else
{
data = reader.GetValue(colNum).ToString();
}
}
row++;
}
}
finally
{
cmd.Connection.Close();
}
return data;
}
static void Main(string[] args)
{
String[] sheetNames = null;
sheetNames = new String[20];
String value=null;
Program pgm = new Program(@"C:\User\Desktop\Test\Test.xlsx”);
sheetNames = pgm.GetExcelSheetName();
for (int j = 0; j < sheetNames.Length; j++)
{
value =pgm.GetData(3, 0, "[" + sheetNames[j] + "]") // 4th Row, 1st Column Data
}
//
Rest Code
//
}
}
By Gupta
Note : Provide your comments by clicking below options! Thanks ! :)
0 comments:
கருத்துரையிடுக