.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Reading and Writing Excel Spreadsheets Using ADO.NET C#

Posted By: Syed Shakeer Hussain     Posted Date: October 02, 2010    Points: 2   Category :ADO.Net
 
1)http://support.microsoft.com/search/default.aspx?mode=r&query=how+to+transfer+dataset+to+an+excel+workbook+by+using+asp.net&spid=global&catalog=LCID%3D1033&1033comm=1&ast=25&ast=28&res=20

2)Exporting datagrid to Excel
http://www.codersource.net/asp-net/asp-net-miscellaneous/exporting-data-grid-to-excel-using-c.aspx


View Complete Post


More Related Resource Links

Reading and Writing Data to/from Excel File

  

I need to read and write data from excel sheet. Firstly for example I need to use the concept of accessing workgroup, sheet and then cells whatever I want.

For example I need to use cell A1+B1 and put the result in C1

alternatively I can use the same function above and put data in some other sheet of same excel file.

Please reply the answer either using C#.NET or asp.net using C#.NET


Reading and Writing Images From a Windows Mobile Database using UltraLite 10(C#)

  
Periodically I get a request for information on how to read and write binary data to a database running on Windows Mobile. If you search the Internet you can typically find examples that are available on Windows Desktops or allow you to read and write to a local file system. The problem is that it can take a bit of work to get this code to work on Windows Mobile accessing a database.

Ultimately you might be asking, why would I want to store and image in a database? Well in an environment where you synchronizing data between a local mobile database and a consolidated (central) database this can be extremely useful. Imagine if an insurance adjuster went to an accident scene, took a picture of a damaged car, loaded it into his Windows Mobile database and then replicated that image up to the Insurance headquarters for approval. All of this could be done in a very short period of time when using images in the database. Another good example might be a doctor who was waiting for a patient chart to become available. If you could store the image in a database this chart could be sent down to the doctor's device once it became available.

For this article I am not going to get into how to synchronize the images to and from a remote and central database as this is typically fairly straightforward when using a data synchronization technologies like MobiLink

XML Reading and Writing

  
We learned to process XML files using the Document Object Model as implemented by the XmlDocument class. To go further and make XML friendlier, the .NET Framework provides many other classes for different purposes, allowing you to create and manage nodes from custom .NET classes.
Besides the XmlDocument and the derived classes of XmlNode, the .NET Framework provides the XmlTextWriter class, which is derived from XmlWriter. The XmlTextWriter class works in a top-down approach to create, or deal with, the contents of an XML file. This class writes an XML node and moves down without referring

Excel: Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services

  

Excel 2003 lets you dynamically integrate the data provided by different Web services. It also lets you take advantage of the latest capabilities in Office 2003 to customize list views, graphs, and charts, and to catalog bulk items online or offline. Find out how you can makle the most of the data returned from your Web services with the Office 2003 Web Services Toolkit API.

Alok Mehta

MSDN Magazine February 2005


reading excel file problem

  

Hi,


i have 200 rows in my excel file. im using OleDbConnection to read the excel file.

The problem is that it will read all the blank rows from row 200 onwards. Is there a configuration im missing ? or is there a way to import all rows that has data? Here's some of my code.


string excelConnectionString =
               "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + filePath + ";" +
                "Extended Properties=Excel 8.0";

OleDbConnection excelConnection =
                    new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO'");


OleDbCommand cmd = new OleDbCommand("Select * from [list$]",excelConnection);







Reading an excel file

  

Hi,

i am trying to read an excel file.

when i read the entire file, it works fine.

but when i try to read a single column, i get the following exception message:

"Could not find installable ISAM".

the code i am using is:

string  connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +  

 

"Data Source="+ fileName + ";" +

 

"Extended Properties=Excel 12.0;HDR=Yes";

OleDbConnection oledbConn = new OleDbConnection(connString);

 

try

{

// Open connection

oledbConn.Open();

 

// Create OleDbCommand object and select data from worksheet Sheet1

OleDbCommand cmd = new OleDbCommand

Reading/writing Word documents - keeping formatting

  

I've done some reading about reading from and writing to a Word doc. The reading from worked without issue:

string strPath = "Test.doc";
FileStream fStream = new FileStream
    (strPath, FileMode.Open, FileAccess.Read);
StreamReader sReader = new StreamReader(fStream);
string text = sReader.ReadToEnd();
sReader.Close();


But of course this is just a string, without any formatting information included. Writing to the document using a StreamWriter worked, but I received errors when opening the document.

I've also read that trying to perform actions on an Office document on a server without the Office products installed on it can result in mayhem. I'm working on a MVC application that will allow users to upload documents to the server, work on them, save their work and email it around. That doesn't specifically require the need to read/write programmatically, but for certain application functions I do need this. Any helpful suggestions are appreciated.


reading excel file without saving to disk first

  
Having an issue.  I need to be able to read an excel file from a file upload control but I can not save the file to disk first, it must be done in memory.string excelConnectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="; excelConnectionstring += filePath.Replace("/", "\\"); excelConnectionstring += ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"; OleDbConnection con = new OleDbConnection(excelConnectionstring); OleDbDataAdapter da = new OleDbDataAdapter();Above is my code for reading the data file if it IS saved to disk, but again, I have to be able to do this without saving the file to disk, it must be done in memory.  I have not been able to find any sample code anywhere on how to do this from memory, everything seems to force the file be uploaded, saved to disk, and then read in the connection string, which again I can not do.Any advise would be great, thanks in advance.  I'm really in a bind here.

Reading data from Excel 2007

  
I am attempting to read data from an uploaded spreadsheet using ACE.OLEDB. The code, which is running fine on dev and test machines for XL2003/2007 reports "External table is not in the expected format" error on connecting on the production server for XL2007 only. The code is Dim connectionString As String = "provider=Microsoft.Ace.OLEDB.12.0;" _ & "Data Source='" & ImportData.FullName & "';Extended Properties=Excel 12.0;" LogWebActivity.LogThis("Entering POPSUKD, ConStr=" & connectionString, LogWebActivity.LogDetailLevel.DetailAndData) Dim con As New System.Data.OleDb.OleDbConnection(connectionString) LogWebActivity.LogThis("Dimmed con", LogWebActivity.LogDetailLevel.Debugging) Try Dim cmdSelect As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" & WorksheetName & "$]", con) Dim adapter As New System.Data.OleDb.OleDbDataAdapter(cmdSelect) Dim dS As New Data.DataSet LogWebActivity.LogThis(cmdSelect.CommandText, LogWebActivity.LogDetailLevel.Debugging) con.Open() LogWebActivity.LogThis("Opened Connection", LogWebActivity.LogDetailLevel.Debugging) adapter.Fill(dS, WorksheetName) LogWebActivity.LogThis("Filled DataAdapter", LogWebActivity.LogDetailLevel.Debugging) _SKUS = dS.Tables(W

Reading Excel files from 64-bit ASP.Net app

  
I have an ASP.Net app that is running on a 64-bit server. Part of that app reads data from Excel files and loads that data into our SQL_Server database.I am using the ACE OLE driver to read the Excel files and it works great on my 32-bit development machine. When we deploy the app (from a 64-bit client machine) to our 64-bit server, I get this exception when trying to open the connection:"System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."There are many posts about addressing this issue, so I think these are my options:1. Compile the app for any cpu from a 32-bit machine and deploy it to the server (making the app 32-bit) - not desirable as ideally we would like to run the app in 64-bit mode2. Convert the excel file to csv then use more base .Net libraries to get the data out3. Install Office on the server and use the Microsoft.Office.Interop.Excel library to access the data - not sure if this will work though4. Purchase a conversion library5.  Wait for the 64-bit version of Office and use the new Microsoft.ACE.OLEDB.14.0 driver. - Can I get a Beta version now?I am looking for confirmation that my options are accurate/complete and guidance on which of these (or another option) are the most viable.Thanks, Mike

Reading Excel Spreadsheet from Cell A7

  
I don't know a lot about SSIS.  I have an excel spreadsheet where cell A1 and A2 have varchar values and B1 and B2 have decimal values.  Rows 3, 4, and 5 are blank.  Row 6 has the header column names and the data itself starts in A7 through F7.  I am not interested in rows 1 through 5.  The data ends in row 44.  Row 45, 46, and 47 are blank and in cell D48 it has the comment End of Report.  I am only interested in loading A7 through F44.  I do not know if the row count could grow or if it always stays at line 44.  Is there a way to tell SSIS to skip the first 5 rows, read the 6th row as the header and to start loading from A7?lcerni

SSIS - reading blank rows from Excel 2007 which are actually deleted

  
Folks, We are facing a strange issue with SSIS and thought I would share the same with you and see if there are any solutions. We are developing a solution where users upload their data in the form of Excel 2007 spreadsheets to a web application interface and then, the file is loaded into an Oracle RDBMS using SSIS 2008 (SSIS called using SSIS Object Model from .NET). Now, there are specific templates provided to the users for their input files and they are using the same. However, it is being observed that when the user deletes certain rows from Excel manually and tries to load it, the file load fails. The reason for the same   is seen when the package is run in debug mode – the excel source is reading a few rows (we are assuming the recently deleted rows) as NULL values in all the columns. Now, some searching revealed at http://www.tech-recipes.com/rx/2189/excel_2007_eliminate_blank_rows/ that the following manual set of operations are resolving the problem – 1. Go to the Ribbon, and select the Home tab. 2. In the Editing section, click the small arrow next to Find & Select . 3. Select Go To Special . 4. Select the Blanks radio button and click OK. 5. All blank columns/rows will be highlighted. 6. Delete However, this requires a user intervention and obviously, the users are not really loving it J . I just wanted to check that if there are any po

Reading from Excel file : win form

  
Hi All, I have the following requirement  There are 10 Excel files in a folder.Each excel file contains only 1 column which contains severeal IDs listed. Now, I want to read those IDs in each & every excel file. How do I get the count of number of excel files present in that folder?? After getting the count, how do I read each Id present in that file?? Any help will be greatly apprecited.. Thanks, Alex

Reading AND Writing Access 2 databases with SSIS

  

I went for a job interview this morning, and one of the scenarios that the IT manager (a former programmer) told me about was the following .... If anyone could give me any pointers that I can email him pointers to tomorrow (Wednesday 22nd) it may just help tip his decision in my direction. So there's beer in it if you answer a relevent answer, and I get the job.

Old systems using Access 2 databases. These systems are not due to be updated any time soon.

New systems need access to that data, so each night he exports information using SSIS. However, he needs to also write data back, and more often than just overnight.

However, when he connects to the database, using ODBC connections I think, he says that the app (SSIS) opens the tables / database exclusively, even if he selects the option NOT to open it exclusively.

Any ideas, suggestions or comments ?


Writing to excel file - The field is too small to accept the amount of

  

Hi

I am writing to excel file using jet driver. If the length of value exceeds 255 characters i get the below error.

'The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data'

Please could you provide a solution to how can i add values with length greater than 255. Excel file is 97-2003 format.

System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
 
 System.Data.OleDb.OleDbConnection connection =
          new System.Data.OleDb.OleDbConnection(
              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destinationFilePath + ";Extended Properties=Excel 8.0;");
 
command.CommandText = "Insert into [sheet1$] (col1, col2) values ('1', 'TEXT Message greater than 255 characters')
 

Reading Excel data to SQL database

  

Hi,


Using C# to develop an application.

I need to upload data ( which is in excel) to a sql table.

This is the code I have.



public ReadExcel(string strFileName,string strRegion)
    {
        string strFilePath = string.Concat("c:/BulkUpload/", strFileName);
        myDataSet = new DataSet("Excel_DataSet1");
        string myConnectionString;
        OleDbConnection myConnection;
        OleDbDataAdapter myAdapter;
        myConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source='" + strFilePath + "';Extended Properties=Excel 8.0;";
        myConnection = new OleDbConnection(myConnectionString);

        myAdapter = new OleDbDataAdapter("select * from [sheet1$]", myConnection);
        myAdapter.Fill(myDataSet);
        myConnection.Close();

        DataTable dt = myDataSet.Tables[0];
        int count = 0;
         
        foreach (DataRow r in dt.Rows)
        {
            if (count != 0)
            {                
                DBConnect db = new DBConnect();
                db.AddData("INSERT INTO " + strRegion + "(Country,ProjectOwner,State,County,ProjectName,Quarter,Manager,Comments) VALUES ('" + r[1].ToString() + "','"

Reading AND Writing Access 2 databases with SSIS

  

I went for a job interview this morning, and one of the scenarios that the IT manager (a former programmer) told me about was the following .... If anyone could give me any pointers that I can email him pointers to tomorrow (Wednesday 22nd) it may just help tip his decision in my direction. So there's beer in it if you answer a relevent answer, and I get the job.

Old systems using Access 2 databases. These systems are not due to be updated any time soon.

New systems need access to that data, so each night he exports information using SSIS. However, he needs to also write data back, and more often than just overnight.

However, when he connects to the database, using ODBC connections I think, he says that the app (SSIS) opens the tables / database exclusively, even if he selects the option NOT to open it exclusively.

Any ideas, suggestions or comments ?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend