目录

需求

Excel 的文本文件导入功能

范例运行环境

配置Office DCOM

实现

组件库引入

OpenTextToExcelFile

代码

调用

小结


需求

在一些导入功能里,甲方经常会给我们一些格式化的文本,类似 CSV 那样的纯文本。比如有关质量监督的标准文件(如国家标准、地方标准、企业标准等),还有一此国际标准文件等等。提供给我们的这些文件是文件尺寸比较大的纯文本文件,文件内容是格式化的文本,具有规律的分隔字符。Excel 本身提供有导入文本文件的功能,但由于标准制定和发布是比较频繁,每次的导入与整理还是比较耗时的,因些实现文本文件导入到 Excel 的功能可以更快速的解决重复劳动和错误,实现流程自动化的一环。

Excel 的文本文件导入功能

我们运行 Excel ,点击选择打开文本文件时,会弹出一个导入向导,如下图:

 C# 实现格式化文本导入到Excel-LMLPHP

 C# 实现格式化文本导入到Excel-LMLPHP

如图我们需要选择合适的文本文件原始编码,输入分隔符,选择其它的选项,如连续的分隔符号视分单个处理等。下面我们将介绍如何利用 COM 来实现这一操作的自动化处理。 

范例运行环境

操作系统: Windows Server 2019 DataCenter

操作系统上安装 Office Excel 2016

.net版本: .netFramework4.7.1 或以上

开发工具:VS2019  C#

配置Office DCOM

配置方法可参照我的文章《C# 读取Word表格到DataSet》进行处理和配置。

实现

组件库引入

C# 实现格式化文本导入到Excel-LMLPHP

OpenTextToExcelFile

OpenTextToExcelFile方法返回 object[] 类型,object[0] 返回生成成功的 Excel 文件地址,object[1]返回错误信息,其实体为 string 类型。方法参数据说明见下表:

代码

方法完整代码如下:

/*本方法通过打开一个具有一定分隔格式的文本到EXCEL中,并且由EXCEL进行整理
 * openfile参数:打开的文件绝对完整路径及名称。splitchar参数:分隔符。ReplaceChars参数:一个二维数组,用于整理后替换相关字符的数组,第一维为查找字符串
 * ,第二维为要替换的字符串。allowtodataset参数:是否允许整理后生成一个dataset对象。ValidResult参数:指定有效的字段生成数,如果小于1则不进行判断,否则如果
 * 生成的最终列数与此值不符,则生成错误信息。StartCol参数:指定额外的分隔列策略,大于0为不处理。小于1则表示以最大列加上此值为基准行进行倒序删除,
 * 删除位移为offerset参数指定的数值。ExtraSplit参数:是否指定一系列列值进行删除,这些列可能是无规律的,如1、6、19列等。这些列的值将在Esplits参数数组中定义
 * 注意Esplits数组的指定生效顺序在StartCol参数之后,如果StartCol参数有效的话。obj_table参数:是否有目标参照表SQL语句返回的结果与文本列进行对应
 * XmlCfg 文件,如果您有XML配置文件,则可以忽略除openfile以外所有的参数传递,本函数将分析此配置文件的内容,如果分析失败则整个函数将失败
 *ref_maxcolid,由用户指定在打开文本文件之后应该生成的最大的列,一般这个参数用于最后一列都为空的情况,因为这样EXCEL无法定位最后一个单元格,如果为0则跳过
*/
public object[] OpenTextToExcelFile(string OpenFile, string SplitChar, string[,] ReplaceChars, int ValidResult, bool ExtraSplit, int[] Esplits, object[,] AddCols, int ref_maxcolid, int StartCol = 1, int offerset = 1)
{
            object[] rv = new object[4];
            rv[0] = ""; //存储返回生成的EXCEL文件
            rv[1] = ""; //返回错误信息或附加的信息
            rv[2] = null;
            rv[3] = "";
            try
            {
                //创建EXCEL应用对象
                ExcelApplication excel = new ExcelApplication();
                Workbooks workbook = excel.Workbooks;

                object[,] dlist = new object[ref_maxcolid, 2];
                for (int i = 0; i < ref_maxcolid; i++)
                {
                    dlist[i, 0] = i + 1;
                    dlist[i, 1] = Excel.XlColumnDataType.xlTextFormat;
                }
                workbook.OpenText(OpenFile, 20936, 1, Excel.XlTextParsingType.xlDelimited,
                    Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, false, false, false, false, true, SplitChar,
                    dlist,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                Excel.Range _range;
                int maxcolid = excel.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
                if (ref_maxcolid > 0)
                {
                    maxcolid = ref_maxcolid;
                }
                int maxrowid = excel.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
                int _addcol = 0;
                if (AddCols != null)
                {
                    _addcol = AddCols.GetLength(0);
                }

                int delcount = 0;
                if (StartCol <= 0)
                {
                    for (int i = (maxcolid + StartCol); i >= 1; i -= offerset)
                    {
                        _range = excel.get_Range(excel.Cells[1, i], excel.Cells[65536, i]);
                        _range.Select();
                        _range.Delete(Type.Missing);
                        delcount++;
                    }
                }
                if ((ExtraSplit) && (Esplits != null))
                {
                    for (int j = 0; j < Esplits.GetLength(0); j++)
                    {
                        int colid = Esplits[j];
                        _range = excel.get_Range(excel.Cells[1, colid], excel.Cells[65536, colid]);
                        _range.Select();
                        _range.Delete(Type.Missing);
                        delcount++;
                    }
                }
                if ((ValidResult > 0) && ((maxcolid - delcount + _addcol) != ValidResult))
                {
                    rv[1] = "生成的最终数据结果与指定的列数目不符合。\r\n用户指定的有效列为:" +
                        ValidResult.ToString() + "\r\n系统生成的列:" + (maxcolid - delcount).ToString() + "附加的列:" + _addcol.ToString() +
                        "\r\n系统检测到的最大列:" + maxcolid.ToString(); //返回错误信息
                    return rv;
                }
                //创建模板的映像解析文件,最终以变量 desfilename 为输出对象
                FileEx commonApi = new FileEx();
                string _file = "", _path = "";
                _path = Path.GetDirectoryName(OpenFile);
                if (_path.Length > 3)
                {
                    _path += "\\";
                }
                _file = Path.GetFileNameWithoutExtension(OpenFile);
                string _validfilename = commonApi.GetValidFileName(_path, _file, ".xlsx");
                string _lastfile = _path + _validfilename;
                rv[0] = _lastfile;
                if (File.Exists(_lastfile))
                {
                    File.Delete(_lastfile);
                }
                Worksheet worksheet = (Worksheet)excel.ActiveSheet;
                //解决替换字符的要求
                if (ReplaceChars != null)
                {
                    for (int i = 0; i < ReplaceChars.GetLength(0); i++)
                    {
                        string _find = ReplaceChars[i, 0], _rep = ReplaceChars[i, 1];
                        worksheet.Cells.Replace(_find, _rep, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, false, false, false);
                    }
                }
                _range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]);
                _range.EntireRow.Insert(Type.Missing, Type.Missing);
                if (AddCols != null)
                {
                    int ref_col = 0;
                    string ref_fname = "", ref_fvalue = "";
                    Excel.Range _newrange;
                    for (int ad = 0; ad < AddCols.GetLength(0); ad++)
                    {
                        ref_col = (int)AddCols[ad, 0];
                        ref_fname = AddCols[ad, 1].ToString();
                        ref_fvalue = AddCols[ad, 2].ToString();
                        _range = excel.get_Range(excel.Cells[1, ref_col], excel.Cells[1, ref_col]);
                        _range.EntireColumn.Insert(Type.Missing, Type.Missing);
                        //						ref_col=_newrange.Column;
                        excel.Cells[1, ref_col] = ref_fname;
                        if (maxrowid > 0)
                        {
                            excel.Cells[2, ref_col] = ref_fvalue;
                            _newrange = excel.get_Range(excel.Cells[2, ref_col], excel.Cells[2, ref_col]);
                            _newrange.Copy(excel.get_Range(excel.Cells[2, ref_col], excel.Cells[maxrowid + 1, ref_col]));

                        }

                    }
                }

