/*本方法通過(guò)打開(kāi)一個(gè)具有一定分隔格式的文本到EXCEL中，并且由EXCEL進(jìn)行整理
 * openfile參數(shù)：打開(kāi)的文件絕對(duì)完整路徑及名稱(chēng)。splitchar參數(shù)：分隔符。ReplaceChars參數(shù)：一個(gè)二維數(shù)組，用于整理后替換相關(guān)字符的數(shù)組，第一維為查找字符串
 * ，第二維為要替換的字符串。allowtodataset參數(shù):是否允許整理后生成一個(gè)dataset對(duì)象。ValidResult參數(shù)：指定有效的字段生成數(shù)，如果小于1則不進(jìn)行判斷，否則如果
 * 生成的最終列數(shù)與此值不符，則生成錯(cuò)誤信息。StartCol參數(shù):指定額外的分隔列策略，大于0為不處理。小于1則表示以最大列加上此值為基準(zhǔn)行進(jìn)行倒序刪除，
 * 刪除位移為offerset參數(shù)指定的數(shù)值。ExtraSplit參數(shù)：是否指定一系列列值進(jìn)行刪除，這些列可能是無(wú)規(guī)律的，如1、6、19列等。這些列的值將在Esplits參數(shù)數(shù)組中定義
 * 注意Esplits數(shù)組的指定生效順序在StartCol參數(shù)之后，如果StartCol參數(shù)有效的話(huà)。obj_table參數(shù):是否有目標(biāo)參照表SQL語(yǔ)句返回的結(jié)果與文本列進(jìn)行對(duì)應(yīng)
 * XmlCfg 文件，如果您有XML配置文件，則可以忽略除openfile以外所有的參數(shù)傳遞，本函數(shù)將分析此配置文件的內(nèi)容，如果分析失敗則整個(gè)函數(shù)將失敗
 *ref_maxcolid，由用戶(hù)指定在打開(kāi)文本文件之后應(yīng)該生成的最大的列，一般這個(gè)參數(shù)用于最后一列都為空的情況，因?yàn)檫@樣EXCEL無(wú)法定位最后一個(gè)單元格，如果為0則跳過(guò)
*/
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] = ""; //存儲(chǔ)返回生成的EXCEL文件
            rv[1] = ""; //返回錯(cuò)誤信息或附加的信息
            rv[2] = null;
            rv[3] = "";
            try
            {
                //創(chuàng)建EXCEL應(yīng)用對(duì)象
                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] = "生成的最終數(shù)據(jù)結(jié)果與指定的列數(shù)目不符合。\r\n用戶(hù)指定的有效列為：" +
                        ValidResult.ToString() + "\r\n系統(tǒng)生成的列：" + (maxcolid - delcount).ToString() + "附加的列：" + _addcol.ToString() +
                        "\r\n系統(tǒng)檢測(cè)到的最大列：" + maxcolid.ToString(); //返回錯(cuò)誤信息
                    return rv;
                }
                //創(chuàng)建模板的映像解析文件，最終以變量 desfilename 為輸出對(duì)象
                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] = "準(zhǔn)備數(shù)據(jù)成功，共有記錄" + 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，則將連續(xù)分隔符視為一個(gè)分隔符，如“##” 則視為“#”
object[,] AddCols = new object[1, 3];
AddCols[0,0]=4;
AddCols[0,1]="導(dǎo)入標(biāo)志";
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]);