C#操作Excel文件

采用C#语言创建并填写Excel文件时出现异常来自 hresult 0x800a03ec的错误，网上资料多是说因为Excel的行索引或列索引出现0或者其他版本问题之类的（参考点击打开链接，点击打开链接等），但是经过检查代码并参考如何在 Microsoft Visual C# .NET 中实现 Microsoft Excel 自动化和如何使用 256 个以上的字段或列的数据导入 Excel资料，发现是因为03版和07版的Excel最大列数为256，最大行数为65536，如果超出这一限制，必然导致0X800a03ec的错误，所以对原有代码进行了改造，基本实现了所需功能。

此外还可以采用13版Excel，13版的.xlsx文档貌似没有行数和列数的限制。

附主要功能代码：

	/// <summary>
        /// Creates new Excel files for input data.
        /// </summary>
        /// <param name="desc">a list that stores the title of a table</param>
        /// <param name="dgs"></param>
        /// <param name="idx"></param>
        /// <param name="directory">the directory used for store the created files</param>
        private void newExcel(IList<Metadata> desc, IEnumerable<Data> dgs, int idx, string directory)
        {
            Application xlapp = new Application();
            Workbook wbook = null;
            int shtn = (int)Math.Ceiling(desc.Count / 256.0);
            int row = 9, part = 1;
            double ring = 0;
            double temp = double.NaN;
            foreach (var data in dgs)
            {
                double[] parsedata = parser.Parse(data.Datagram);
                ring = parsedata[idx];
                if (temp != ring)
                {
                    if (wbook != null)
                    {
                        break;
                        newExcelCore(ref wbook, directory, temp, part);
                    }
                    wbook = xlapp.Workbooks.Add(Missing.Value);
                    if (wbook.Sheets.Count < shtn)
                        wbook.Sheets.Add(Missing.Value, Missing.Value, shtn - wbook.Sheets.Count, XlSheetType.xlWorksheet);
                    newExcelTitle(ref wbook, desc);
                    row = 9;
                    temp = ring;
                }
                fillExcelData(ref wbook, 1, data.Time, row, parsedata, 0);
                row++;
                if (row > 65536)
                {
                    newExcelCore(ref wbook, directory, temp, part);
                    wbook = xlapp.Workbooks.Add(Missing.Value);
                    if (wbook.Sheets.Count < shtn)
                        wbook.Sheets.Add(Missing.Value, Missing.Value, shtn - wbook.Sheets.Count, XlSheetType.xlWorksheet);
                    newExcelTitle(ref wbook, desc);
                    row = 9;
                    part++;
                }//行数超出65536时，保存当前workbook，并创建新的workbook，其中part变量用作文件第几部分的标识。
            }
            if (wbook != null)
            {
                newExcelCore(ref wbook, directory, temp, part);
            }
            xlapp.Quit();
        }

        private void newExcelCore(ref Workbook wbook, string directory, double temp, int part)
        {
            string filename = string.Format(@"{0}\{2}_{1}_{3}.xlsx", directory, DateTime.Now.ToFileTime(), temp, part);
            wbook.SaveAs(filename);
            wbook.Close();
        }

        private void newExcelTitle(ref Workbook wbook, IList<Metadata> desc)
        {
            newExcelTitleCore(ref wbook, 1, desc, 0);
            
        }

        private void newExcelTitleCore(ref Workbook wbook, int sidx, IList<Metadata> desc, int startidx)
        {
            Worksheet wsheet = wbook.Worksheets[sidx];
            wsheet.Name = string.Format("Partition_{0}", sidx);
            fillExcelTitleCore(ref wsheet);
            for (int i = 2; i < (desc.Count - startidx + 2); i++)
            {
                if (i > 256)
                {
                    newExcelTitleCore(ref wbook, ++sidx, desc, i - 2);
                    break;
                }//列数大于256时，将剩下的数据递归写入新的Excel表单中。
                Metadata md = desc[startidx + i - 2];
                wsheet.Cells[1, i] = md.Addr;
                wsheet.Cells[2, i] = md.DataLoc;
                wsheet.Cells[3, i] = md.ValueType;
                wsheet.Cells[4, i] = md.DataLen;
                wsheet.Cells[5, i] = md.BitLoc;
                wsheet.Cells[6, i] = md.Range;
                wsheet.Cells[7, i] = md.Unit;
                wsheet.Cells[8, i] = md.Desc;
            }
        }

        private void fillExcelTitleCore(ref Worksheet wsheet)
        {
            wsheet.Cells[1, 1] = "PLC Address";
            wsheet.Cells[2, 1] = "Data Location";
            wsheet.Cells[3, 1] = "Value Type";
            wsheet.Cells[4, 1] = "Data Length";
            wsheet.Cells[5, 1] = "Bit Location";
            wsheet.Cells[6, 1] = "Data Range";
            wsheet.Cells[7, 1] = "Unit";
            wsheet.Cells[8, 1] = "Time";
        }

        private void fillExcelData(ref Workbook wbook,int sidx, DateTime dt, int row, double[] parsedata, int idx)
        {
            Worksheet wsheet = wbook.Sheets[sidx];
            double[] temp = null;
            int length = parsedata.Length - idx;
            if (length > 255)
            {
                fillExcelData(ref wbook, ++sidx, dt, row, parsedata, idx + 255);
                temp = new double[255];
            }
            else
            {
                temp = new double[length];
            }
            wsheet.Cells[row, 1] = dt;
            Array.Copy(parsedata, idx, temp, 0, temp.Length);
            wsheet.get_Range("B" + row, get_ColumnStr((uint)(temp.Length + 1)) + row).Value2 = temp;
        }

另外需要添加引用及Microsoft.Office.Interop.Excel名称空间。