.NET导入导出Excel方法总结
最近，应项目的需求，需要实现Excel的导入导出功能，对于Web架构的Excel导入导出功能，比较传统的实现方式是：
1）导入Excel：将Excel文件上传到服务器的某一文件夹下，然后在服务端完成Excel的读取及数据的存储；
2）导出Excel：在服务端生成需要导出的Excel，然后下载到客户端。
其中，文件的上传和下载本文不在详述，以下主要写一些DataTable或DataSet与Excel之间的相互转换。
转换方式多种多样，网上也有很多前辈分享的代码实现，本文也借鉴了前辈的诸多思路及代码，具体方法如下：
1. DCOM方式
使用DCOM方式实现Excel的导入导出功能，首先需要在服务端安装office软件。
具体实现代码如下：
1）数据导出到Excel
public class ExportExcel : IDisposable { private Excel.ApplicationClass excelApp; private Excel.Workbook workBook; private Excel.Worksheet workSheet; private Excel.Range range; public void DataTableToExcel(DataTable sourceTable, string fileName) { excelApp = new Excel.ApplicationClass(); if (excelApp == null) { throw new Exception("打开Excel程序错误！"); } workBook = excelApp.Workbooks.Add(true); workSheet = (Excel.Worksheet)workBook.Worksheets[1]; int rowIndex = 0; //写入列名 ++rowIndex; for (int i = 0; i < sourceTable.Columns.Count; i++) { workSheet.Cells[rowIndex, i + 1] = sourceTable.Columns[i].ColumnName; } range = workSheet.get_Range(workSheet.Cells[rowIndex, 1], workSheet.Cells[rowIndex, sourceTable.Columns.Count]); FontStyle headerStyle = new FontStyle { FontSize = 30, BordersValue = 1, FontBold = true, EntireColumnAutoFit = true }; FontStyleHelper.SetFontStyleForRange(range, headerStyle); //写入数据 ++rowIndex; for (int r = 0; r < sourceTable.Rows.Count; r++) { for (int i = 0; i < sourceTable.Columns.Count; i++) { workSheet.Cells[rowIndex, i + 1] = ExportHelper.ConvertToCellData(sourceTable, r, i); } rowIndex++; } range = workSheet.get_Range(workSheet.Cells[2, 1], workSheet.Cells[sourceTable.Rows.Count + 1, sourceTable.Columns.Count]); FontStyle bodyStyle = new FontStyle { FontSize = 16, BordersValue = 1, FontAlign = Infrastruction.FontAlign.Right, EntireColumnAutoFit = true }; FontStyleHelper.SetFontStyleForRange(range, bodyStyle); //只保存一个sheet页 //workSheet.SaveAs(fileName, Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, // Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); //保存整个Excel workBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); workBook.Close(false, Type.Missing, Type.Missing); excelApp.Quit(); Dispose(); } public void Dispose() { GC.Collect(); BaseExcel.Dispose(excelApp, workSheet, workBook, range); } }
2）数据导入到内存
public class ImportExcel : IDisposable { private Excel.ApplicationClass excelApp; private Excel.Workbook workBook; private Excel.Worksheet workSheet; private Excel.Range range; public DataSet ExcelToDataSet(string fileName) { if (!File.Exists(fileName)) { return null; } FileInfo file = new FileInfo(fileName); string strConnection = string.Empty; string extension = file.Extension; string vsSql = string.Empty; switch (extension) { case ".xls": strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; break; case ".xlsx": strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; break; default: strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; break; } DataSet ds = ImportHelper.GetDataSetFromExcel(strConnection); Dispose(); ds = ImportHelper.ConvertDataSet(ds); return ds; } public DataSet ExcelToDataSetByDcom(string fileName) { DataSet result = null; excelApp = new Excel.ApplicationClass(); if (excelApp == null) { throw new Exception("打开Excel程序错误！"); } excelApp.Visible = false; excelApp.UserControl = true; // 以只读的形式打开EXCEL文件 workBook = excelApp.Application.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); int sheets = workBook.Worksheets.Count; if (sheets >= 1) { result = new DataSet(); } for(int i = 1; i <= sheets; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); string sheetName = workSheet.Name; DataTable dt = new DataTable(); dt.TableName = sheetName; //取得总记录行数 int rows = workSheet.UsedRange.Cells.Rows.Count; //得到行数 int columns = workSheet.UsedRange.Cells.Columns.Count;//得到列数 if (rows == 0 || columns == 0) return null; //取得数据范围区域 range = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[rows, columns]); object[,] arryItem = (object[,])range.Value2; //get range's value //生成DataTable的列 for(int col = 1; col <= columns; col++) { string dcName = arryItem[1, col].ToString().Trim(); DataColumn dc = new DataColumn(dcName, typeof(string)); dt.Columns.Add(dc); } //将数据填充到DataTable for(int row = 2; row <= rows; row++) { object[] rowvalue = new object[columns]; for (int col = 1; col <= columns; col++) { rowvalue[col - 1] = arryItem[row, col]; } dt.Rows.Add(rowvalue); } //将DataTable填充到DataSet result.Tables.Add(dt); } //清理非托管对象 workBook.Close(false, Type.Missing, Type.Missing); excelApp.Quit(); Dispose(); return result; } public void Dispose() { GC.Collect(); BaseExcel.Dispose(excelApp, workSheet, workBook, range); } }
3）其他辅助类
public class BaseExcel { /// <summary> /// 释放Excel资源 /// </summary> /// <param name="excelApp"></param> public static void Dispose(Excel.ApplicationClass excelApp, Excel.Worksheet workSheet, Excel.Workbook workBook, Excel.Range range) { //清理非托管的代码 if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); workSheet = null; } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; } if (range != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range); range = null; } KillProcess(); } /// <summary> /// 关闭进程 /// </summary> /// <param name="hwnd"></param> /// <param name="ID"></param> /// <returns></returns> [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); private static void Kill(Excel.Application excel) { int id = 0; IntPtr intptr = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 System.Diagnostics.Process p = null; try { GetWindowThreadProcessId(intptr, out id); //得到本进程唯一标志 p = System.Diagnostics.Process.GetProcessById(id); //得到对进程k的引用 if (p != null) { p.Kill(); //关闭进程k p.Dispose(); } } catch { } } //强制结束进程 private static void KillProcess() { System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses(); foreach (System.Diagnostics.Process thisprocess in allProcess) { string processName = thisprocess.ProcessName; if (processName.ToLower() == "excel") { try { thisprocess.Kill(); } catch { } } } } }
public class FontStyle { /// <summary> /// 字体大小 /// </summary> public int FontSize { get; set; } /// <summary> /// 字体名称 /// </summary> public string FontName { get; set; } /// <summary> /// 是否为粗体 /// </summary> public bool FontBold { get; set; } /// <summary> /// 字体对齐方式 /// </summary> public FontAlign FontAlign { get; set; } /// <summary> /// 边框样式 /// </summary> public int BordersValue { get; set; } /// <summary> /// 字体颜色索引 /// </summary> public int FontColorIndex { get; set; } /// <summary> /// 背景颜色索引 /// </summary> public int InteriorColorIndex { get; set; } /// <summary> /// 列宽自适应 /// </summary> public bool EntireColumnAutoFit { get; set; } } public enum FontAlign { Center, Right, Left }
public class FontStyleHelper { /// <summary> /// 对选中区域设置格式 /// </summary> /// <param name="range">选中区域</param> /// <param name="fontStyle">样式表</param> public static void SetFontStyleForRange(Excel.Range range, FontStyle fontStyle) { if (fontStyle.FontSize != 0) { range.Font.Size = fontStyle.FontSize; } if (fontStyle.FontName != null) { range.Font.Name = fontStyle.FontName; } if (fontStyle.FontBold != false) { range.Font.Bold = fontStyle.FontBold; } if (fontStyle.FontAlign == FontAlign.Center) { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } else if (fontStyle.FontAlign == FontAlign.Left) { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } else if (fontStyle.FontAlign == FontAlign.Right) { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; } if (fontStyle.BordersValue != 0) { range.Borders.Value = fontStyle.BordersValue; } if (fontStyle.FontColorIndex != 0) { range.Font.ColorIndex = fontStyle.FontColorIndex; } if (fontStyle.InteriorColorIndex != 0) { range.Interior.ColorIndex = fontStyle.InteriorColorIndex; } if (fontStyle.EntireColumnAutoFit == true) { range.EntireColumn.AutoFit(); } } }
public class ExportHelper { public static string ConvertToCellData(DataTable sourceTable, int rowIndex, int colIndex) { DataColumn col = sourceTable.Columns[colIndex]; object data = sourceTable.Rows[rowIndex][colIndex]; if (col.DataType == System.Type.GetType("System.DateTime")) { if (data.ToString().Trim() != "") { return Convert.ToDateTime(data).ToString("yyyy-MM-dd HH:mm:ss"); } else { return (Convert.ToDateTime(DateTime.Now)).ToString("yyyy-MM-dd HH:mm:ss"); } } else if (col.DataType == System.Type.GetType("System.String")) { return "'" + data.ToString().Trim(); } else { return data.ToString().Trim(); } } }
public class ImportHelper { /// <summary> /// 通过OleDb获得DataSet /// </summary> /// <param name="connStr"></param> /// <param name="sheetNames"></param> /// <returns></returns> public static DataSet GetDataSetFromExcel(string connStr) { DataSet ds = null; using (OleDbConnection conn = new OleDbConnection(connStr)) { try { conn.Open(); DataTable tblName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (tblName.Rows.Count < 1 || tblName == null) { conn.Close(); return null; } else { ds = new DataSet(); DataTable tbl = null; for (int i = 0; i < tblName.Rows.Count; i++) { tbl = new DataTable(); tbl.TableName = tblName.Rows[i]["TABLE_NAME"].ToString().Replace("$", ""); string vsSql = "SELECT * FROM [" + tblName.Rows[i]["TABLE_NAME"].ToString() + "]"; OleDbDataAdapter myCommand = new OleDbDataAdapter(vsSql, conn); myCommand.Fill(tbl); ds.Tables.Add(tbl.Copy()); tbl.Dispose(); tbl = null; } conn.Close(); } } catch (Exception ex) { conn.Close(); throw new Exception(ex.Source + ":" + ex.Message); } } return ds; } public static DataSet ConvertDataSet(DataSet source) { if (source == null) return null; DataSet result = new DataSet(); int dataTableCount = source.Tables.Count; DataTable temp = null; for (int i = 0; i < dataTableCount; i++) { temp = ConvertDataTable(source.Tables[i]); result.Tables.Add(temp); result.Tables[i].TableName = source.Tables[i].TableName; } return result; } private static DataTable ConvertDataTable(DataTable source) { DataTable result = new DataTable(); int columnsCount = source.Columns.Count; int rowsCount = source.Rows.Count; for (int i = 0; i < columnsCount; i++) { DataColumn column = new DataColumn(source.Rows[0][i].ToString().Trim()); result.Columns.Add(column); } DataRow dr; for (int r = 1; r < rowsCount; r++) { dr = result.NewRow(); for (int c = 0; c < columnsCount; c++) { dr[c] = source.Rows[r][c].ToString().Trim(); } result.Rows.Add(dr); } return result; } }
2. Open XML方式
该方法只针对office2007及以上版本有效，因为office2007以上版本是基于XML实现的数据存储，详细内容不做讲解，有兴趣的网友可以将Excel文件扩展名修改为zip并解压，然后对解压出的文件进行分析。
以Open XML的方式实现Excel的导入导出，需要先下载并安装Open XML Format SDK 2.0及其以上版本，具体下载地址为：https://www.microsoft.com/en-us/download/details.aspx?id=5124。SDK默认会安装在C:\Program Files (x86)\Open XML Format SDK\V2.0 (64bit)目录下，lib子目录下的DocumentFormat.OpenXml.dll必须被引用到项目中。
具体代码如下：
public class ExcelOperater { #region 读取Excel /// <summary> /// 将Excel数据读取到DataSet /// </summary> /// <param name="filePath"></param> /// <returns></returns> public DataSet ExcelToDataSet(string filePath) { DataSet dataSet = new DataSet(); try { using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false)) { //指定WorkbookPart对象 WorkbookPart workBookPart = spreadDocument.WorkbookPart; //获取Excel中SheetName集合 List<string> sheetNames = GetSheetNames(workBookPart); foreach (string sheetName in sheetNames) { DataTable dataTable = WorkSheetToTable(workBookPart, sheetName); if (dataTable != null) { dataSet.Tables.Add(dataTable);//将表添加到数据集 } } } } catch (Exception exp) { //throw new Exception("可能Excel正在打开中,请关闭重新操作！"); } return dataSet; } /// <summary> /// 将Excel数据读取到DataTable /// </summary> /// <param name="sheetName"></param> /// <param name="filePath"></param> /// <returns></returns> public DataTable ExcelToDataTable(string sheetName, string filePath) { DataTable dataTable = new DataTable(); try { //根据Excel流转换为spreadDocument对象 using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))//Excel文档包 { //Workbook workBook = spreadDocument.WorkbookPart.Workbook;//主文档部件的根元素 //Sheets sheeets = workBook.Sheets;//块级结构（如工作表、文件版本等）的容器 WorkbookPart workBookPart = spreadDocument.WorkbookPart; //获取Excel中SheetName集合 List<string> sheetNames = GetSheetNames(workBookPart); if (sheetNames.Contains(sheetName)) { //根据WorkSheet转化为Table dataTable = WorkSheetToTable(workBookPart, sheetName); } } } catch (Exception exp) { //throw new Exception("可能Excel正在打开中,请关闭重新操作！"); } return dataTable; } /// <summary> /// 获取Excel中的sheet页名称 /// </summary> /// <param name="workBookPart"></param> /// <returns></returns> private List<string> GetSheetNames(WorkbookPart workBookPart) { List<string> sheetNames = new List<string>(); Sheets sheets = workBookPart.Workbook.Sheets; foreach (Sheet sheet in sheets) { string sheetName = sheet.Name; if (!string.IsNullOrEmpty(sheetName)) { sheetNames.Add(sheetName); } } return sheetNames; } /// <summary> /// 获取指定sheet名称的Excel数据行集合 /// </summary> /// <param name="workBookPart"></param> /// <param name="sheetName"></param> /// <returns></returns> public IEnumerable<Row> GetWorkBookPartRows(WorkbookPart workBookPart, string sheetName) { IEnumerable<Row> sheetRows = null; //根据表名在WorkbookPart中获取Sheet集合 IEnumerable<Sheet> sheets = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName); if (sheets.Count() == 0) { return null;//没有数据 } WorksheetPart workSheetPart = workBookPart.GetPartById(sheets.First().Id) as WorksheetPart; //获取Excel中得到的行 sheetRows = workSheetPart.Worksheet.Descendants<Row>(); return sheetRows; } /// <summary> /// 将指定sheet名称的数据转换成DataTable /// </summary> /// <param name="workBookPart"></param> /// <param name="sheetName"></param> /// <returns></returns> private DataTable WorkSheetToTable(WorkbookPart workBookPart, string sheetName) { //创建Table DataTable dataTable = new DataTable(sheetName); //根据WorkbookPart和sheetName获取该Sheet下所有行数据 IEnumerable<Row> sheetRows = GetWorkBookPartRows(workBookPart, sheetName); if (sheetRows == null || sheetRows.Count() <= 0) { return null; } //将数据导入DataTable,假定第一行为列名,第二行以后为数据 foreach (Row row in sheetRows) { //获取Excel中的列头 if (row.RowIndex == 1) { List<DataColumn> listCols = GetDataColumn(row, workBookPart); dataTable.Columns.AddRange(listCols.ToArray()); } else { //Excel第二行同时为DataTable的第一行数据 DataRow dataRow = GetDataRow(row, dataTable, workBookPart); if (dataRow != null) { dataTable.Rows.Add(dataRow); } } } return dataTable; } /// <summary> /// 获取数字类型格式集合 /// </summary> /// <param name="workBookPart"></param> /// <returns></returns> private List<string> GetNumberFormatsStyle(WorkbookPart workBookPart) { List<string> dicStyle = new List<string>(); Stylesheet styleSheet = workBookPart.WorkbookStylesPart.Stylesheet; var test = styleSheet.NumberingFormats; if (test == null) return null; OpenXmlElementList list = styleSheet.NumberingFormats.ChildElements;//获取NumberingFormats样式集合 foreach (var element in list)//格式化节点 { if (element.HasAttributes) { using (OpenXmlReader reader = OpenXmlReader.Create(element)) { if (reader.Read()) { if (reader.Attributes.Count > 0) { string numFmtId = reader.Attributes[0].Value;//格式化ID string formatCode = reader.Attributes[1].Value;//格式化Code dicStyle.Add(formatCode);//将格式化Code写入List集合 } } } } } return dicStyle; } /// <summary> /// 获得DataColumn /// </summary> /// <param name="row"></param> /// <param name="workBookPart"></param> /// <returns></returns> private List<DataColumn> GetDataColumn(Row row, WorkbookPart workBookPart) { List<DataColumn> listCols = new List<DataColumn>(); foreach (Cell cell in row) { string cellValue = GetCellValue(cell, workBookPart); DataColumn col = new DataColumn(cellValue); listCols.Add(col); } return listCols; } /// <summary> /// 将sheet页中的一行数据转换成DataRow /// </summary> /// <param name="row"></param> /// <param name="dateTable"></param> /// <param name="workBookPart"></param> /// <returns></returns> private DataRow GetDataRow(Row row, DataTable dateTable, WorkbookPart workBookPart) { //读取Excel中数据,一一读取单元格,若整行为空则忽视该行 DataRow dataRow = dateTable.NewRow(); IEnumerable<Cell> cells = row.Elements<Cell>(); int cellIndex = 0;//单元格索引 int nullCellCount = cellIndex;//空行索引 foreach (Cell cell in row) { string cellVlue = GetCellValue(cell, workBookPart); if (string.IsNullOrEmpty(cellVlue)) { nullCellCount++; } dataRow[cellIndex] = cellVlue; cellIndex++; } if (nullCellCount == cellIndex)//剔除空行 { dataRow = null;//一行中单元格索引和空行索引一样 } return dataRow; } /// <summary> /// 获得单元格数据值 /// </summary> /// <param name="cell"></param> /// <param name="workBookPart"></param> /// <returns></returns> public string GetCellValue(Cell cell, WorkbookPart workBookPart) { string cellValue = string.Empty; if (cell.ChildElements.Count == 0)//Cell节点下没有子节点 { return cellValue; } string cellRefId = cell.CellReference.InnerText;//获取引用相对位置 string cellInnerText = cell.CellValue.InnerText;//获取Cell的InnerText cellValue = cellInnerText;//指定默认值(其实用来处理Excel中的数字) //获取WorkbookPart中NumberingFormats样式集合 //List<string> dicStyles = GetNumberFormatsStyle(workBookPart); //获取WorkbookPart中共享String数据 SharedStringTable sharedTable = workBookPart.SharedStringTablePart.SharedStringTable; try { EnumValue<CellValues> cellType = cell.DataType;//获取Cell数据类型 if (cellType != null)//Excel对象数据 { switch (cellType.Value) { case CellValues.SharedString://字符串 //获取该Cell的所在的索引 int cellIndex = int.Parse(cellInnerText); cellValue = sharedTable.ChildElements[cellIndex].InnerText; break; case CellValues.Boolean://布尔 cellValue = (cellInnerText == "1") ? "TRUE" : "FALSE"; break; case CellValues.Date://日期 cellValue = Convert.ToDateTime(cellInnerText).ToString(); break; case CellValues.Number://数字 cellValue = Convert.ToDecimal(cellInnerText).ToString(); break; default: cellValue = cellInnerText; break; } } else//格式化数据 { #region 根据Excel单元格格式设置数据类型，该部分代码有误，暂未处理 /* if (dicStyles.Count > 0 && cell.StyleIndex != null)//对于数字,cell.StyleIndex==null { int styleIndex = Convert.ToInt32(cell.StyleIndex.Value); string cellStyle = dicStyles[styleIndex - 1];//获取该索引的样式 if (cellStyle.Contains("yyyy") || cellStyle.Contains("h") || cellStyle.Contains("dd") || cellStyle.Contains("ss")) { //如果为日期或时间进行格式处理,去掉“;@” cellStyle = cellStyle.Replace(";@", ""); while (cellStyle.Contains("[") && cellStyle.Contains("]")) { int otherStart = cellStyle.IndexOf('['); int otherEnd = cellStyle.IndexOf("]"); cellStyle = cellStyle.Remove(otherStart, otherEnd - otherStart + 1); } double doubleDateTime = double.Parse(cellInnerText); DateTime dateTime = DateTime.FromOADate(doubleDateTime);//将Double日期数字转为日期格式 if (cellStyle.Contains("m")) { cellStyle = cellStyle.Replace("m", "M"); } if (cellStyle.Contains("AM/PM")) { cellStyle = cellStyle.Replace("AM/PM", ""); } cellValue = dateTime.ToString(cellStyle);//不知道为什么Excel 2007中格式日期为yyyy/m/d } else//其他的货币、数值 { cellStyle = cellStyle.Substring(cellStyle.LastIndexOf('.') - 1).Replace("\\", ""); decimal decimalNum = decimal.Parse(cellInnerText); cellValue = decimal.Parse(decimalNum.ToString(cellStyle)).ToString(); } } */ #endregion } } catch { //string expMessage = string.Format("Excel中{0}位置数据有误,请确认填写正确！", cellRefId); //throw new Exception(expMessage); cellValue = "N/A"; } return cellValue; } /// <summary> /// 获得sheet页集合 /// </summary> /// <param name="filePath"></param> /// <returns></returns> private List<string> GetExcelSheetNames(string filePath) { string sheetName = string.Empty; List<string> sheetNames = new List<string>();//所有Sheet表名 using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false)) { WorkbookPart workBook = spreadDocument.WorkbookPart; Stream stream = workBook.GetStream(FileMode.Open); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load(stream); XmlNamespaceManager xmlNSManager = new XmlNamespaceManager(xmlDocument.NameTable); xmlNSManager.AddNamespace("default", xmlDocument.DocumentElement.NamespaceURI); XmlNodeList nodeList = xmlDocument.SelectNodes("//default:sheets/default:sheet", xmlNSManager); foreach (XmlNode node in nodeList) { sheetName = node.Attributes["name"].Value; sheetNames.Add(sheetName); } } return sheetNames; } #region SaveCell private void InsertTextCellValue(Worksheet worksheet, string column, uint row, string value) { Cell cell = ReturnCell(worksheet, column, row); CellValue v = new CellValue(); v.Text = value; cell.AppendChild(v); cell.DataType = new EnumValue<CellValues>(CellValues.String); worksheet.Save(); } private void InsertNumberCellValue(Worksheet worksheet, string column, uint row, string value) { Cell cell = ReturnCell(worksheet, column, row); CellValue v = new CellValue(); v.Text = value; cell.AppendChild(v); cell.DataType = new EnumValue<CellValues>(CellValues.Number); worksheet.Save(); } private static Cell ReturnCell(Worksheet worksheet, string columnName, uint row) { Row targetRow = ReturnRow(worksheet, row); if (targetRow == null) return null; return targetRow.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + row, true) == 0).First(); } private static Row ReturnRow(Worksheet worksheet, uint row) { return worksheet.GetFirstChild<SheetData>(). Elements<Row>().Where(r => r.RowIndex == row).First(); } #endregion #endregion #region 写入Excel /// <summary> /// 在指定路径创建SpreadsheetDocument文档 /// </summary> /// <param name="filePath"></param> /// <returns></returns> private SpreadsheetDocument CreateParts(string filePath) { SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); return document; } /// <summary> /// 创建WorksheetPart /// </summary> /// <param name="workbookPart"></param> /// <param name="sheetName"></param> /// <returns></returns> private WorksheetPart CreateWorksheet(WorkbookPart workbookPart, string sheetName) { WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); if (sheets == null) sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets()); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); uint sheetId = 1; if (sheets.Elements<Sheet>().Count() > 0) {//确定sheet的唯一编号 sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1; } if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet" + sheetId; } Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart; } /// <summary> /// 创建sheet样式 /// </summary> /// <param name="workbookPart"></param> /// <returns></returns> private Stylesheet CreateStylesheet(WorkbookPart workbookPart) { Stylesheet stylesheet = null; if (workbookPart.WorkbookStylesPart != null) { stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; if (stylesheet != null) { return stylesheet; } } workbookPart.AddNewPart<WorkbookStylesPart>("Style"); workbookPart.WorkbookStylesPart.Stylesheet = new Stylesheet(); stylesheet = workbookPart.WorkbookStylesPart.Stylesheet; stylesheet.Fonts = new Fonts() { Count = (UInt32Value)3U }; //fontId =0,默认样式 Font fontDefault = new Font( new FontSize() { Val = 11D }, new FontName() { Val = "Calibri" }, new FontFamily() { Val = 2 }, new FontScheme() { Val = FontSchemeValues.Minor }); stylesheet.Fonts.Append(fontDefault); //fontId =1，标题样式 Font fontTitle = new Font(new FontSize() { Val = 15D }, new Bold() { Val = true }, new FontName() { Val = "Calibri" }, new FontFamily() { Val = 2 }, new FontScheme() { Val = FontSchemeValues.Minor }); stylesheet.Fonts.Append(fontTitle); //fontId =2，列头样式 Font fontHeader = new Font(new FontSize() { Val = 13D }, new Bold() { Val = true }, new FontName() { Val = "Calibri" }, new FontFamily() { Val = 2 }, new FontScheme() { Val = FontSchemeValues.Minor }); stylesheet.Fonts.Append(fontHeader); //fillId,0总是None，1总是gray125，自定义的从fillid =2开始 stylesheet.Fills = new Fills() { Count = (UInt32Value)3U }; //fillid=0 Fill fillDefault = new Fill(new PatternFill() { PatternType = PatternValues.None }); stylesheet.Fills.Append(fillDefault); //fillid=1 Fill fillGray = new Fill(); PatternFill patternFillGray = new PatternFill() { PatternType = PatternValues.Gray125 }; fillGray.Append(patternFillGray); stylesheet.Fills.Append(fillGray); //fillid=2 Fill fillYellow = new Fill(); PatternFill patternFillYellow = new PatternFill(new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } }) { PatternType = PatternValues.Solid }; fillYellow.Append(patternFillYellow); stylesheet.Fills.Append(fillYellow); stylesheet.Borders = new Borders() { Count = (UInt32Value)2U }; //borderID=0 Border borderDefault = new Border(new LeftBorder(), new RightBorder(), new TopBorder() { }, new BottomBorder(), new DiagonalBorder()); stylesheet.Borders.Append(borderDefault); //borderID=1 Border borderContent = new Border( new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin }, new DiagonalBorder() ); stylesheet.Borders.Append(borderContent); stylesheet.CellFormats = new CellFormats(); stylesheet.CellFormats.Count = 3; //styleIndex =0U CellFormat cfDefault = new CellFormat(); cfDefault.Alignment = new Alignment(); cfDefault.NumberFormatId = 0; cfDefault.FontId = 0; cfDefault.BorderId = 0; cfDefault.FillId = 0; cfDefault.ApplyAlignment = true; cfDefault.ApplyBorder = true; stylesheet.CellFormats.Append(cfDefault); //styleIndex =1U CellFormat cfTitle = new CellFormat(); cfTitle.Alignment = new Alignment(); cfTitle.NumberFormatId = 0; cfTitle.FontId = 1; cfTitle.BorderId = 1; cfTitle.FillId = 0; cfTitle.ApplyBorder = true; cfTitle.ApplyAlignment = true; cfTitle.Alignment.Horizontal = HorizontalAlignmentValues.Center; stylesheet.CellFormats.Append(cfTitle); //styleIndex =2U CellFormat cfHeader = new CellFormat(); cfHeader.Alignment = new Alignment(); cfHeader.NumberFormatId = 0; cfHeader.FontId = 2; cfHeader.BorderId = 1; cfHeader.FillId = 2; cfHeader.ApplyAlignment = true; cfHeader.ApplyBorder = true; cfHeader.ApplyFill = true; cfHeader.Alignment.Horizontal = HorizontalAlignmentValues.Center; stylesheet.CellFormats.Append(cfHeader); //styleIndex =3U CellFormat cfContent = new CellFormat(); cfContent.Alignment = new Alignment(); cfContent.NumberFormatId = 0; cfContent.FontId = 0; cfContent.BorderId = 1; cfContent.FillId = 0; cfContent.ApplyAlignment = true; cfContent.ApplyBorder = true; stylesheet.CellFormats.Append(cfContent); workbookPart.WorkbookStylesPart.Stylesheet.Save(); return stylesheet; } /// <summary> /// 创建文本单元格,Cell的内容均视为文本 /// </summary> /// <param name="columnIndex"></param> /// <param name="rowIndex"></param> /// <param name="cellValue"></param> /// <param name="styleIndex"></param> /// <returns></returns> private Cell CreateTextCell(int columnIndex, int rowIndex, object cellValue, Nullable<uint> styleIndex) { Cell cell = new Cell(); cell.DataType = CellValues.InlineString; cell.CellReference = GetCellReference(columnIndex) + rowIndex; if (styleIndex.HasValue) cell.StyleIndex = styleIndex.Value; InlineString inlineString = new InlineString(); Text t = new Text(); t.Text = cellValue.ToString(); inlineString.AppendChild(t); cell.AppendChild(inlineString); return cell; } /// <summary> /// 创建值单元格，Cell会根据单元格值的类型 /// </summary> /// <param name="columnIndex"></param> /// <param name="rowIndex"></param> /// <param name="cellValue"></param> /// <param name="styleIndex"></param> /// <returns></returns> private Cell CreateValueCell(int columnIndex, int rowIndex, object cellValue, Nullable<uint> styleIndex) { Cell cell = new Cell(); cell.CellReference = GetCellReference(columnIndex) + rowIndex; CellValue value = new CellValue(); value.Text = cellValue.ToString(); //apply the cell style if supplied if (styleIndex.HasValue) cell.StyleIndex = styleIndex.Value; cell.AppendChild(value); return cell; } /// <summary> /// 获取行引用，如A1 /// </summary> /// <param name="colIndex"></param> /// <returns></returns> private string GetCellReference(int colIndex) { int dividend = colIndex; string columnName = String.Empty; int modifier; while (dividend > 0) { modifier = (dividend - 1) % 26; columnName = Convert.ToChar(65 + modifier).ToString() + columnName; dividend = (int)((dividend - modifier) / 26); } return columnName; } /// <summary> /// 创建行数据,不同类型使用不同的styleIndex /// </summary> /// <param name="dataRow"></param> /// <param name="rowIndex"></param> /// <returns></returns> private Row CreateDataRow(DataRow dataRow, int rowIndex) { Row row = new Row { RowIndex = (UInt32)rowIndex }; //Nullable<uint> styleIndex = null; double doubleValue; int intValue; DateTime dateValue; decimal decValue; for (int i = 0; i < dataRow.Table.Columns.Count; i++) { Cell dataCell; if (DateTime.TryParse(dataRow[i].ToString(), out dateValue) && dataRow[i].GetType() == typeof(DateTime)) { dataCell = CreateTextCell(i + 1, rowIndex, dataRow[i], 3u); //dataCell.DataType = CellValues.Date; } else if (decimal.TryParse(dataRow[i].ToString(), out decValue) && dataRow[i].GetType() == typeof(decimal)) { dataCell = CreateValueCell(i + 1, rowIndex, decValue, 3u); } else if (int.TryParse(dataRow[i].ToString(), out intValue) && dataRow[i].GetType() == typeof(int)) { dataCell = CreateValueCell(i + 1, rowIndex, intValue, 3u); } else if (Double.TryParse(dataRow[i].ToString(), out doubleValue) && dataRow[i].GetType() == typeof(double)) { dataCell = CreateValueCell(i + 1, rowIndex, doubleValue, 3u); } else { dataCell = CreateTextCell(i + 1, rowIndex, dataRow[i], 3u); } row.AppendChild(dataCell); //styleIndex = null; } return row; } /// <summary> /// 将DataTable的列名称导入Excel /// </summary> /// <param name="dt"></param> /// <param name="sheetData"></param> private void CreateTableHeader(DataTable dt, SheetData sheetData) { Row header = new Row { RowIndex = (UInt32)1 }; int colCount = dt.Columns.Count; for(int i = 0; i < colCount; i++) { string colName = dt.Columns[i].ColumnName; Cell dataCell = CreateTextCell( i + 1, 1, colName, 3u); header.AppendChild(dataCell); } //Row contentRow = CreateDataRow(header, 1); sheetData.AppendChild(header); } /// <summary> /// 将DataTable的数据导入Excel /// </summary> /// <param name="dt"></param> /// <param name="sheetData"></param> private void InsertDataIntoSheet(DataTable dt, SheetData sheetData) { //SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild<SheetData>(); //CreateTableHeader(dt, sheetData); for (int i = 0; i < dt.Rows.Count; i++) { Row contentRow = CreateDataRow(dt.Rows[i], i + 2); sheetData.AppendChild(contentRow); } return; } /// <summary> /// 创建一个SharedStringTablePart(相当于各Sheet共用的存放字符串的容器) /// </summary> /// <param name="workbookPart"></param> /// <returns></returns> private SharedStringTablePart CreateSharedStringTablePart(WorkbookPart workbookPart) { SharedStringTablePart shareStringPart = null; if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0) { shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First(); } else { shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>(); } return shareStringPart; } /// <summary> /// 导出Excel，执行函数 /// </summary> /// <param name="dt"></param> /// <param name="filePath"></param> public void DataTableToExcel(DataTable dt, string filePath) { try { using (SpreadsheetDocument document = CreateParts(filePath)) { WorksheetPart worksheetPart = CreateWorksheet(document.WorkbookPart, dt.TableName); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>(); Stylesheet styleSheet = CreateStylesheet(document.WorkbookPart); //InsertTableTitle(parameter.SheetName, sheetData, styleSheet); // MergeTableTitleCells(dt.Columns.Count, worksheetPart.Worksheet); CreateTableHeader(dt, sheetData); InsertDataIntoSheet(dt, sheetData); SharedStringTablePart sharestringTablePart = CreateSharedStringTablePart(document.WorkbookPart); sharestringTablePart.SharedStringTable = new SharedStringTable(); sharestringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text("ExcelReader"))); sharestringTablePart.SharedStringTable.Save(); } //result = 0; } catch (Exception ex) { //iSession.AddError(ex); //result = error_result_prefix - 99; } //return result; } #endregion }
