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名称空间。