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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Preserve underline, bold, and italics when importing from excel to .net app?

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :ASP.Net

I've got an excel file being saved to do, everything works fine, but one of my cells I need to preserve underline, bold, and italics. I found this http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25022797.html but this is a script for using in Excel. I need something just like it but running on the excel File when they upload it.

Right now I have this code:

Excel Connection:

Protected Function ExcelConnection() As OleDbCommand

		' Connect to the Excel Spreadsheet
		Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
		   "Data Source=" & Server.MapPath("~/temp/ExcelImport.xls") & ";" & _
		   "Extended Properties=Excel 8.0;"

		' create your excel connection object using the connection string
		Dim objXConn As New OleDbConnection(xConnStr)

		' use a SQL Select command to retrieve the data from the Excel Spreadsheet
		' the "table name" is the name of the worksheet within the spreadsheet
		Dim objCommand As New OleDbCommand("SELECT * FROM [Accomplishments$]", objXConn)
		Return objCommand

	End Function 

View Complete Post

More Related Resource Links

While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




Importing Excel 2007 spreadsheet into WSS 3.0 -- Error Message



I'm trying to import (Custom Lists >> Import Spreadsheet) into WSS 3.0 and I'm getting the following message: 

Refers to the _layouts

You are not authorized to view this page.  You might not have permissions to view this direcotyr or page using the credentials you supplied. [More stuff here.]

Http ERror 403 - Forbidden

Is this just a permissions problem or is there some other underlying issue?  Should you be able to upload an Excel spreadsheet (with links) into a Custom List?


Thanks! Patti N.

Importing Excel with multiple worksheets via MVC

Is there a way wherein I could import data from multiple sheets of an excel file?  Thanks in advance...

Trouble importing strange column format report from Excel 2007 to SQL 08

Hi, I'm not sure whether this belongs in this section or the SSIS one so hopefully I've got it right!  Hoping someone will be able to help with a problem I'm having importing a report with header and detail rows from our antiquated POS system into SQL 2008 tables.  The reports export in Excel 2007 format and for each header row there can be one or more detail rows starting from column B.  As such I've tried several things such as openrowset, an Access 12.0 OLE DB source in SSIS and even COM automation of Excel to try and write a conditional split which will hold the header details in variables then write them to rows alongside the detail rows.  I've pasted a small sample of the data here as I wasn't able to attach it: 01/07/2010 @ 11:18 Page: 1 RECEIVING: Voucher Journal Sort: VC|Str|Vou Date|Vou #|Document SID Filter: Voucher Date: 01/06/2010@12:00a..30/06/2010@11:59p Include Item detail: DCS|Item#|Desc1|Attr|Material|Size|Qty VC Str Vou Date Vou # Qty ABC 001 15/06/2010 12345 38 E NN 66 200148 XXXXXXXX RED SHAD E NN 66 200149 YYYYYYYYY BLACK GO E PP 60 200154 ZZZZZZZZZ BLACK CDE 002 16/06/2010 13839 8 F SA 35 217500 XXXXXXXX Natural F SA FL 218674 YYYYYYYYY Chalk F SA WE 221462 ZZZZZZZZZ White FGH 001 21/06/2010 13905 3 F SH 85 126260 XXXXXXXX Navy IJK 001 23/06/2010 13914 3 E AA 61 250005 YYYYYY

Importing Data from Excel into SQL Server using SSIS: some datetime values appear as NULLS How to Re

I created a Package in the Business Intelligence studio to Import data from Excel file  into SQL Server 2005 using a Excel Source and a OLE DB Destination that uses a data convertion transformation before it reaches the destination a mjority of the data is copied over. However i am having 2 Issues. 1. In the Date field some of the values appear as Null in SQL Server 2. I need to change the format of the date in Excel from dd/mm/yyyy to mm/dd/yyyy before inserting into SQL Server if Possible. I am not sure of the solution for Ques 1 but i attempted using a script task for #2 It did not work. Please Advice what the best way to proceed Thanks.

Importing Excel 2007 data into SQL 2005 database

