import/export excel content ?

Question

Can I use the OLEDB/Ace provider to import/export Excel content ?

The OLEDB jet engine is supposed to be part of .NET and I am not sure about the ACE engine(that can handle Office 2007).

Another potential issue I see is that both export the file to be on local storage like c:\some_directory\myexcel.xls

While Azure does have LocalStorage which I assume resemble file system, I am not sure if it is compatible to this level.

Answer 1

 

Unfortunately currently you can't use the OleDb/ACE data provider in Azure. OleDb permission is not granted in Azure's partial trust environment. VSTO is not an option either because it requires interop permissions. But the Open XML Format SDK should not have any problems to work in Azure. So if you're only working with Excel 2007, that is your choice. Alternatively, you can directly parse the zip package and the xml documents in it. You can either use local storage (for temp storage) or blob (for permanent storage). I don't know much about Open XML Format SDK. But I just wrote a quick sample of parsing a simple xlsx document.

Upload the file and store it in the local storage:

protected void ButtonUpload_Click(object sender, EventArgs e)

{

ILocalResource locRes = RoleManager.GetLocalResource("tempStorage");

if (FileUpload1.HasFile)

{

String fileName = FileUpload1.FileName;

String savePath = locRes.RootPath + "\\" + fileName;

FileUpload1.SaveAs(savePath);

}

}

Parse the xlsl document:

ILocalResource locRes = RoleManager.GetLocalResource("tempStorage");

using (Package package = Package.Open(locRes.RootPath + "\\Book1.xlsx"))

{

PackagePart part = package.GetPart(new Uri("/xl/worksheets/sheet1.xml", UriKind.Relative));

Stream s = part.GetStream();

XDocument doc = XDocument.Load(XmlReader.Create(s));

XNamespace ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";

foreach (XElement row in doc.Root.Element(ns + "sheetData").Elements(ns + "row"))

{

foreach (XElement column in row.Elements(ns + "c"))

{

string data = (string)column.Element(ns + "v");

//Do anything you like.

}

}

}

Note this sample only works for non-string type data. String type data is stored in /xl/sharedStrings.xml, and is referenced in /xl/worksheets/yoursheet.xml. So you need to do more work to parse the document...

---

Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Hi Yi-Lun Luo ,

In your above post,

ILocalResource locRes = RoleManager .GetLocalResource( "tempStorage" );

Can you elaborate what exactly "tempStorage" term? is this a storage account name? that we are using to store table, blob and queue.

Thanks
Hemang