C# Excel question

[Guest]

Hi

Does anyone know of a way (via code behind) to pull a single sheet out of a
Excel workbook and convert it to a stand alone html document?

Thanks

Brian

 

[Mark Rae]

Does anyone know of a way (via code behind) to pull a single sheet out of
a
Excel workbook and convert it to a stand alone html document?

1) Use ADO.NET
http://www.google.co.uk/search?sour...rls=GGLG,GGLG:2006-28,GGLG:en&q=ADO.NET+Excel

2) Use a 3rd-party tool:
http://www.aspose.com/Products/Aspose.Cells/Default.aspx

3) If the workbook is saved in the (relatively) new XML file format, use the
standard methods in the Xml namespace:
http://www.microsoft.com/downloads/...52-3547-420a-a412-00a2662442d9&displaylang=en
http://www.microsoft.com/downloads/...80-f2c0-4b80-9ad1-2cb0c300aef9&displaylang=en

4) If this is a WinForms app and you know that Excel will be installed, use
InterOp
http://www.google.co.uk/search?sour...rls=GGLG,GGLG:2006-28,GGLG:en&q=Excel+Interop

However, if this is a WebForms app, server-side Office Automation is not
recommended, and not actually supported by Microsoft:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2

 

[Guest]

Hi Mark

Thanks for the links but I am still having an issue. The code does convert
the document to html, but it does all the sheets. I am trying to pull just
one sheet, save it to a new location so it can be viewed as a stand along
html doc.

Excel.Application excelApp = new Excel.ApplicationClass();
Excel.Workbook theWorkbook = excelApp.Workbooks._Open(excelFile, false,
false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Sheets sheets = theWorkbook.Worksheets;
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
String outputFile =
@"C:\SourceSafe\ConvertExcelSheetsToHTML\ReportFiles.html";
Microsoft.Office.Interop.Excel.Worksheet wsCurrent =
(Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(52);
wsCurrent.SaveAs(outputFile, format, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);

As you can see I am requesting sheet # 52 but it does all the sheets.

Thanks

 

[Mark Rae]

The code does convert
the document to html, but it does all the sheets. I am trying to pull
just
one sheet, save it to a new location so it can be viewed as a stand along
html doc.

Excel.Application excelApp = new Excel.ApplicationClass();
Excel.Workbook theWorkbook = excelApp.Workbooks._Open(excelFile, false,
false, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Sheets sheets = theWorkbook.Worksheets;
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
String outputFile =
@"C:\SourceSafe\ConvertExcelSheetsToHTML\ReportFiles.html";
Microsoft.Office.Interop.Excel.Worksheet wsCurrent =
(Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(52);
wsCurrent.SaveAs(outputFile, format, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);

As you can see I am requesting sheet # 52 but it does all the sheets.

Hmm - I'm not exactly certain that you are...

It looks to me as if you are selecting sheet #52, but then calling the
SaveAs method - I'm pretty sure (but don't quote me on that) that the SaveAs
method applies to the entire workbook, not the currently selected worksheet,
even though you are invoking it from the Worksheet object...

Two thoughts spring to mind:

1) Delete all the other sheets, then invoke SaveAs...

2) Export the current sheet instead of invoking SaveAs...

Also, you may find that you get a better response by posting your message
in: microsoft.pulic.vsnet.vstools.office - that's the newsgroup
*specifically* for this type of question...

 

[Guest]

OK, I did it.
// Save Excel Docmnet as HTML
string excelFile = "@"C:\excelDocument.xls";
Excel.Application excelApp = new Excel.ApplicationClass();
Excel.Workbook excelWorkbook =
excelApp.Workbooks._Open(excelFile, false, false, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
Excel.Sheets excelSheet = excelWorkbook.Worksheets;
object sheetFormat =
Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
String outputFile = @"C:\excelDocument.xls";
Microsoft.Office.Interop.Excel.Worksheet excelCurrentSheet =
(Microsoft.Office.Interop.Excel.Worksheet)excelSheet.get_Item(Convert.ToInt32(SheetNametextBox.Text));
excelCurrentSheet.SaveAs(outputFile, sheetFormat, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
excelSheet = null;
excelCurrentSheet = null;
excelWorkbook = null;
excelApp.ActiveWorkbook.Close(false, excelFile, false);
if (excelApp != null)
{
excelApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
excelApp = null;
//////////////////////////// Read and collect html markup value from sheet
you need
WebRequest webRequest =
WebRequest.Create(@"C:\Inetpub\wwwroot\WebSites\sheet057.htm");
StreamReader streamRequest = new
StreamReader(webRequest.GetResponse().GetResponseStream());
System.Text.StringBuilder stringBuilderRequest = new
System.Text.StringBuilder();
string stringLine = "";
while ((stringLine = streamRequest.ReadLine()) != null)
{
if (stringLine.Length > 0)
stringBuilderRequest.Append(stringLine);
}
streamRequest.Close();
pageValue = stringBuilderRequest.ToString();
CreateASCX();

////////////////////////////////// Create new page
private void CreateASCX()
{
// strip out reference to home page and tab page
int findStart = pageValue.IndexOf("</style>");
findStart = findStart + 8;
int findEnd = pageValue.IndexOf("</script>");
findEnd = findEnd - findStart;
pageValue = pageValue.Remove(findStart, findEnd);
StreamWriter SW;
SW = File.CreateText(@"C:\Inetpub\wwwroot\WebSites\newPage.ascx");
SW.WriteLine(pageValue.ToString());
SW.Close();
Application.Exit();
}
Brian