C#EXCEL操作大全

网页导出EXCEL，调用该方法 前台会下载EXCEL

/// <summary>

    /// Excle导出数据

    /// </summary>

    /// <typeparam name="T">类对象<\typeparam>

    /// <param name="list">对象数据</param>

    /// <param name="column">类字段，字段对应列名</param>

    /// <param name="filename">excel表名</param>

    public void OutExcel<T>(List<T> list, Dictionary<string, string> column, string filename)
    {

        if (list == null || list.Count == 0 || column == null || column.Count == 0)
        {

            return;

        }

        StringWriter sw = new StringWriter();

        //-------------------------------表头读取开始------------------------------------------------

        string title = string.Empty;

        foreach (KeyValuePair<string, string> kvp in column)
        {

            title += kvp.Value + "\t";

        }

        title = title.Substring(0, title.LastIndexOf("\t"));

        sw.WriteLine(title);

        //-------------------------------表头读取结束--------------------------------------------------------

        //--------------------------------数据读取start----------------------------------------------------------------------------------

        Type objType = typeof(T);

        BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识 

        PropertyInfo[] propInfoArr = objType.GetProperties(bf); //获取映射列表

        foreach (T model in list)
        {

            System.Text.StringBuilder data = new System.Text.StringBuilder();

            foreach (string key in column.Keys)
            {

                foreach (PropertyInfo propInfo in propInfoArr)
                {

                    if (key == propInfo.Name)//判断头相对应的字段 
                    {

                        PropertyInfo modelProperty = model.GetType().GetProperty(propInfo.Name);

                        if (modelProperty != null)
                        {

                            object objResult = modelProperty.GetValue(model, null);//获取值                        

                            data.Append(((objResult == null) ? string.Empty : objResult) + "\t");

                        }

                    }

                }



            }

            var temp = data.ToString();

            temp = temp.Substring(0, temp.LastIndexOf("\t"));

            sw.WriteLine(temp);

        }

        //------------------------------------------end----------------------------------------------------------------------------------

        sw.Close();//读取数据结束

        //-----------------------------------输出excel-------------------------------------------------------------

        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");

        HttpContext.Current.Response.ContentType = "application/ms-excel";

        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GBK");

        HttpContext.Current.Response.Write(sw.ToString());

        HttpContext.Current.Response.End();

        //-------------------------------------------------------------------------------------------------------------               

    }

调用例子

  List<easy_CX> es = a.ToObjectFromJson<List<easy_CX>>();
           Dictionary<string, string> mydict = new Dictionary<string, string>();
           OutExcel(es,mydict,"all_down");

DataGridview中的数据导出EXCEL

 public bool ExportDataGridview(DataGridView gridView, bool isShowExcle)
        {
            if (gridView.Rows.Count == 0)
            {
                return false;
            }
            //创建Excel对象
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Application.Workbooks.Add(true);

            //生成字段名称
            for (int i = 0; i < gridView.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
            }
            //填充数据
            for (int i = 0; i < gridView.RowCount - 1; i++)   //循环行
            {
                for (int j = 0; j < gridView.ColumnCount; j++) //循环列
                {
                    if (gridView[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = "'" + gridView.Rows[i].Cells[j].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = gridView.Rows[i].Cells[j].Value.ToString();
                    }
                }
            } 
            excel.Visible = false;
            excel.DisplayAlerts = false;
            excel.AlertBeforeOverwriting = false;
            excel.Save("c:\\321.xls");
            excel.Quit();
            return true;
        }

把DataTable导出为EXCEL

        public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
        {

            if (tmpDataTable == null)

                return;

            int rowNum = tmpDataTable.Rows.Count;

            int columnNum = tmpDataTable.Columns.Count;

            int rowIndex = 1;

            int columnIndex = 0;



            Excel.Application xlApp = new Excel.Application();

            xlApp.DefaultFilePath = "";

            xlApp.DisplayAlerts = true;

            xlApp.SheetsInNewWorkbook = 1;

            Excel.Workbook xlBook = xlApp.Workbooks.Add(true);



            //将DataTable的列名导入Excel表第一行

            foreach (DataColumn dc in tmpDataTable.Columns)
            {

                columnIndex++;

                xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;

            }



            //将DataTable中的数据导入Excel中

            for (int i = 0; i < rowNum; i++)
            {

                rowIndex++;

                columnIndex = 0;

                for (int j = 0; j < columnNum; j++)
                {

                    columnIndex++;

                    xlApp.Cells[rowIndex, columnIndex] = "'" + tmpDataTable.Rows[i][j].ToString();

                }

            }

            //xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));

            xlBook.SaveCopyAs(strFileName);
            xlBook.Close(false);

        }

将EXCEL转换为DataTable  之后存入数据库常用于批量导入

 // <summary>
        /// 将Excel文件导出至DataTable(第一行作为表头)
        /// </summary>
        /// <param name="ExcelFilePath">Excel文件路径</param>
        /// <param name="TableName">数据表名，如果数据表名错误，默认为第一个数据表名</param>
        public static DataTable InputFromExcel(string ExcelFilePath, string TableName)
        {
            if (!File.Exists(ExcelFilePath))
            {
                throw new Exception("Excel文件不存在！");
            }

            //如果数据表名不存在，则数据表名为Excel文件的第一个数据表
            ArrayList TableList = new ArrayList();
            TableList = GetExcelTables(ExcelFilePath);

            if (TableName.IndexOf(TableName) < 0)
            {
                TableName = TableList[0].ToString().Trim();
            }

            DataTable table = new DataTable();
            OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");
            OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

            try
            {
                if (dbcon.State == ConnectionState.Closed)
                {
                    dbcon.Open();
                }
                adapter.Fill(table);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (dbcon.State == ConnectionState.Open)
                {
                    dbcon.Close();
                }
            }
            return table;
        }
        public static ArrayList GetExcelTables(string ExcelFileName)
        {
            DataTable dt = new DataTable();
            ArrayList TablesList = new ArrayList();
            if (File.Exists(ExcelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    }
                    catch (Exception exp)
                    {
                        throw exp;
                    }

                    //获取数据表个数
                    int tablecount = dt.Rows.Count;
                    for (int i = 0; i < tablecount; i++)
                    {
                        string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                        if (TablesList.IndexOf(tablename) < 0)
                        {
                            TablesList.Add(tablename);
                        }
                    }
                }
            }
            return TablesList;
        }