C#上传Excel文件报错，在最后一列添加异常列的方法

使用了一个三方插件：Aspose.Cells用来操作Excel

在里面修改了写了使用了一些方法一些方法

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Web;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;
using System.Drawing;
using Aspose.Cells;

namespace ISS.DF.Utilities.iDoc
{
    public class ExcelHelper
    {
        #region 静态函数及变量

        private Workbook workbook;
        private Worksheet worksheet;
        private Dictionary<string, Worksheet> worksheets;
        private Worksheet currentWorksheet; //指定要操作的Excel工作薄，默认为第一个工作薄

        private string fullName;            //导出/导出Excel文件名称(全路径)
        private int maxRow;                 //包含数据或样式的最大行索引
        private int maxColumn;              //包含数据或样式的最大列索引
        public string FullName
        {
            get
            {
                if (string.IsNullOrEmpty(fullName))
                    return ConfigurationManager.AppSettings["ExcelFullName"].ToString();

                return fullName;
            }
            set
            { 
                fullName = value;
            }
        }
        public int MaxRow
        {
            get { return maxRow; }
            set { maxRow = value; }
        }
        public int MaxColumn
        {
            get { return maxColumn; }
            set { maxColumn = value; }
        }


        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="isExport">是否从Excel导出数据</param>
        /// <param name="excelFullName">Excel文件名称(全路径)(为空则使用配置文件名称)</param>
        public ExcelHelper(bool isExport, string excelFullName)
        {
            this.FullName = excelFullName;

            if (isExport)
            {
                workbook = new Workbook(this.FullName);
                worksheets = new Dictionary<string, Worksheet>();
                foreach (Worksheet worksheet in workbook.Worksheets)
                {
                    worksheets.Add(worksheet.Name, worksheet);
                }
            }
            else
            {
                workbook = new Workbook();
            }
            currentWorksheet = workbook.Worksheets[0];

        }
        #endregion

        #region 导入函数之为当前sheet添加列表字段
        /// <summary>
        /// 在当前sheet列表最后添加列表字段及对应表字段数据
        /// </summary>
        /// <param name="cl">列表字段及对应表字段数据的字典</param>
        public void AddFieldAndDataAtLastField(Dictionary<string, ArrayList> cl)
        {
            int i = 0;
            MaxColumn = currentWorksheet.Cells.MaxColumn + 1;

            Cell cell;
            ArrayList alst;

            foreach (string key in cl.Keys)
            {
                MaxColumn += i;
                cell = currentWorksheet.Cells[0, MaxColumn];
                cell.PutValue(key);
                
                alst = new ArrayList();
                alst = cl[key];

                for (int j = 0; j < alst.Count; j++)
                {
                    cell = currentWorksheet.Cells[j + 1, MaxColumn];
                    cell.PutValue(alst[j].ToString());
                }
                i++;
            }
        }
        /// <summary>
        /// 在当前sheet列表指定行与列索引处添加表字段或表字段数据
        /// </summary>
        /// <param name="data">要添加的数据</param>
        /// <param name="row">当前sheet列表指定行索引</param>
        /// <param name="column">当前sheet列表指定列索引</param>
        public void AddDataAtField(string data, int row, int column)
        {
            Cell cell = currentWorksheet.Cells[row, column];
            cell.PutValue(data);
        }
        //最后一列指定行添加错误列
        public void AddDataAtMaxColumn(string data, int row)
        {
            MaxColumn = currentWorksheet.Cells.MaxColumn + 1;
            row = row - 1;
            Cell cell = currentWorksheet.Cells[row, MaxColumn];
            cell.PutValue(data);
        }
        //最后一列指定行添加错误列
        public void AddDataAtMaxColumn1(string data, int row)
        {
            MaxColumn = currentWorksheet.Cells.MaxColumn;
            row = row - 1;
            Cell cell = currentWorksheet.Cells[row, MaxColumn];
            cell.PutValue(data);
        }

        #endregion

        #region 导入函数之导入数据到Excel工作薄

