你的位置:首页 > ASP.net教程

[ASP.net教程]办公超实用利器系列


背景

1.在测绘行业中涉及到地籍业务,除了gis,表单生成和档案扫描其实是最消耗人力和时间的一块。

2.之前经常遇到Excle表格的定制输出每次都要用NPOI累代码很烦躁。想到用

3.效果很好,稍微教一下,不会宏命令和编程的同事也会用,上手极快,利用NPOI输出也比原生COM的速度快几倍。

4.最后形成了一个系列,见项目名称,包括Excle、word的输出,打印、图片扫描工具,有很多复杂的格式设定功能没有延伸下去就辞职了。

功能

很简单的流程-设置数据源(MDB文件)、

关键代码

1.设定动作模型

 /// <summary>  /// 动作参数  /// </summary>  public class Action  {    public string OID { get; set; }    public string ColNm { get; set; }    public string RowNm { get; set; }    public string SheetNm { get; set; }    public string FieldName { get; set; }    public string ActionType { get; set; }    public string Param { get; set; }    public string AppendType { get; set; }  }  /// <summary>  /// 按表单分组  /// </summary>  public class SheetActionGroup  {    public int SheetNm { get; set; }    public List<Action> Actions { get; set; }    public SheetActionGroup()    {      Actions = new List<Action>();    }  }  public class GeneralSetting  {    public string MDBTableName { get; set; }    public string saveName { get; set; }    public bool isChildFolder { get; set; }    public string childFolderName { get; set; }    public string OIDField { get; set; }    public string templateName { get; set; }  }

View Code