                worksheet.SaveAs(@_lastfile, Excel.XlFileFormat.xlAddIn, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                excel.ActiveWorkbook.Close(false, Type.Missing, Type.Missing);
                excel.Quit();
                rv[1] = "准备数据成功,共有记录" + maxrowid.ToString() + "行。字段" + (maxcolid - delcount + _addcol).ToString() + "列。";
            }
            catch (Exception e)
            {
                rv[0] = "";
                rv[1] = e.Message;
            }

            return rv;
}

调用

调用示例代码如下:

string splitchar = "#";
int validResult = 4;
int origin = 65001;  //utf-8
bool ConsecutiveDelimiter=true; //如果为 true,则将连续分隔符视为一个分隔符,如“##” 则视为“#”
object[,] AddCols = new object[1, 3];
AddCols[0,0]=4;
AddCols[0,1]="导入标志";
AddCols[0,2] = "是";

object[] rv2 = OpenTextToExcelFile("d:\\std.txt", "d:\\std.xls", splitchar, null, validResult, false, null, AddCols, 0, 1, 1, origin, ConsecutiveDelimiter);

Response.Write("result:"+rv2[0] + "<br>" + rv2[1]);

导入的文本文件示例(以两个#号为分隔符)如下图:

 C# 实现格式化文本导入到Excel-LMLPHP

 导入成功后如下图所示:

C# 实现格式化文本导入到Excel-LMLPHP

小结

1、OpenTextToExcelFile方法是一种兼容旧 EXCEL 版本的写法(如2003),我们可以根据实际需要进行改造。

2、许多参数是根据我们在使用过程中的实际需要而设置,以满足特殊需要,简化后期处理。

3、方法的核心是使用 EXCEL COM 的 OpenText 方法,关于该方法的详细介绍请参考如下链接:

https://learn.microsoft.com/zh-cn/office/vba/api/Excel.Workbooks.OpenText#parameters

4、原始文件的编码请参照本文Excel 的文本文件导入功能部分的图示所示,选择框中就是对应的编码代码,如65001表示UTF-8,这也是默认值。20936 则表示简体中文(GB2312-80)等等。

本方法仅做参考,感谢阅读,希望本文能够对您有所帮助。

05-02 04:37