        /// <summary>
        /// 将ArrayList数组数据，从"A1"列开始导入到Excel第一个工作薄
        /// </summary>
        /// <param name="list"></param>
        public void ExcuteImport(ArrayList list)
        {
            if (list.Count <= 0)
            {
                throw new Exception("指定的导入数据源为空!");
            }

            currentWorksheet.Cells.ImportArrayList(list, 0, 0, true);
        }
        /// <summary>
        /// 将包含列名的DataTable数据表内容，从"A1"列开始导入到Excel第一个工作薄
        /// </summary>
        /// <param name="table">DataTable数据表</param>
        public void ExcuteImport(DataTable table)
        {
            if (table == null || table.Rows.Count <= 0)
            {
                throw new Exception("指定的导入数据源为空!");
            }

            currentWorksheet.Cells.ImportDataTable(table, true, "A1");
            currentWorksheet.AutoFitColumns();
        }
        /// <summary>
        /// 将包含列名的dataReader数据内容导入到Excel第一个工作薄
        /// </summary>
        /// <param name="dataReader">dataReader数据内容</param>
        public void ExcuteImport(SqlDataReader dataReader)
        {
            if (!dataReader.HasRows)
            {
                throw new Exception("指定的导入数据源为空!");
            }

            currentWorksheet.Cells.ImportDataReader(dataReader, true, 0, 0, false);
            currentWorksheet.AutoFitColumns();
        }
        /// <summary>
        /// 将包含列名的dataReader数据内容，从"A1"列开始导入到Excel第一个工作薄
        /// </summary>
        /// <param name="dataReader"></param>
        public void ExcuteImport(OleDbDataReader dataReader)
        {
            if (!dataReader.HasRows)
            {
                throw new Exception("指定的导入数据源为空!");
            }

            #region 设置表头样式--注释
            //int styleIndex = workbook.Styles.Add();
            //Aspose.Cells.Style style = workbook.Styles[styleIndex];
            //style.Font.IsBold = true;
            //style.Font.Name = "Verdana";
            style.ForegroundColor = Color.White;
            //style.Pattern = BackgroundType.Solid;
            //style.Font.Size = 10;

            //Range range = worksheet.Cells.CreateRange(0, 0, 1, 4);
            //range.SetStyle(style);
            #endregion

            currentWorksheet.Cells.ImportDataReader(dataReader, true, 0, 0, false);
            currentWorksheet.AutoFitColumns();
        }
        /// <summary>
        /// 保存导入的Excel到磁盘
        /// </summary>
        public void SaveImport()
        {
            if (fullName.EndsWith(".xls", System.StringComparison.CurrentCultureIgnoreCase))
            {
                workbook.Save(fullName, new XlsSaveOptions(SaveFormat.Excel97To2003));
            }
            else
            {
                workbook.Save(fullName, new XlsSaveOptions(SaveFormat.Xlsx));
            }
        }

        /// <summary>
        /// 保存导入的Excel到磁盘
        /// </summary>
        public void SaveImportxlsm(string Address)
        {
            if (fullName.EndsWith(".xlsx", System.StringComparison.CurrentCultureIgnoreCase))
            {
                workbook.Save(Address, new XlsSaveOptions(SaveFormat.Excel97To2003));
            }
            else
            {
                workbook.Save(Address, new XlsSaveOptions(SaveFormat.Xlsx));
            }
        }
        #endregion

        #region 导出函数之设置指定的sheet为当前操作sheet

        /// <summary>
        /// 导出函数之设置指定名称的sheet为当前操作sheet
        /// </summary>
        /// <param name="worksheetName">指定的sheet名称</param>
        private void SetCurrentWorksheet(string worksheetName)
        {
            if (worksheets.ContainsKey(worksheetName))
            {
                currentWorksheet = worksheets[worksheetName];
            }
            else
            {
                throw new Exception("当前工作表中不存在指定的工作薄\"" + worksheetName + "\"");
            }
        }

        /// <summary>
        /// 导出函数之设置指定索引的sheet为当前操作sheet
        /// </summary>
        /// <param name="worksheetIndex">指定的sheet索引</param>
        private void SetCurrentWorksheet(byte worksheetIndex)
        {
            if (worksheetIndex <= worksheets.Count)
            {
                currentWorksheet = GetWorkSheetByIndex(worksheetIndex);
            }
            else
            {
                throw new Exception("工作薄索引范围超过了工作表数量");
            }
        }

        #endregion

        #region 导出函数之根据索引或名称得到sheet

