使用Open XML SDK建立xlsx檔案卻出現內容必須修正的原因：AutoSave
問題情境：
使用Open XML SDK 2.5的SpreadsheetDocument.Create()建立xlsx檔案之後，企圖使用Excel 2013開啟，卻出現錯誤訊息。
問題描述：
使用Excel 2013開啟的錯誤訊息如下圖：
點選Yes進行修復之後的錯誤訊息如下圖：
依據前述的錯誤訊息，檢視位於使用者資料夾之下的AppData\Local\Temp\的Log檔，內容如下：
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error081200_01.xml</logFileName>
<summary>Errors were detected in file 'C:\Users\Kyle\Documents\Test\Test.xlsx'</summary>
<removedParts summary="Following is a list of removed parts:">
<removedPart>Replaced Part: /xl/worksheets/sheet.xml part with XML error. A document must contain exactly one root element. Line 1, column 0.</removedPart>
</removedParts>
</recoveryLog>
使用Open XML SDK 2.5 Productivity Tool開啟xlsx檔案的錯誤訊息如下圖：
將Test.xlsx更名為Test.xlsx.zip之後再解壓縮，發現sheet.xml檔案內容完全空白。
產生問題的原因：
下列程式碼的第三個參數將AutoSave設為false，所以系統不會自動為程式設計師產生的WorkSheet呼叫Save。
解決問題的方法：
方法一：
程式設計師必須一一為每一個產生的WorkSheet呼叫Save，否則將會出現前述錯誤。
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
worksheetPart.Worksheet.Save();方法二：
不要變更AutoSave的預設值。(自Open XML SDK 2.0開始支援AutoSave的預設值)[1]
補充說明：
1、buffer是在記憶體中的資料，可以不必透過OS直接存取以提高存取速度。
(A buffer is a block of bytes in memory used to cache data, thereby reducing the number of calls to the operating system. Buffers improve read and write performance. A buffer can be used for either reading or writing, but never both simultaneously.)[2]
2、stream是一組byte[]的資料。
(A stream is an abstraction of a sequence of bytes, such as a file, an input/output device, an inter-process communication pipe, or a TCP/IP socket.)[3]
3、flush method將buffer的資料清出至目的地。
(Override Flush on streams that implement a buffer. Use this method to move any information from an underlying buffer to its destination, clear the buffer, or both.)[4]
4、WorkSheet的Save方法將buffer的資料寫入WorkSheetPart。
(Saves the data in the DOM tree back to the part. It could be called multiple times as well. Each time it is called, the stream will be flushed.)[5]
範例程式碼[6]：
{
CreateSpreadsheetWorkbook(@"C:\Users\Kyle\My Documents\Test\Test.xlsx");
}
public static void CreateSpreadsheetWorkbook(string filepath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
//SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook, false);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
worksheetPart.Worksheet.Save();
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
資料來源：
[1]Improvements Made to the Final Open XML SDK 2.0 compared to the December 2009 CTP
http://blogs.msdn.com/b/brian_jones/archive/2010/05/03/improvements-made-to-the-final-open-xml-sdk-2-0-compared-to-the-december-2009-ctp.aspx
[2]BufferedStream Class
https://msdn.microsoft.com/en-us/library/system.io.bufferedstream(v=vs.110).aspx
[3]Stream Class
https://msdn.microsoft.com/en-us/library/system.io.stream(v=vs.110).aspx
[4]Stream.Flush Method
https://msdn.microsoft.com/en-us/library/system.io.stream.flush(v=vs.110).aspx
[5]Worksheet.Save Method
https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.worksheet.save(v=office.14).aspx
[6]Structure of a SpreadsheetML document (Open XML SDK)
https://msdn.microsoft.com/en-us/library/office/gg278316.aspx
