服务器上没有安装excel的情况下导出excel文件的处理 c#下

早就想写这篇文章，今天终于有了点时间。。。分享一下。

首先那要把DocumentFormat.OpenXml这个dll文件导入到你的工程里。  

 DocumentFormat.OpenXml是什么文件，它在哪有？别着急。。。。

写这篇文章时http://msdn.microsoft.com/en-us/office/bb265236.aspx这个路径可以找到。

打开以上链接之后下载Open XML SDK 2.0 for Microsoft Office这个就是了。

下面这个代码是我五天的成果，从不知道什么是Open XML到导出文件成功。。。五天，是不是太久了。。。人比较笨，没办法了

/// <summary>
        /// 「Excel」ファイルを作成
        /// </summary>
        /// <param name="ht">出力データ</param>
        /// <returns>ファイル名</returns>
        private string CreateExcel(SysDataTable dt)
        {
            // 変数を定義
            string fileName = CommonUtils.GetTempFileName();
            string filePath = "这是文件存放路径！！"; //把这改了啊
            object miss = Missing.Value;
          
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();

                WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();               
                stylesPart.Stylesheet = new Stylesheet();

                Sheets sheets = new Sheets();
                string relId = workbookPart.GetIdOfPart(worksheetPart);
                //シート名を設定
                string sheetName = SpsConstants.PROP_SEARCH_OUTPUT_SHEET_NAME + DateTime.Now.ToString("yyyyMM");
                Sheet sheet = new Sheet { Name = sheetName, SheetId = 1U, Id = relId };
                sheets.Append(sheet);
                workbookPart.Workbook.Append(sheets);

                Fonts fonts = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };
                //fontId = 0
                Font font = new Font();
                DocumentFormat.OpenXml.Spreadsheet.FontSize fontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 10D };
                FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering() { Val = 1 };
                FontScheme fontScheme = new FontScheme() { Val = FontSchemeValues.Minor };
                font.Append(fontSize);
                font.Append(fontFamilyNumbering);
                font.Append(fontScheme);
                fonts.Append(font);
                //fontId = 1
                font = new Font();
                Bold bold = new Bold();
                fontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 10D };
                fontFamilyNumbering = new FontFamilyNumbering() { Val = 1 };
                fontScheme = new FontScheme() { Val = FontSchemeValues.Minor };
                font.Append(bold);
                font.Append(fontSize);
                font.Append(fontFamilyNumbering);
                font.Append(fontScheme);
                fonts.Append(font);
                stylesPart.Stylesheet.Append(fonts);

                Fills fills = new Fills() { Count = (UInt32Value)3U };
                //FillId = 0
                Fill fill = new Fill();
                PatternFill patternFill = new PatternFill() { PatternType = PatternValues.None };
                fill.Append(patternFill);
                fills.Append(fill);
                //FillId = 1
                fill = new Fill();
                patternFill = new PatternFill() { PatternType = PatternValues.Gray125 };
                fill.Append(patternFill);
                fills.Append(fill);
                //FillId = 2
                fill = new Fill();
                patternFill = new PatternFill() { PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor = new ForegroundColor() { Rgb = "FFFCD5B4" };
                BackgroundColor backgroundColor = new BackgroundColor() { Indexed = (UInt32Value)64U };
                patternFill.Append(foregroundColor);
                patternFill.Append(backgroundColor);
                fill.Append(patternFill);
                fills.Append(fill);
                stylesPart.Stylesheet.Append(fills);

                Borders borders = new Borders() { Count = (UInt32Value)2U };
                //BorderId = 0
                Border border = new Border();
                LeftBorder leftBorder = new LeftBorder();
                RightBorder rightBorder = new RightBorder();
                TopBorder topBorder = new TopBorder();
                BottomBorder bottomBorder = new BottomBorder();
                DiagonalBorder diagonalBorder = new DiagonalBorder();
                border.Append(leftBorder);
                border.Append(rightBorder);
                border.Append(topBorder);
                border.Append(bottomBorder);
                border.Append(diagonalBorder);
                borders.Append(border);
                //BorderId = 1
                border = new Border();
                leftBorder = new LeftBorder() { Style = BorderStyleValues.Thin };
                rightBorder = new RightBorder() { Style = BorderStyleValues.Thin };
                topBorder = new TopBorder() { Style = BorderStyleValues.Thin };
                bottomBorder = new BottomBorder() { Style = BorderStyleValues.Thin };
                Color color = new Color() { Indexed = (UInt32Value)64U };
                leftBorder.Append(color);
                color = new Color() { Indexed = (UInt32Value)64U };
                rightBorder.Append(color);
                color = new Color() { Indexed = (UInt32Value)64U };
                topBorder.Append(color);
                color = new Color() { Indexed = (UInt32Value)64U };
                bottomBorder.Append(color);
                diagonalBorder = new DiagonalBorder();
                border.Append(leftBorder);
                border.Append(rightBorder);
                border.Append(topBorder);
                border.Append(bottomBorder);
                border.Append(diagonalBorder);
                borders.Append(border);
                stylesPart.Stylesheet.Append(borders);
               
                //StyleIndex = 0;
                CellFormats cellFormats = new CellFormats() { Count = (UInt32Value)3U };
                CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
                Alignment alignment = new Alignment() { Vertical = VerticalAlignmentValues.Center };
                cellFormat.Append(alignment);
                cellFormats.Append(cellFormat);
                //StyleIndex = 1;
                cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
                alignment = new Alignment() { Vertical = VerticalAlignmentValues.Center };
                cellFormat.Append(alignment);
                cellFormats.Append(cellFormat);
                //StyleIndex = 2;
                cellFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
                alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center };
                cellFormat.Append(alignment);
                cellFormats.Append(cellFormat);

                stylesPart.Stylesheet.Append(cellFormats);

                stylesPart.Stylesheet.Save();

                SheetData sheetData = new SheetData();             

                //タイトルを設定
                Columns columns = new Columns();
                columns = SetExcelTitle();
                worksheetPart.Worksheet.Append(columns);
                string[] titles = SpsConstants.PROP_OUTPUT_EXCEL_TITLE.Split(',');
                Row row = new Row();
                Cell cell = null;
                CellValue cellValue = null;
                for (int index = 0; index < titles.Length; index++)
                {
                    cell = new Cell() { StyleIndex = 2U, DataType = CellValues.String };
                    cellValue = new CellValue();
                    cellValue.Text = titles[index].ToString();
                    cell.Append(cellValue);
                    row.Append(cell);
                }
                sheetData.Append(row);               
               
                //出力データを設定
                string[] cells = SpsConstants.PROP_OUTPUT_EXCEL_CELL.Split(',');
                for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
                {
                    DataRow datarow = dt.Rows[rowIndex];
                    row = new Row();
                    for (int celIndex = 0; celIndex < dt.Columns.Count; celIndex++)
                    {
                        cell = new Cell() { StyleIndex = 1U, DataType = CellValues.String };
                        cellValue = new CellValue();
                        // セルを取得
                        if (dt.Columns.Contains(cells[celIndex]) && datarow[cells[celIndex]] != null && !string.IsNullOrEmpty(datarow[cells[celIndex]].ToString()))
                        {
                            cellValue.Text = SetDataFormat(datarow[cells[celIndex]].ToString(), celIndex);
                        }
                        cell.Append(cellValue);                       
                        row.Append(cell);
                    }
                    sheetData.Append(row);
                }
                worksheetPart.Worksheet.Append(sheetData);

                worksheetPart.Worksheet.Save();               
                document.WorkbookPart.Workbook.Save();
                document.Close();
            }
            return fileName;
        }

        /// <summary>
        /// シートのタイトルを設定
        /// </summary>
        /// <param name="sheet"></param>
        private Columns SetExcelTitle()
        {
            string[] titles = SpsConstants.PROP_OUTPUT_EXCEL_TITLE.Split(',');
            double[] widths = { 11.00, 47.13, 28.38, 15.25, 17.13, 14.25, 34.25, 28.75,
                              13.75, 11.00, 7.88, 16.63, 7.88, 33.88, 35.25, 20.00, 15.88,
                              20.13, 9.88, 34.63, 45.88, 34.50, 17.25, 10.75};
            Columns columns = new Columns();
            Column column = null;
            for (UInt32Value index = 1; index <= titles.Length; index++)
            {
                column = new Column() { Min = (UInt32Value)index, Max = (UInt32Value)index, Width = widths[index - 1], CustomWidth = true };
                // スタイルを設定
                columns.Append(column);
            }
            return columns;
        }