        /// <summary>
        /// 导出函数之根据索引得到sheet
        /// </summary>
        /// <param name="index">指定的sheet索引</param>
        /// <returns>返回指定的Worksheet(如果sheet索引超出范围，则返回null)</returns>
        private Worksheet GetWorkSheetByIndex(byte index)
        {
            byte i = 0;
            Worksheet worksheet = null;
            foreach (string name in worksheets.Keys)
            {
                if (index == i)
                {
                    worksheet = worksheets[name];
                }
                i++;
            }
            return worksheet;
        }

        /// <summary>
        /// 导出函数之根据sheet名称得到sheet
        /// </summary>
        /// <param name="sheetName">指定的sheet名称</param>
        /// <returns>返回指定的Worksheet(如果不存在指定的sheet名称，则返回null)</returns>
        private Worksheet GetWorkSheetByName(string sheetName)
        {
            if (worksheets.ContainsKey(sheetName))
            {
                return worksheets[sheetName];
            }
            else
            {
                return null;
            }

        }

        #endregion

        #region 导出函数之验证sheet指定位置是否有数据

        /// <summary>
        /// 导出函数之判断指定行是否有数据（以连续50列没有数据为标准）
        /// </summary>
        /// <param name="row">行数，以Excel行序号1为起始数值</param>
        /// <returns>指定行是否有数据</returns>
        public bool IsRowHasValue(int row)
        {
            bool hasValue = false;
            for (int i = 0; i < 50; ++i)
            {
                if (currentWorksheet.Cells[row - 1, i].Value != null && currentWorksheet.Cells[row - 1, i].Value.ToString() != "")
                {
                    hasValue = true;
                    break;
                }
            }
            return hasValue;
        }
        /// <summary>
        /// 导出函数之判断指定行与列是否有数据
        /// </summary>
        /// <param name="row">行数，以Excel行序号1为起始数值</param>
        /// <param name="column">列数，以Excel列序号1为起始数值</param>
        /// <returns>指定行是否有数据</returns>
        public bool IsRowHasValue(int row,int column)
        {
            bool hasValue = false;
            for (int i = 0; i < column; ++i)
            {
                if (currentWorksheet.Cells[row - 1, i].Value != null && currentWorksheet.Cells[row - 1, i].Value.ToString() != "")
                {
                    hasValue = true;
                    break;
                }
            }
            return hasValue;
        }
        /// <summary>
        /// 导出函数之判断指定位置是否有数据
        /// </summary>
        /// <param name="position">工作薄指定位置的单元格</param>
        /// <returns>工作薄指定位置的单元格是否有数据</returns>
        public bool IsRowHasValue(string position)
        {
            bool hasValue = false;
            if (GetCellValue(position) != "")
            {
                hasValue = true;
            }
            return hasValue;
        }

        #endregion

        #region 导出函数之获取sheet中指定cell位置的数据

        /// <summary>
        /// 导出函数之获取指定sheet名称与指定cell位置的数据
        /// </summary>
        /// <param name="worksheetName">指定sheet名称</param>
        /// <param name="cellName">指定cell位置名称</param>
        /// <returns>指定sheet名称与指定cell位置的数据(无则返回空字符)</returns>
        public string GetCellValue(string worksheetName, string cellName)
        {
            return worksheets[worksheetName].Cells[cellName].Value != null ? worksheets[worksheetName].Cells[cellName].Value.ToString() : "";
        }
        /// <summary>
        /// 导出函数之获取指定sheet索引与指定cell位置的数据
        /// </summary>
        /// <param name="worksheetIndex">指定sheet索引</param>
        /// <param name="cellName">指定cell位置名称</param>
        /// <returns>指定sheet索引与指定cell位置的数据(无则返回空字符)</returns>
        public string GetCellValue(byte worksheetIndex, string cellName)
        {
            return GetWorkSheetByIndex(worksheetIndex).Cells[cellName].Value != null ? GetWorkSheetByIndex(worksheetIndex).Cells[cellName].Value.ToString() : "";
        }
        /// <summary>
        /// 导出函数之获取当前sheet指定cell位置的数据
        /// </summary>
        /// <param name="cellName">指定cell位置名称</param>
        /// <returns>当前sheet指定cell位置的数据(无则返回空字符)</returns>
        public string GetCellValue(string cellName)
        {
            return currentWorksheet.Cells[cellName].Value != null ? currentWorksheet.Cells[cellName].Value.ToString() : "";
        }
        /// <summary>
        /// 导出函数之根据行列索引,获取当前sheet指定位置的数据
        /// </summary>
        /// <param name="row">行索引值，从0开始</param>
        /// <param name="column">列索引值，从0开始</param>
        /// <returns>获取当前sheet指定位置的数据(无则返回空字符)</returns>
        public string GetCellValue(int row, int column)
        {
            return currentWorksheet.Cells[row, column].Value != null ? currentWorksheet.Cells[row, column].Value.ToString() : "";
        }

