表格导出与导入

导出

   /// <summary>
   /// 导出报表为Csv
   /// </summary>
   /// <param name="dt">DataTable</param>
   /// <param name="strFilePath">物理路径</param>
   /// <param name="tableheader">表头</param>
   /// <param name="columname">字段标题,逗号分隔</param>
   public  bool dt2csv(DataTable dt, string strFilePath, string tableheader, string columname)
        {
            try
            {
                string strBufferLine = "";
                //StreamWriter strmWriterObj = new StreamWriter(strFilePath, false, System.Text.Encoding.UTF8);   中文标题乱码
                StreamWriter strmWriterObj = new StreamWriter(strFilePath, false, Encoding.GetEncoding("GB2312"));
                strmWriterObj.WriteLine(tableheader);
                strmWriterObj.WriteLine(columname);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    strBufferLine = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j > 0)
                            strBufferLine += ",";
                        strBufferLine += dt.Rows[i][j].ToString();
                    }
                    strmWriterObj.WriteLine(strBufferLine);
                }
                strmWriterObj.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

导入

/// <summary>
/// 导入数据
/// </summary>
public void GridCtlImport(string  ModelCode,string DatasetCode)
  {
      OperateReturnInfo returnInfo = null;
      try
      {
          if (string.IsNullOrEmpty(ModelCode) && string.IsNullOrEmpty(DatasetCode)) return;
         
          using (OpenFileDialog openFileDialog = new OpenFileDialog())
          {
              openFileDialog.Filter = "Microsft Excel files(*.xls)|*.xls;*.xlsx";
              if (openFileDialog.ShowDialog() == DialogResult.OK)
              {
                  string strPath = openFileDialog.FileName;
                  DataTable dt = Entities.ExcelToDataTableHelper(strPath, null);
                  if (dt == null) return;
                  foreach (DataRow row in dt.Rows)
                  {
                      Hashtable hs = new Hashtable();
                      foreach (DataColumn dtColumn in dt.Columns)
                      {
                          hs[dtColumn.ColumnName] = row[dtColumn.ColumnName];
                      }
                    returnInfo = ManagerModelSet.ExcuteSQL(hs, ModelCode, DatasetCode);
                      if (returnInfo.ReturnCode != OperateCodeEnum.Success) return;
      
                  }
                  if (returnInfo.ReturnCode==OperateCodeEnum.Success)
                  {
                      XtraMessageBox.ShowSuccessMessage("数据导入成功！");
                  }
                  else
                  {
                      XtraMessageBox.ShowErrorMessage("数据导入失败：导入数据存在问题！");
                  }

              }

          }
      }
      catch (Exception ex)
      {
          XtraMessageBox.ShowErrorMessage("数据导入失败！"+ex.Message);
       
      }
  }

  public void GridCtlExport(DevExpress.XtraGrid.GridControl gridControl)
  {
      using (SaveFileDialog saveDialog = new SaveFileDialog())
      {
          saveDialog.Filter = "Excel(.xls)|*.xls|Excel(.xlsx)|*.xlsx |RichText File (.rtf)|*.rtf |Word File (.docx)|*.docx|Html File (.html)|*.html";
          if (saveDialog.ShowDialog() != DialogResult.Cancel)
          {
              string exportFilePath = saveDialog.FileName;
              string fileExtenstion = new FileInfo(exportFilePath).Extension;

              switch (fileExtenstion)
              {
                  case ".xls":
                      gridControl.ExportToXls(exportFilePath);
                      break;
                  case ".xlsx":
                      gridControl.ExportToXlsx(exportFilePath);
                      break;
                  case ".rtf":
                      gridControl.ExportToRtf(exportFilePath);
                      break;
                  case ".docx":
                      gridControl.ExportToDocx(exportFilePath);
                      break;
                  case ".pdf":
                      gridControl.ExportToPdf(exportFilePath);
                      break;
                  case ".html":
                      gridControl.ExportToHtml(exportFilePath);
                      break;
                  case ".mht":
                      gridControl.ExportToMht(exportFilePath);
                      break;
                  default:
                      break;
              }

              if (File.Exists(exportFilePath))
              {
                  try
                  {
                      //Try to open the file and let windows decide how to open it.
                      System.Diagnostics.Process.Start(exportFilePath);
                  }
                  catch
                  {
                      String msg = "The file could not be opened." + Environment.NewLine + Environment.NewLine + "Path: " + exportFilePath;
                      MessageBox.Show(msg, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                  }
              }
              else
              {
                  String msg = "The file could not be saved." + Environment.NewLine + Environment.NewLine + "Path: " + exportFilePath;
                  MessageBox.Show(msg, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
              }
          }
      }
  }

工具类

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tools
{
    public class Entities : IDisposable
    {
        private string fileName = null; //文件名
        private static IWorkbook workbook = null;
        private static FileStream fs = null;
        private bool disposed;

        public Entities(string fileName)
        {
            this.fileName = fileName;
            disposed = false;

        }

        #region 将excel中的数据导入到DataTable中
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        ///  <param name="fileNameurl">表名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTableHelper(string fileNameurl, string sheetName)
        {
            return ExcelToDataTableHelper(fileNameurl, sheetName, 1);
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        ///  <param name="fileNameurl">表名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="ColumnNum">第几行是表头</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTableHelper(string fileNameurl, string sheetName, int ColumnNum)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileNameurl, FileMode.Open, FileAccess.Read);
                if (fileNameurl.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileNameurl.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow;

                    // firstRow = sheet.GetRow(0);
                    if (ColumnNum > 0)
                    {
                        firstRow = sheet.GetRow(ColumnNum - 1);
                    }
                    else
                    {
                        firstRow = sheet.GetRow(0);
                    }
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null && cellValue != "")
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    cellCount = data.Columns.Count;

                    //找出第几行是列名
                    startRow = ColumnNum;

                    //startRow = firstDataNum-1;
                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null
                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            {
                                //dataRow[j] = row.GetCell(j).ToString();
                                //读取Excel格式，根据格式读取数据类型
                                ICell cell = row.GetCell(j);
                                dataRow[j] = parseExcel(cell);
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return null;
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }
        }

        //格式转换
        private static String parseExcel(ICell cell)
        {
            string result = "";
            switch (cell.CellType)
            {
                case CellType.Formula:
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
                    result = e.Evaluate(cell).StringValue;
                    break;
                case CellType.Numeric:// 数字类型
                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                    {// 处理日期格式、时间格式
                        string sdf = "";
                        if (cell.CellStyle.DataFormat == HSSFDataFormat
                                .GetBuiltinFormat("h:mm"))
                        {
                            sdf = "HH:mm";
                        }
                        else
                        {// 日期
                            sdf = "yyyy-MM-dd";
                        }
                        DateTime date = cell.DateCellValue;
                        result = date.ToString(sdf);
                    }
                    else if (cell.CellStyle.DataFormat == 58)
                    {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        string sdf = "yyyy-MM-dd";
                        double value = cell.NumericCellValue;
                        DateTime date = new DateTime(1899, 12, 30); // 起始时间
                        date = date.AddDays(value);
                        result = date.ToString(sdf);
                    }
                    else
                    {
                        result = cell.NumericCellValue.ToString();
                    }
                    break;
                case CellType.String:// String类型
                    result = cell.StringCellValue;
                    break;
                case CellType.Blank:
                    result = "";
                    break;
                default:
                    result = "";
                    break;
            }
            return result;
        }

        /// <summary>
        /// 获取Exce工作薄名称
        /// </summary>
        /// <param name="fileNameurl"></param>
        /// <returns></returns>
        public static List<string> GetSheetNames(string fileNameurl)
        {
            using (FileStream sr = new FileStream(fileNameurl, FileMode.OpenOrCreate))
            {
                //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
                HSSFWorkbook workbook = new HSSFWorkbook(sr);
                int x = workbook.Workbook.NumSheets;
                List<string> sheetNames = new List<string>();
                for (int i = 0; i < x; i++)
                {
                    sheetNames.Add(workbook.Workbook.GetSheetName(i));
                }
                return sheetNames;
            }
        }

        //资源释放
        public void Dispose()
        {
            //释放资源
            Dispose(true);
            //告诉垃圾回收器不要调用指定对象的Dispose方法
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (fs != null)
                        fs.Close();
                }

                fs = null;
                disposed = true;
            }
        }
        #endregion
    }
}