Exemple #1
0
        public ExcelWriter()
        {
            _stream = SpreadsheetReader.Create();
            _doc    = SpreadsheetDocument.Open(_stream, true);
            var worksheetPart = SpreadsheetReader.GetWorksheetPartByName(_doc, "Sheet1");

            _writer = new WorksheetWriter(_doc, worksheetPart);
        }
        /// <summary>
        /// 将查询出的DataSet导出为Excel。
        /// </summary>
        /// <param name="dataSet">要导出的DataSet数据集,可以包含多个DataTable数据,使用DataSetName作为导出的文件名</param>
        public static void ExportDataSet(DataSet dataSet)
        {
            if (dataSet == null)
            {
                throw new ArgumentException("dataSet");
            }
            if (dataSet.Tables.Count == 0)
            {
                return;
            }

            MemoryStream        stream = SpreadsheetReader.Create();
            SpreadsheetDocument doc    = SpreadsheetDocument.Open(stream, true);
            //首先清空原有的Sheet
            var allSheet = doc.WorkbookPart.Workbook.Descendants <Sheet>();

            if (allSheet.Count() > 0)
            {
                doc.WorkbookPart.Workbook.Sheets.RemoveAllChildren();
            }
            //构造Excel
            foreach (DataTable dt in dataSet.Tables)
            {
                WorksheetPart   sheetPart   = SpreadsheetWriter.InsertWorksheet(doc, dt.TableName);
                WorksheetWriter sheetWriter = new WorksheetWriter(doc, sheetPart);
                //打印列名
                int colIndex = 0;//当前第几列,从0开始
                foreach (DataColumn col in dt.Columns)
                {
                    string reference = GetExcelColumnHeader(colIndex) + "1";
                    sheetWriter.PasteText(reference, col.ColumnName);
                    colIndex++;
                }
                sheetWriter.PasteDataTable(dt, "A2");
                SpreadsheetWriter.Save(doc);
            }
            //Write to response stream
            System.Web.HttpContext context = System.Web.HttpContext.Current;
            context.Response.Clear();
            string UserAgent = context.Request.ServerVariables["http_user_agent"].ToLower();
            string FileName  = dataSet.DataSetName;

            if (UserAgent.IndexOf("firefox") == -1)
            {//非火狐浏览器
                FileName = context.Server.UrlEncode(FileName);
            }
            context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", FileName + ".xlsx"));
            context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            stream.WriteTo(context.Response.OutputStream);
            context.Response.End();
        }
        /// <summary>
        /// Create the spreadsheet.
        /// </summary>
        /// <param name="documentName">Excel file name.</param>
        /// <param name="excelWorkSheetName">Excel worksheet name: default: sheet1.</param>
        /// <param name="rowData">Row data to write.</param>
        /// <param name="headerData">Header data.</param>
        /// <param name="rowPointers">Row pointers.</param>
        /// <param name="styleSheet">Style sheet.</param>
        /// <returns>Memory stream.</returns>
        private static MemoryStream CreateSpreadSheet(string documentName, string excelWorkSheetName, IEnumerable <T> rowData, string[] headerData, string[] rowPointers, Stylesheet styleSheet)
        {
            int rowNum   = 0;
            int colNum   = 0;
            int maxWidth = 0;
            int minCol   = 1;
            int maxCol   = rowPointers == null ? minCol : rowPointers.Length;

            maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length;

            MemoryStream        xmlStream   = SpreadsheetReader.Create();
            SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true);

            SetSheetName(excelWorkSheetName, spreadSheet);

            if (styleSheet == null)
            {
                SetStyleSheet(spreadSheet);
            }
            else
            {
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
            }

            WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(spreadSheet, excelWorkSheetName);

            WriteHeaders(headerData, out rowNum, out colNum, out maxWidth, spreadSheet, worksheetPart);
            AddCellWidthStyles(Convert.ToUInt32(minCol), Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart);

            if (rowPointers == null || rowPointers.Length == 0)
            {
                WriteRowsFromHeaders(rowData, headerData, rowNum, out maxWidth, spreadSheet, worksheetPart);
            }
            else
            {
                WriteRowsFromKeys(rowData, rowPointers, rowNum, out maxWidth, spreadSheet, worksheetPart);
            }

            // Save to the memory stream
            SpreadsheetWriter.Save(spreadSheet);
            spreadSheet.Close();
            spreadSheet.Dispose();
            return(xmlStream);
        }