        #endregion

        #region 导出函数之从Excel工作薄导出数据

        /// <summary>
        /// 将指定工作表的第一个工作薄，从第一行与第一列索引处开始，将数据导出到DataTable
        /// </summary>
        /// <returns>导出DataTable数据表</returns>
        public DataTable ExportDataTable()
        {
            return ExportDataTable(0);
        }
        /// <summary>
        /// 将指定工作薄，从第一行与第一列索引处开始，将数据导出到DataTable
        /// </summary>
        /// <param name="worksheetIndex">指定工作薄索引值</param>
        /// <returns>导出DataTable数据表</returns>
        public DataTable ExportDataTable(byte worksheetIndex)
        {
            if (worksheetIndex != 0)
            {
                SetCurrentWorksheet(worksheetIndex);
            }

            DataTable dataTable = new DataTable();
            try
            {
                dataTable = currentWorksheet.Cells.ExportDataTable(0, 0, currentWorksheet.Cells.MaxRow + 1, currentWorksheet.Cells.MaxColumn + 1);
            }
            catch (Exception ex)
            {
                throw new Exception("从指定的导出数据文件工作薄导出数据时发生错误，错误信息为：" + ex.ToString());
            }
            return dataTable;
        }
        public DataTable ExportDataTable(byte worksheetIndex,int firstRow,int firstColumn)
        {
            if (worksheetIndex != 0)
            {
                SetCurrentWorksheet(worksheetIndex);
            }

            DataTable dataTable = new DataTable();
            try
            {
                dataTable = currentWorksheet.Cells.ExportDataTable(firstRow, firstColumn, currentWorksheet.Cells.MaxRow + 1, currentWorksheet.Cells.MaxColumn + 1);


            }
            catch (Exception ex)
            {
                throw new Exception("从指定的导出数据文件工作薄导出数据时发生错误，错误信息为：" + ex.ToString());
            }
            return dataTable;
        }

        /// <summary>
        /// 重构经过ExportDataTable()导出的数据表，将数据列表中被转成数据的字段再转化为字段，并填充数据
        /// </summary>
        /// <param name="dt">经过ExportDataTable()导出的数据表</param>
        /// <returns>将数据列表中被转成数据的字段再转化为字段，并填充数据后的数据表</returns>
        public DataTable TransformDataTable(DataTable dt)
        {
            int sequence = 0;
            string columnName = null;
            string tempColumnName = null;
            ArrayList alst = new ArrayList();
            DataTable dataTable = new DataTable();

            #region 将数据列表中被转成数据的字段再转化为字段

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                columnName = dt.Rows[0][i].ToString();

                if (!alst.Contains(columnName))
                {
                    alst.Add(columnName);
                }
                else
                {
                    for (int j = alst.Count; j >= 1; j--)
                    {
                        tempColumnName = alst[j-1].ToString();

                        if (tempColumnName.IndexOf(columnName) >= 0)
                        {
                            tempColumnName = tempColumnName.Remove(0, columnName.Length);
                            if (int.TryParse(tempColumnName, out sequence))
                            {
                                columnName += (sequence + 1).ToString();
                            }
                            else
                            {
                                columnName += "1";
                            }
                            alst.Add(columnName);
                        }
                    }
                }

                DataColumn column = new DataColumn();
                column.DataType = typeof(string);
                column.AllowDBNull = true;
                column.Caption = columnName;
                column.ColumnName = columnName;
                column.DefaultValue = "";

                dataTable.Columns.Add(column); 
            }
            #endregion

            #region 为转化后的新数据表添加数据
            