2.设定动作定义

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Text.RegularExpressions;using System.IO;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;namespace DAL.MDB2EXCEL{  public class ActionModual  {    /// <summary>    /// 按表划分的动作设置    /// </summary>    /// <param name="acRecs"></param>    /// <returns></returns>    public static List<Repository.SheetActionGroup> GetSheetGroups(List<Repository.Action> acRecs)    {      List<Repository.SheetActionGroup> sheetRecs = new List<Repository.SheetActionGroup>();      foreach (var item in acRecs)      {        var match = sheetRecs.Find(cc => cc.SheetNm == int.Parse(item.SheetNm));        if (match == null)        {          Repository.SheetActionGroup sheetRec = new Repository.SheetActionGroup();          sheetRec.SheetNm = int.Parse(item.SheetNm);          sheetRec.Actions.Add(item);          sheetRecs.Add(sheetRec);        }        else        {          match.Actions.Add(item);        }      }      return sheetRecs;    }    public static Dictionary<string, Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>> InitCellFormateStrategy(Dictionary<string, Action<string, ICell, HSSFWorkbook>> appendStrategy)    {      //"直接输出", "数字格式", "日期格式", "符号隔断", "添加内容", "固定值", "普通正则","复杂正则"      Dictionary<string, Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>> format = new Dictionary<string, Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>>();      #region "中文格式"      format.Add("直接输出", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          string strValue = dr[acRec.FieldName].ToString();          IRow oRow = oSheet.GetRow(int.Parse(acRec.RowNm));          ICell oCell = oRow.Cells[int.Parse(acRec.ColNm)];          appendStrategy[acRec.AppendType].Invoke(strValue, oCell, wb);        }));      #endregion      #region "数字格式"      format.Add("数字格式", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          string strValue = double.Parse(dr[acRec.FieldName].ToString()).ToString(acRec.Param);          appendStrategy[acRec.AppendType].Invoke(strValue, oSheet.GetRow(int.Parse(acRec.RowNm)).Cells[int.Parse(acRec.ColNm)], wb);        }));      #endregion      #region "日期格式"      format.Add("日期格式", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          string strValue = DateTime.Parse(dr[acRec.FieldName].ToString()).ToString(acRec.Param);          appendStrategy[acRec.AppendType].Invoke(strValue, oSheet.GetRow(int.Parse(acRec.RowNm)).Cells[int.Parse(acRec.ColNm)], wb);        }));      #endregion      #region "符号隔断"      format.Add("符号隔断", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          string[] paramArr = acRec.Param.Split(';');          char[] spliter = paramArr[0].ToCharArray();          string[] strArr = dr[acRec.FieldName].ToString().Split(spliter[0]);          string strValue = string.Empty;          for (int i = 1; i < paramArr.Length; i++)          {            strValue += strArr[int.Parse(paramArr[i])] + paramArr[0];          }          strValue = strValue.Substring(0, strValue.Length - 1);          appendStrategy[acRec.AppendType].Invoke(strValue, oSheet.GetRow(int.Parse(acRec.RowNm)).Cells[int.Parse(acRec.ColNm)], wb);        }));      #endregion      #region "添加内容"      format.Add("添加内容", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          string[] paramArr = acRec.Param.Split(';');          string strValue = string.Empty;          if (paramArr[0] == "前")          {            strValue = paramArr[1] + dr[acRec.FieldName].ToString();          }          else          {            strValue = dr[acRec.FieldName].ToString() + paramArr[1];          }          appendStrategy[acRec.AppendType].Invoke(strValue, oSheet.GetRow(int.Parse(acRec.RowNm)).Cells[int.Parse(acRec.ColNm)], wb);        }));      #endregion      #region "固定值"      format.Add("固定值", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          appendStrategy[acRec.AppendType].Invoke(acRec.Param, oSheet.GetRow(int.Parse(acRec.RowNm)).Cells[int.Parse(acRec.ColNm)], wb);        }));      #endregion      #region "正则替换"      format.Add("正则替换", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          string[] paramArr = acRec.Param.Split(';');          string strValue = dr[acRec.FieldName].ToString();          strValue = Regex.Replace(strValue, paramArr[0], paramArr[1]);          appendStrategy[acRec.AppendType].Invoke(strValue, oSheet.GetRow(int.Parse(acRec.RowNm)).Cells[int.Parse(acRec.ColNm)], wb);        }));      #endregion      #region "正则提取"      format.Add("正则提取", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          string[] paramArr = acRec.Param.Split(';');          string strValue = dr[acRec.FieldName].ToString();          MatchCollection matches = Regex.Matches(strValue, paramArr[0]);          if (matches.Count != 0)          {            strValue = matches[int.Parse(paramArr[1])].Value;          }          appendStrategy[acRec.AppendType].Invoke(strValue, oSheet.GetRow(int.Parse(acRec.RowNm)).Cells[int.Parse(acRec.ColNm)], wb);        }));      #endregion      #region "复杂正则"      format.Add("复杂正则", new Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>(        (DataRow dr, Repository.Action acRec, ISheet oSheet, HSSFWorkbook wb) =>        {          string strValue = string.Empty;          List<string> param = DAL.CORE.RegexEx.GetParam(acRec.Param);          foreach (var item in param)          {            if (!item.Contains("%"))            {              strValue += item;            }            else            {              string fileName = DAL.CORE.RegexEx.GetFieldName(item);              strValue += dr[fileName].ToString();            }          }          appendStrategy[acRec.AppendType].Invoke(strValue, oSheet.GetRow(int.Parse(acRec.RowNm)).Cells[int.Parse(acRec.ColNm)], wb);        }));      #endregion      return format;    }    public static Dictionary<string, Action<string, ICell, HSSFWorkbook>> InitCellAppendStrategy()    {      Dictionary<string, Action<string, ICell, HSSFWorkbook>> strategy = new Dictionary<string, Action<string, ICell, HSSFWorkbook>>();      strategy.Add("直接覆盖", new Action<string, ICell, HSSFWorkbook>(        (string strValue, ICell oCell, HSSFWorkbook wb) =>        {          oCell.SetCellValue(strValue);        }));      strategy.Add("追加到前", new Action<string, ICell, HSSFWorkbook>(       (string strValue, ICell oCell, HSSFWorkbook wb) =>       {         string O1 = oCell.ToString();         oCell.SetCellValue(strValue + O1);       }));      strategy.Add("追加到后", new Action<string, ICell, HSSFWorkbook>(       (string strValue, ICell oCell, HSSFWorkbook wb) =>       {         string O1 = oCell.ToString();         oCell.SetCellValue(O1 + strValue);       }));      return strategy;    }  }}

View Code

3.数据输出

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.ComponentModel;using System.IO;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using System.Text.RegularExpressions;namespace DAL.MDB2EXCEL{  public class ExcelModual  {    #region 一条记录生成一个表    public static void OneToOne(List<Repository.Action> acRecs, Repository.GeneralSetting generalRec, DataSet dbSource, string templatePath, string savePath, BackgroundWorker bWork)    {      #region 打开模板      bWork.ReportProgress(10, "打开模板");      HSSFWorkbook wb = FrameWork.ExcelTool.Open(templatePath);      #endregion      #region 重构动作/初始化解集      bWork.ReportProgress(10, "解析动作");      List<Repository.SheetActionGroup> sheetRecs = ActionModual.GetSheetGroups(acRecs);      Dictionary<string, Action<string, ICell, HSSFWorkbook>> appendStrategy = ActionModual.InitCellAppendStrategy();      Dictionary<string, Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>> CellFormateStategy = ActionModual.InitCellFormateStrategy(appendStrategy);      #endregion      #region 插入数据      for (int i = 0; i < dbSource.Tables[0].Rows.Count; i++)      {        #region 执行操作        foreach (var sheetRec in sheetRecs)        {          bWork.ReportProgress(10, "处理模板表" + sheetRec.SheetNm.ToString());          ISheet oSheet = wb.GetSheetAt(sheetRec.SheetNm);          foreach (var acRec in sheetRec.Actions)          {            try            {              CellFormateStategy[acRec.ActionType].Invoke(dbSource.Tables[0].Rows[i], acRec, oSheet, wb);            }            catch (Exception ex)            {              throw new Exception(ex.Message + "\n动作类型:" + acRec.ActionType + "\n数据:" + dbSource.Tables[0].Rows[i][acRec.FieldName].ToString() + "\n字段名称:" + acRec.FieldName + "\n表号:" + acRec.SheetNm + "\n行号:" + acRec.RowNm + "\n列号:" + acRec.ColNm + "\n请完整截图,然后把模板、数据、配置发给我");            }          }        }        #endregion        #region 保存数据        bWork.ReportProgress(10, "保存文件" + CORE.Common.GetMatchStringByRegex(generalRec.saveName, dbSource.Tables[0].Rows[i]));        string saveFullName = savePath + "\\" + CORE.Common.GetMatchStringByRegex(generalRec.saveName, dbSource.Tables[0].Rows[i]) + ".xls";        if (generalRec.isChildFolder)        {          if (!Directory.Exists(savePath + "\\" + generalRec.childFolderName))          {            Directory.CreateDirectory(savePath + "\\" + generalRec.childFolderName);          }          saveFullName = savePath + "\\" + generalRec.childFolderName + "\\" + CORE.Common.GetMatchStringByRegex(generalRec.saveName, dbSource.Tables[0].Rows[i]) + ".xls";        }        FrameWork.ExcelTool.SaveAs(wb, saveFullName);        #endregion      }      #endregion    }    #endregion    #region 多条记录生成一个表    public static void MulityToOne(List<Repository.Action> acRecs, Repository.GeneralSetting generalRec, DataSet dbSource, string templatePath, string savePath, BackgroundWorker bWork)    {      #region 打开模板      bWork.ReportProgress(10, "打开模板");      HSSFWorkbook wb = FrameWork.ExcelTool.Open(templatePath);      #endregion      #region 重构动作/初始化解集      bWork.ReportProgress(10, "解析动作");      List<Repository.SheetActionGroup> sheetRecs = ActionModual.GetSheetGroups(acRecs);      Dictionary<string, Action<string, ICell, HSSFWorkbook>> appendStrategy = ActionModual.InitCellAppendStrategy();      #endregion      #region 插入数据      foreach (var sheetRec in sheetRecs)      {        bWork.ReportProgress(10, "处理模板表" + sheetRec.SheetNm.ToString());        ISheet oSheet = wb.GetSheetAt(sheetRec.SheetNm);        Dictionary<string, Action<DataRow, Repository.Action, ISheet, HSSFWorkbook>> CellFormateStategy = ActionModual.InitCellFormateStrategy(appendStrategy);        for (int i = 0; i < dbSource.Tables[0].Rows.Count; i++)        {          #region 执行操作          foreach (var acRec in acRecs)          {            CellFormateStategy[acRec.ActionType].Invoke(dbSource.Tables[0].Rows[i], acRec, oSheet, wb);          }          #endregion        }      }      #endregion      #region 保存数据      bWork.ReportProgress(10, "保存文件");      string saveFullName = savePath + "\\导出结果.xls";      if (generalRec.isChildFolder)      {        if (!Directory.Exists(savePath + "\\" + generalRec.childFolderName))        {          Directory.CreateDirectory(savePath + "\\" + generalRec.childFolderName);        }        saveFullName = savePath + "\\" + generalRec.childFolderName + "\\导出结果.xls";      }      FrameWork.ExcelTool.SaveAs(wb, saveFullName);      #endregion    }    #endregion  }}

View Code

 

源码

http://pan.baidu.com/s/1bn5AylD 密码:op8g

 

1.代码没有整理,但备注的已经很清楚了,随便用。

2.引用的Office的COM的可能需要重新引用下,我都忘了到底用没用了。

3.有不懂的欢迎提问。

4.用着效果好请回来顶一下。