本文利用第三方开源库NPOI实现Excel97-2003,Excel2007+的数据导入导出操作。不依赖Office是否安装。NPOI开源项目地址:http://npoi.codeplex.com/。
库文件下载:http://npoi.codeplex.com/releases/view/115353
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Data;
- using System.IO;
- using System.Reflection;
- using System.Text;
- using System.Windows.Forms;
- using NPOI.HPSF;
- using NPOI.HSSF.UserModel;
- using NPOI.HSSF.Util;
- using NPOI.SS.UserModel;
- using NPOI.SS.Util;
- using NPOI.XSSF.UserModel;
- using NPOI.XSSF.Util;
- namespace Youwei.Common
- {
- /// <summary>
- /// Excel操作类。利用第三方开源库NPOI实现Excel97-2003,Excel2007+的数据导入导出操作。不依赖Office是否安装。
- /// NPOI开源项目地址:http://npoi.codeplex.com/
- /// </summary>
- public class ExcelHelper
- {
- private static String FilterExcel = "Excel文件 (*.xls;*.xlsx)|*.xls;*.xlsx";
- /// <summary>
- /// 从网格将数据导出到Excel,支持Excel97-2003(.xls)、Excel2007+(.xlsx)
- /// </summary>
- /// <param name="dgv">网格</param>
- /// <param name="ignoredColumns">要忽略导出的列名称集合</param>
- /// <param name="fileName">导出到的文件名。为空时将弹出保存对话框</param>
- public static void ExportToExcel(DataGridView dgv,List<string> ignoredColumns = null,string fileName = "")
- {
- if (String.IsNullOrEmpty(fileName))
- fileName = Dialog.SaveFileDialog(FilterExcel);
- if (String.IsNullOrEmpty(fileName))
- return;
- bool isSuccess = false;
- IWorkbook workBook = null;
- ISheet sheet = null;
- IRow dataRow = null;
- try
- {
- //不同格式实例化不同工作薄
- if (fileName.EndsWith(".xls"))
- workBook = new HSSFWorkbook();
- else if (fileName.EndsWith(".xlsx"))
- workBook = new XSSFWorkbook();
- sheet = workBook.CreateSheet();
- dataRow = sheet.CreateRow(0);
- //表头样式
- ICellStyle headerStyle = workBook.CreateCellStyle();
- headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
- headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
- headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
- headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
- headerStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
- headerStyle.LeftBorderColor = HSSFColor.Black.Index;
- headerStyle.RightBorderColor = HSSFColor.Black.Index;
- headerStyle.TopBorderColor = HSSFColor.Black.Index;
- IFont font = workBook.CreateFont();
- font.FontHeightInPoints = 10;
- font.Boldweight = 700;
- headerStyle.SetFont(font);
- headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
- int i = 0,j = 0;
- int ignoreCnt = 0;
- //填充表头
- for (i = 0; i < dgv.Columns.Count; i++)
- {
- if ((ignoredColumns != null && ignoredColumns.Contains(dgv.Columns[i].Name)) || !dgv.Columns[i].Visible || string.IsNullOrEmpty(dgv.Columns[i].HeaderText))
- {
- ignoreCnt++;
- continue;
- }
- dataRow.CreateCell(i - ignoreCnt).SetCellValue(dgv.Columns[i].HeaderText);
- dataRow.Cells[i - ignoreCnt].CellStyle = headerStyle;
- }
- //内容样式
- ICellStyle cellStyle = headerStyle;
- cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
- font = workBook.CreateFont();
- font.FontHeightInPoints = 10;
- font.Boldweight = 100;
- cellStyle.SetFont(font);
- //填充内容
- DataGridViewCell cell = null;
- for (i = 0; i < dgv.Rows.Count; i++)
- {
- dataRow = sheet.CreateRow(i + 1);
- ignoreCnt = 0;
- for (j = 0; j < dgv.Columns.Count; j++)
- {
- if ((ignoredColumns != null && ignoredColumns.Contains(dgv.Columns[j].Name)) || !dgv.Columns[j].Visible || string.IsNullOrEmpty(dgv.Columns[j].HeaderText))
- {
- ignoreCnt++;
- continue;
- }
- cell = dgv[j,i];
- if (cell is DataGridViewComboBoxCell)
- dataRow.CreateCell(j - ignoreCnt).SetCellValue(ConvertHelper.ToString(cell.FormattedValue));
- else
- dataRow.CreateCell(j - ignoreCnt).SetCellValue(ConvertHelper.ToString(cell.Value));
- dataRow.Cells[j - ignoreCnt].CellStyle = cellStyle;
- }
- }
- //写文件
- using (MemoryStream ms = new MemoryStream())
- {
- using (FileStream fs = new FileStream(fileName,FileMode.Create,FileAccess.Write))
- {
- workBook.Write(ms);
- ms.Flush();
- ms.Position = 0;
- byte[] data = ms.ToArray();
- fs.Write(data,data.Length);
- fs.Flush();
- data = null;
- isSuccess = true;
- }
- }
- //打开文件
- if (isSuccess && Dialog.Confirm("数据已经导出到Excel成功,你要打开吗?") == DialogResult.Yes)
- {
- Util.OpenFile(fileName);
- }
- }
- catch (Exception ex)
- {
- Dialog.Error(ex,System.Reflection.MethodBase.GetCurrentMethod(),true);
- }
- finally
- {
- if (dataRow != null)
- dataRow = null;
- if (sheet != null)
- sheet = null;
- if (workBook != null)
- {
- workBook.Clear();
- workBook = null;
- }
- }
- }
- /// <summary>
- /// 从Excel导入数据到实体类集合。支持Excel97-2003(.xls)、Excel2007+(.xlsx)
- /// </summary>
- /// <typeparam name="T">实体类</typeparam>
- /// <param name="fileName">要导入的Excel文件名。为空时将弹出保存对话框</param>
- /// <param name="propertyTextNamePair">实体类属性的名称及属性名对应键值对</param>
- /// <param name="startSheet">导入的起始Excel表单序号</param>
- /// <param name="startRow">导入的起始Excel行号</param>
- /// <param name="startColumn">导入的起始Excel列号</param>
- /// <returns>泛型实体类集合</returns>
- public static List<T> ImportFromExcel<T>(string fileName,Dictionary<string,string> propertyTextNamePair,int startSheet = 0,int startRow = 0,int startColumn = 0) where T : new()
- {
- List<T> list = new List<T>();
- if (String.IsNullOrEmpty(fileName) || !System.IO.File.Exists(fileName))
- fileName = Dialog.OpenFileDialog(FilterExcel);
- if (String.IsNullOrEmpty(fileName) || !System.IO.File.Exists(fileName))
- return list;
- IWorkbook workBook = null;
- ISheet sheet = null;
- IRow row = null;
- try
- {
- //加载文档
- using (FileStream fileStream = new FileStream(fileName,FileMode.Open))
- {
- //不同格式实例化不同工作薄
- if (fileName.EndsWith(".xls"))
- workBook = new HSSFWorkbook(fileStream);
- else if (fileName.EndsWith(".xlsx"))
- workBook = new XSSFWorkbook(fileStream);
- }
- //加载指定工作薄
- sheet = workBook.GetSheetAt(startSheet);
- //workBook.NumberOfSheets //工作薄的表单数
- //加载表头
- IRow headerRow = sheet.GetRow(startRow);
- ICell cellHeader = null;
- ICell cell = null;
- int cellCount = headerRow.LastCellNum;
- //获取实体类属性
- Type type = typeof(T);
- PropertyInfo[] ps = type.GetProperties();
- T t = default(T);
- PropertyInfo p = null;
- //遍历行列,赋值到实体类,并添加到实体类集合
- for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
- {
- row = sheet.GetRow(i);
- t = new T();
- for (int j = 0; j < cellCount; j++)
- {
- cellHeader = headerRow.GetCell(j);
- cell = row.GetCell(j);
- if (propertyTextNamePair != null && propertyTextNamePair.ContainsKey(cellHeader.ToString()))
- p = type.GetProperty(propertyTextNamePair[cellHeader.ToString()]);
- else
- p = type.GetProperty(cellHeader.ToString());
- if (p != null)
- p.SetValue(t,Convert.ChangeType(cell.ToString(),p.PropertyType),null);
- }
- list.Add(t);
- }
- //回收资源
- ps = null;
- cellHeader = null;
- headerRow = null;
- }
- catch (Exception ex)
- {
- Dialog.Error(ex,true);
- }
- finally
- {
- if (row != null)
- row = null;
- if (sheet != null)
- sheet = null;
- if (workBook != null)
- {
- workBook.Clear();
- workBook = null;
- }
- }
- return list;
- }
- }
- }