            DataRow dr = null;
            for (int i = 1; i < dt.Rows.Count; i++)
            {
                dr = dataTable.NewRow();

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    dr[j] = dt.Rows[i][j];
                }
                dataTable.Rows.Add(dr);
            }
            #endregion

            return dataTable;
        }
        #endregion


        #region 待扩展与封装区域

        /// <summary>
        /// 导出函数之判断某位置的单元格是否为合并单元格
        /// </summary>
        /// <param name="row">从0开始</param>
        /// <param name="column">从0开始</param>
        /// <returns></returns>
        public bool IsMerged(int row, int column)
        {
            return currentWorksheet.Cells[row, column].IsMerged;
        }

        /// <summary>
        /// 导入函数之设置样式
        /// </summary>
        /// <param name="isFontBold">是否加粗</param>
        /// <param name="fontSize">字体大小</param>
        /// <returns>返回Style</returns>
        public Style SetStyle(bool isFontBold, int fontSize)
        {
            int styleIndex = workbook.Styles.Add();
            Style style = workbook.Styles[styleIndex];
            style.Font.IsBold = isFontBold; //true
            style.Font.Name = "Verdana";
            //style.ForegroundColor = Color.White;
            style.Pattern = BackgroundType.Solid;
            style.Font.Size = fontSize;     //10

            return style;
        }

        /// <summary>
        /// 导入函数之创建cell区域
        /// </summary>
        /// <param name="firstRow"></param>
        /// <param name="firstColumn"></param>
        /// <param name="totalRows"></param>
        /// <param name="totalColumns"></param>
        /// <returns></returns>
        public Range CreateRange(int firstRow,int firstColumn,int totalRows,int totalColumns)
        {
            Range range = currentWorksheet.Cells.CreateRange(firstRow, firstColumn, totalRows, totalColumns);
            return range;

        }

        /// <summary>
        /// 导入函数之设置cell区域样式
        /// </summary>
        /// <param name="range"></param>
        /// <param name="style"></param>
        public void SetRangeStyle(Range range, Style style)
        {
            range.SetStyle(style);
        }

        #endregion
    }

    
}

#region 插入错误列
    public string ExcelPath
    {
        get { return this.ViewState["ErrorsExcelPath"] as string; }
        set { this.ViewState["ErrorsExcelPath"] = value; }
    }
    protected bool HasError
    {
        get { return this.ViewState["HasError"] == null ? false : (bool)this.ViewState["HasError"]; }
        set { this.ViewState["HasError"] = value; }
    }
    public class ExcelAddColumn
    {
        public string ArrayList = "23|a";
        public string excelAddress = "C:\\Users\\issuser\\Desktop\\MTK POA.xlsx";
        public string Address = "C:\\Users\\issuser\\Desktop\\MTK POA1.xlsx";
    }
    public void AddErrorFormation(ExcelAddColumn AddExcelerror)
    {
        ArrayList newlist = new System.Collections.ArrayList();
        newlist.Add(AddExcelerror.ArrayList);
        //上传位置
        DataTable table;
        ExcelHelper execlHelper = new ExcelHelper(true, AddExcelerror.excelAddress);
        try
        {
            table = execlHelper.ExportDataTable(0);
            table = execlHelper.TransformDataTable(table);
        }
        catch (Exception ex)
        {
            //this.ShowClientMessageBox(this.GetLocalResourceObject("PleaseUploadRealTemplateStr").ToString());
            return;
        }
        try
        {
            //添加异常列
            execlHelper.AddDataAtMaxColumn("异常",1);
            //将错误添加到异常列的对应行
            for (int i = 0; i < newlist.Count; i++)
            {
                execlHelper.AddDataAtMaxColumn1(newlist[i].ToString().Split('|')[1], Convert.ToInt32(newlist[i].ToString().Split('|')[0]));
            }
            //保存格式.xlsx
            execlHelper.SaveImportxlsm(AddExcelerror.Address);
            //this.ShowClientMessageBox(this.GetLocalResourceObject("ExcelDataHasErrorsStr").ToString());
        }
        catch (Exception ex)
        {
            //日志
            BRSys_ErrLog.AddLog(ex.Message);
        }
    }
    #endregion

方法的参数用一个类来代替，类里面的数据是测试用的，一般情况从类里面传出数据来供方法使用