OpenXML - 如何导出List<DataModel>到Excel -- Part 1

最近这几天研究OpenXML:

这是Open XML的一些介绍：

Open XML

介绍：http://baike.baidu.com/view/1201978.htm

下载：http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0

然后下载一个Simple OOXML， 是OpenXML的一个Extention:http://simpleooxml.codeplex.com/

例子：http://www.cnblogs.com/zc22/p/3453381.html

使用文本模板，生成Word报表：http://www.cnblogs.com/kesalin/archive/2012/04/18/open_xml_word.html

http://lateral8.com/articles/2010/3/5/openxml-sdk-20-export-a-datatable-to-excel.aspx
Open XML操作Excel导入数据 http://www.cnblogs.com/SanMaoSpace/p/3294885.html

 

我的任务是导出后，还能导入回来。Anyway.先做导出：

计划是

1. 先把List<datamodel>转换成DataTable

2. 将DataTable写入Excel, 第一行将是DataTable的Column. 第二行开始，为DataTable的Rows

感觉过程比较绕，但是这样解决方案比较多，先做做看吧。

计划1 经过简单搜索，很快实现了：

        /// <summary>
        /// Converts Generic Collection to DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection props =
                TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            }
            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }     

计划2 似乎也不难

private void ExportDSToExcel(List<DataTable> ds, string destination)
        {
            using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                workbookPart.AddNewPart<WorkbookStylesPart>();
                workbookPart.WorkbookStylesPart.Stylesheet = CreateStylesheet();
                //Stylesheet styleSheet = new Stylesheet();

                uint sheetId = 1;

                foreach (DataTable table in ds)
                {
                    var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List<String> columns = new List<string>();
                    foreach (DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);

                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        if (column.DataType.Name == "Int32")
                        {
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                        }
                        else if (column.DataType.Name == "DateTime")
                        {
                            cell.StyleIndex = 14;
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                        }
                        else
                        {
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        }
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(headerRow);

                    foreach (DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            if (dsrow[col].GetType().Name == "Int32")
                            {
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            }
                            else if (dsrow[col].GetType().Name == "DateTime")
                            {
                                //UInt32Value _dateStyleId = createCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(14));

                                cell.StyleIndex = 14;
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; ;
                                DateTime dtValue = (DateTime)dsrow[col];
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dtValue.ToOADate().ToString()); //
                            }
                            else
                            {
                                
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            }
                            
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }
                }
            }
        }

3. 读取数据到DataTable

4. 将DataTable转换成List<datamodel>

To be continue..