I have a SQL 2005 cluster installation without SSIS and without management studio (I didn't install it). Now we need to import data from an excel 2007 file into a specific table in a database. I have tried doing this from a remote management studio, BI development studio and DTSwizard but I can't seem to make it work. The error message from BIDS is that "the data source is on a remote computer..." Does any of you have a step by step  guide that works from a remote location. BR Rasmus    

importing excel file into sql server 2005 in asp .net using c#

Dear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me? rgds,RK

excel desination heading to be in bold


Hi Team,

I have an issue excel destination heading using a script and execute sql task.

I am creating the excel dynamically but i want the heading to be in bold and the data to be normal. do i need to set some property to achieve this.



Importing Data from Excel to SQL Server2008


Hi Experts,

 I have a table with 100 records. I need to import extra 50 records in the same table. So from 101 to 150 records should insert the data which is present in excel Simply I want to import 50 records from Excel to Table without disturbing the first 100 records.

Please suggest me

Problem with List Columns in Foundation 2010 when Importing from Excel


This is a pretty detailed issue. I will give you all the steps I am doing and hopefully there is a solution.

I recently updated our intranet site from WSS 3.0 to SharePoint Foundation. In WSS 3.0 I added a few of the application templates to use, including Asset Tracking. When I moved to Foundation I decided not to use those (and not sure if I can anyway) and exported the Asset Tracking data to Excel, then imported it to Sharepoint Foundation as a list.

Here is the issue I am having: when I imported from Excel all but one of the columns was created as "multiple lines of text". This means I cannot sort by these columns. I thought I could create similar columns as single lines of text, export back to Excel, copy the data, and import back. This changes the columns again to multiple lines. I even tried just typing in the data as a test and same problem. As long as there is no data, the columns will remain as single line of text; but as soon as I put data in I get this issue.

I can cofirm that these columns do not have more text than should be available in a single line. Any ideas of what I can do to make this work? I am at my wits end on this.

Thanks for any and all help.

Importing from Excel - Cannot get SQL to Recognize Column Formats


I am trying create a table in SQL using only 5 columns of data from a large spreadsheet.  I am using the import wizard in SQL and attempting to write a query to only pull in those 5 columns.  I keep getting an error related to file type conversions (see below) for each column I'm trying to bring in.  Any ideas or suggestions?  I tried formatting my excel sheet to be 'text' and to be 'general' but it didn't help.  I tried VARCHAR and TEXT as my destination types without success.  Thanks!!

Source Column       Source Type    Destination Column       Destination Type

DE_H                            12            DE_H                            VARCHAR

Importing Excel 2003 and 2007 formats on a 64 bit platform


The biggest challenge I’ve had in a long time is trying to import Excel spreadsheets into a staging table. Ridiculous you say?   I would agree. The ultimate production box is 64 bit Windows 2003 server with Sql Server 2008 R2 installed.  I need the package to accept either 2003 or 2007 Excel formats.  The sheets to import all have identical column names but the columns contain disparate data. Meaning sometimes a specific column has text and sometimes it has numerical data. I have zero control over that particular aspect.  I don’t want the driver to take it’s best guess as to the data format, I just want to stage the data into a Sql Server table with  nvarchar(255) columns.

 Yes I have the Microsoft.ACE.OLEDB.12.0 driver loaded on the server and yes I have tried adding the IMEX=1 to the connection string.

  I have tried running the import via a stored procedure and for the m

Importing Data to SharePoint Wiki from a Word or Excel doc.



Is it possible to import data directly from a Microsoft Word or Excel or .csv or .txt file directly to a SharePoint Wiki?

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Excel Automation in .Net

Excel Automation is a buzz word in both webapps and winapps. In the programming life (like Mr.Anderson neo) I guess almost every one could have come across this word. For others let me go further in detail. Excel Automation is automating some or all of the process involved in creating or updating excel worsheets.

display data into GridView from excel sheet

I have to display data from excel sheet into my gridview and also I need to figure out a way to store data into excel files from my web page. I am using C# 3.5, VS 2008.

Export SharePoint List to Excel Spreadsheet Programmatically using C#

In SharePoint applications, Custom Lists are used to store business data and Document Libraries to store the documents. But for data manupulation and analysis, Microsoft Excel provides very rich features as compared to SharePoint Lists. That's why people still loves to work on Microsoft Excel Sheets.
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