Excel Chart Export as Picture file VB.NET

The following program shows how can Export a Chart from Excel to Picture file like BMP,JPG, GIF etc. Before we create a Chart , we have to fill data in Excel sheet.

Continue reading “Excel Chart Export as Picture file VB.NET”

Advertisements

Read entire worksheet in an Excel workbook using VB.net

In the following section you can see How to find the last row data in Excel worksheet or How to find the Used area in a worksheet. For finding last row we are using WorkSheet.UsedRange . It will return the last cell . The follwoing code open an Excel file and read the entire content.

Continue reading “Read entire worksheet in an Excel workbook using VB.net”

Read Data from Excel using OLEDB in VB.NET

Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB in VB.NET . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For read the content from Excel file using ado.net , We can use the SELECT command like in SQL Operations.

If you would like to export to excel, check out this article:

How to export from DataGridView to excel

Continue reading “Read Data from Excel using OLEDB in VB.NET”

Retrieving Excel data using EPPlus with and without headers

If you ever had to read from a sheet inside C# using EPPlus, you can use the following snippet to read the data.

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

 

using OfficeOpenXml;
....
string sourceFilePath = hidFilename.Value;
            using (ExcelPackage package = new ExcelPackage(new FileInfo(sourceFilePath)))
            {
                ExcelWorksheet ws = package.Workbook.Worksheets[ddlSheets.SelectedItem.Text];
                DataTable tbl = new DataTable();
                var hasHeader = chkHasHeaders.Checked;
                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    string strColumnName = hasHeader
                        ? firstRowCell.Text
                        : string.Format("Column {0}", firstRowCell.Start.Column);
                    int counts = 0;
                    string newColumn = strColumnName;
                    while (tbl.Columns.Contains(newColumn))
                    {
                        newColumn = strColumnName + counts.ToString();
                        counts++;
                    }
                    tbl.Columns.Add(newColumn);
                }
                var startRow = hasHeader ? 2 : 1;
                for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    var row = tbl.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                    tbl.Rows.Add(row);
                }
                grdTable.DataSource = tbl;
                grdTable.DataBind();
            }

"The file you are trying to open, '[filename]', is in a different format" Excel Error

On a recent project, the client asked me to allow some reports to be exported to Excel (XLS.) I’ve had such a request 1000 times over the years and quickly went to work rendering the grid as HTML, sending its HTML as a response and setting the Response.Header to the Excel MIME type… Simple.

Or so I thought. Turns out that Office 2007 doesn’t like that much. When you open such a spreadsheet in Excel 2007 you get an error like:

“The file you are trying to open, ‘[filename]’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”
(Yes | No | Help)

After searching for hours, I finally ran into this document from MS that essentially says this is a “feature” of the new Excel and no matter how much everyone hates it, they won’t fix it. Here’s an excerpt from that document:

“The current design does not allow you to open HTML content from a web site in Excel… So ASP pages that return HTML and set the MIME type to something like XLS to try to force the HTML to open in Excel instead of the web browser (as expected) will always get the security alert… If you use an HTML MIME type, then the web browser will open the content instead of Excel. So there is no good workaround for this case because of the lack of a special MIME type for HTML/MHTML that is Excel specific. You can add your own MIME type if you control both the web server and the client desktops that need access to it, but otherwise the best option is to use a different file format or alert your users of the warning and tell them to select Yes to the dialog.” [Emphasis added]

In other words, give up because there’s no good solution. Here’s some bad solutions you could try, though:

  • Display a message that says something like, “If you are using Office 2007, please select “Yes” from the resulting dialog.”
  • Include a registry script that the user can optionally run to change their HKCUSoftwareMicrosoftOffice12.0ExcelSecurityExtensionHardening DWord to 0, disabling this useless prompt
  • Only export to CSV, not Excel. Sure, it’s not as pretty but it’ll work.
  • Instead of using this much simpler spreadsheet generation method, instead open a template spreadsheet on the server as a data-source, write to it (using SQL), and save it with a unique file name for the user to download. Of course you’ll then have to do stuff like ensuring your saved files have unique file names (perhaps using GUID) and deleting the old spreadsheets from the File System.

How to insert data into an Excel File using VB.NET

excel-logo

Declaring the connection

If you are using Excel 2007 and above, create the file where you would like to save your data and add the following to your web.config

<appSettings>
<add value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:File.xlsx;Extended Properties='Excel 12.0;ReadOnly=False;HDR=YES;'" key="ExcelLink" />
</appSettings>

Read from an Excel file

First, we initialize the connection to the Excel File:

