感觉OpenXmlSdk的语法真的不是很友好。研究了半天，只实现了简单的导出功能。对于单元格样式的设置暂时还是搞明白，网上的资料真的很少，官方文档是英文的。中文的文章大都是用工具（Open XML SDK 2.0 Productivity Tool）搞出来的，反正在我这是不管用。最终还是回到了NPOI 的怀抱。
最后还是把这点代码记录一下，以后有时间再继续研究吧。
using System;
using System.Data;
using System.IO;
using System.Web;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet; public static class ExportHelper
{
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="fileName"></param>
/// <param name="dataSet">DataSet中每个DataTable生成一个Sheet</param>
public static void ExportExcel(string fileName, DataSet dataSet)
{
if (dataSet.Tables.Count == )
{
return;
} using (MemoryStream stream = DataTable2ExcelStream(dataSet))
{
FileStream fs = new FileStream(fileName, FileMode.CreateNew);
stream.WriteTo(fs);
fs.Flush();
fs.Close();
}
} public static void ExportExcel(string fileName, DataTable dataTable)
{
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable);
ExportExcel(fileName, dataSet);
} /// <summary>
/// Web导出Excel文件
/// </summary>
/// <param name="fileName"></param>
/// <param name="dataSet">DataSet中每个DataTable生成一个Sheet</param>
public static void ResponseExcel(string fileName, DataSet dataSet)
{
if (dataSet.Tables.Count == )
{
return;
} using (MemoryStream stream = DataTable2ExcelStream(dataSet))
{
ExportExcel(fileName, stream);
}
} public static void ResponseExcel(string fileName, DataTable dataTable)
{
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataTable.Copy());
ResponseExcel(fileName, dataSet);
} private static void ExportExcel(string fileName, MemoryStream stream)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename= " + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
} private static MemoryStream DataTable2ExcelStream(DataSet dataSet)
{
MemoryStream stream = new MemoryStream();
SpreadsheetDocument document = SpreadsheetDocument.Create(stream,
SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook(); Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets()); for (int i = ; i < dataSet.Tables.Count; i++)
{
DataTable dataTable = dataSet.Tables[i];
WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheet sheet = new Sheet
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = (UInt32)(i + ),
Name = dataTable.TableName
};
sheets.Append(sheet); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>(); Row headerRow = CreateHeaderRow(dataTable.Columns);
sheetData.Append(headerRow); for (int j = ; j < dataTable.Rows.Count; j++)
{
sheetData.Append(CreateRow(dataTable.Rows[j], j + ));
}
} document.Close(); return stream;
} private static Row CreateHeaderRow(DataColumnCollection columns)
{
Row header = new Row();
for (int i = ; i < columns.Count; i++)
{
Cell cell = CreateCell(i + , , columns[i].ColumnName, CellValues.String);
header.Append(cell);
}
return header;
} private static Row CreateRow(DataRow dataRow, int rowIndex)
{
Row row = new Row();
for (int i = ; i < dataRow.Table.Columns.Count; i++)
{
Cell cell = CreateCell(i + , rowIndex, dataRow[i], GetType(dataRow.Table.Columns[i].DataType));
row.Append(cell);
}
return row;
} private static CellValues GetType(Type type)
{
if (type == typeof(decimal))
{
return CellValues.Number;
}
//if ((type == typeof(DateTime)))
//{
// return CellValues.Date;
//}
return CellValues.SharedString;
} private static Cell CreateCell(int columnIndex, int rowIndex, object cellValue, CellValues cellValues)
{
Cell cell = new Cell
{
CellReference = GetCellReference(columnIndex) + rowIndex,
CellValue = new CellValue { Text = cellValue.ToString() },
DataType = new EnumValue<CellValues>(cellValues),
StyleIndex =
};
return cell;
} private static string GetCellReference(int colIndex)
{
int dividend = colIndex;
string columnName = String.Empty; while (dividend > )
{
int modifier = (dividend - ) % ;
columnName = Convert.ToChar( + modifier) + columnName;
dividend = (dividend - modifier) / ;
} return columnName;
}
}