服务器上没有安装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;
}
以上代码，看不懂的请留言，今天时间有限我就不一一解释没个语句是什么意思了。
以后会好好整理一下。