'connection details
Dim _connectionString As String = System.Configuration.ConfigurationManager.AppSettings("ExcelLink")
'get last value of the id in excel file
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
Dim strErrorsFound As New StringBuilder
Dim blnErrorFound As Boolean = False
Using connection As DbConnection = factory.CreateConnection()
connection.ConnectionString = _connectionString

Then, we create the command that will select information from the sheet:

Using command As DbCommand = connection.CreateCommand()
'Get the RefNo for the latest entry
command.CommandText = "SELECT TOP 1 ID FROM [Feedback$] ORDER BY ID DESC"

We will put our code in a Try-Catch block to prevent any errors bleeding into the Client-Side.

Try
connection.Open()

Using dr As DbDataReader = command.ExecuteReader()
If dr.HasRows Then

While dr.Read()
‘store last ref_number value
lastID = If(dr(“ID”).ToString() <> “”, Int32.Parse(dr(“ID”).ToString()), 0)
End While
End If
End Using

‘clean up- close first connection to the excel file
connection.Dispose()
connection.Close()

Catch ex As Exception
PanelServerError.Visible = True
Exit Sub
End Try

Don’t forget to close the Using Tags

End Using
End Using

Get data from SQL Server and save it into Excel

Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("CN").ConnectionString)

Dim myCommand As New SqlCommand("[Your SP]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Try

myConnection.Open()

Dim myReader As SqlDataReader = myCommand.ExecuteReader()

While myReader.Read()

IDNum = myReader.Item("ID")
contactName = myReader.Item("ContactName")
contactCompany = myReader.Item("ContactCompany")
contactPosition = myReader.Item("ContactPosition")
contactEmail = myReader.Item("ContactEmail")
project = myReader.Item("Project")

'insert into excel file
Dim factory2 As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")

Dim connection2 As DbConnection = factory2.CreateConnection()
connection2.ConnectionString = _connectionString

Dim command2 As DbCommand = connection2.CreateCommand()

'Build SQL string
Dim SQLQuery As New StringBuilder()
SQLQuery.Append("INSERT INTO [Feedback$] ")
SQLQuery.Append("(ID, Name, Company, Position_, Email, Project, ")

SQLQuery.Append("VALUES ('" & IDNum & "', '" & contactName & "','" & contactCompany & "','" & contactPosition & "','" & contactEmail & "','" & project & "','")

Try
connection2.Open()

'insert data into excel spreadsheet
command2.CommandText = SQLQuery.ToString()
command2.ExecuteNonQuery()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Line / ID " + IDNum)
strErrorsFound.AppendLine(ex.Message)
strErrorsFound.AppendLine("Query: " + SQLQuery.ToString())
strErrorsFound.AppendLine("******************************************************************************")
Finally
connection2.Dispose()
connection2.Close()
End Try

'****************excel insert code *******************

End While

myReader.Close()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Generic error: " + ex.Message)
strErrorsFound.AppendLine("******************************************************************************")

Finally

'clean up
myConnection.Close()
myConnection.Dispose()
End Try

Errors

Operation must use an updateable query.

Resolution

Granting access to the file being written to (IISUSR User or the IIS App Pool User) solved this issue for me.

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

Resolution

While importing from an Excel spread sheet you see the following error message “[Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data”. This is problem with the Excel ODBC driver which can often be worked around by reordering the records in the spreadsheet..
Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon what it has read. Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….
A column in an Excel spreadsheet has a column in it which contains string data. The first 8 records contain short strings, (let’s say 20-30 characters). The Excel ODBC driver reads this data and assumes that a short string data type will be appropriate for this column. If a subsequent record contains a longer string, (let’s say 300 characters). The data type may prove inappropriate and unable to store the longer string and the error above is raised by the Excel ODBC driver. Moving the record with the long string to the beginning of the dataset will allow the Excel ODBC to select a more appropriate data type for the column which will apply to all records in the spreadsheet.
A column in a spreadsheet has numeric strings for the first 8 records, For example “123”, “456” etc. Excel decides that this column has numeric data. A subsequent record contains a string which is not numeric data, for example “Hello World”. The assumption made by the Excel ODBC driver will prove incorrect and the above error message will be raised by the ODBC driver. The problem can be worked around by reconsidering the order of the records. If the record containing “hello world” is placed within the first 8 records. The Excel ODBC driver will determine that this column contains string data and hopefully a string data type will be selected which will be appropriate for all data records.
In all cases the technique is to arrange the order of the records such that the Excel ODBC driver is allowed to make the correct selection of data type.