If you encounter a following error, while exporting to Excel 2007 (.xlsx)
Errors were detected in file MyFileName.xlsx.
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part
then, probably, you are exporting string values in wrong format. The right structure in this case is
<sheetData>
<row>
<c>
<v>99999</v>
</c>
<c t="inlineStr">
<is>
<t>My Text</t>
</is>
</c>
</row>
</sheetData>Note, that there is no <v> tag in <c> as it should be with numbers. Instead you have to make an inline string (<is>) tag and put text (<t>) tag with needed text in it. Do not forget to add the t="inlineStr" attribute to <c> tag.
More info can be found developer:network.
Hello...
ReplyDeleteThank you for your post, please consider the following:
I tried your solution and it works, but when I save the Excel file, Excel removes the inlineStr attribute and replace it with "s" and the structure goes back to depend on sharedstring.xml file.
do you have a work around for this.
Thanks and Best regards
Seems to me that Excel transforms all inline strings to shared on opening the file, so you can not do anything here. May be this will help you with retrieving the sharedstring value.
ReplyDeleteI think you can read it as shared string, change and save back as inline already.
Hey... thanks for your help.
ReplyDeleteI have already done that, I'm using STAX API for parsing the xml files of Excel(using JAVA), so that is what makes it hard for me to handle both cases, for shareString and for inlineStr, any way... inlineStr helps me in updating the file, which is great, althought I still need to read from shared string file.
Thanks and Best Regards.
Juri:
ReplyDeleteI also got the following error using the logic below though I am having trouble reconciling your explanation to my code.
Any pointers would be appreciated.
Sub SpreadsheetPush()
'Make a copy of the template file File.Copy("C:\Users\Cona\Documents\Visual Studio 2010\WebSites\OpenXML\Template.xlsx", "c:\Users\Cona\Documents\Visual Studio 2010\WebSites\OpenXML\generated.xlsx", True)
'Open up the copied template workbook
Using myWorkbook As SpreadsheetDocument = SpreadsheetDocument.Open("c:\Users\Cona\Documents\Visual Studio 2010\WebSites\OpenXML\generated.xlsx", True)
'Access the main Workbook part, which contains all references
Dim workbookPart As WorkbookPart = myWorkbook.WorkbookPart
'Grab the first worksheet
Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.First()
'SheetData will contain all the data
Dim sheetData As SheetData = worksheetPart.Worksheet.GetFirstChild(Of SheetData)()
'Connect to database named Customers
Dim db As New LinqtoCustomersDataContext()
'My data starts at row 2
Dim index As Integer = 2
'Select * from Customers table
Dim CustomerQuery =
From cust In db.Customers()
'Select cust
'For each row in my database add a row to my spreadsheeet
For Each Item In CustomerQuery
Dim CustomerID As String = Item.CustomerID
Dim CompanyName As String = Item.CompanyName
Dim ContactName As String = Item.ContactName
'Add a new row
Dim contentRow As Row = CreateContentRow(index, CustomerID, CompanyName, ContactName)
index += 1
'Append new row to sheet data
sheetData.AppendChild(contentRow)
Next
worksheetPart.Worksheet.Save()
End Using
End Sub
Private headerColumns As String() = New String() {"A", "B", "C"}
Private Function CreateContentRow(index As Integer, CustomerID As String, CompanyName As String, ContactName As String) As Row
'Create new row
Dim r As New Row()
r.RowIndex = CType(Convert.ToInt32(index), UInt32)
'First cell is a text cell, so create it and append it
Dim firstCell As Cell = CreateTextCell(headerColumns(0), CustomerID, index)
r.AppendChild(firstCell)
'Create cells that contain data
For i As Integer = 1 To headerColumns.Length - 1
Dim c As New Cell()
c.CellReference = headerColumns(i) & index
Dim v As New CellValue()
If i = 1 Then
v.Text = CompanyName.ToString()
Else
v.Text = ContactName.ToString()
End If
c.AppendChild(v)
r.AppendChild(c)
Next
Return r
End Function
Private Function CreateTextCell(header As String, text As String, index As Integer) As Cell
'Create new inline string cell
Dim c As New Cell()
c.DataType = CellValues.InlineString
c.CellReference = header & index
'Add text to text cell
Dim inlineString As New InlineString()
Dim t As New Text()
t.Text = text
inlineString.AppendChild(t)
c.AppendChild(inlineString)
Return c
End Function
End Sub
End Class
Hello,
ReplyDeleteAre you sure you have the same xml on the output, when inserting values to datasheet? I mean, the inline string tag is "<is>" and c-tag has the attribute?
Superb, I was using ExcelPackage to generate the excel but the hint on data types and attributes solved my issue.
ReplyDeleteSanjay