Question

如何使用C#打开excel并将数据写入第i行第j列单元格，请高手指教！

Answer 1

        /// <summary>
        /// 导出数据到Excel中
        /// </summary>
        /// <param name="dt">DataTable数据源</param>
        /// <returns></returns>
        public void ExportToExcel(System.Data.DataTable dt)
        {
            if (dt == null) return;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                XtraMessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
            saveDia.Filter = "Excel|*.xls";
            saveDia.Title = "导出为Excel文件";
            if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK && !string.Empty.Equals(saveDia.FileName))
            {
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
                Microsoft.Office.Interop.Excel.Range range = null;
                long totalCount = dt.Rows.Count;
                long rowRead = 0;
                float percent = 0;
                string fileName = saveDia.FileName;

                //写入标题 
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                    //range.Interior.ColorIndex = 15;//背景颜色 
                    range.Font.Bold = true;//粗体 
                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中 
                    //加边框 
                    range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                    //range.ColumnWidth = 4.63;//设置列宽 
                    //range.EntireColumn.AutoFit();//自动调整列宽 
                    //r1.EntireRow.AutoFit();//自动调整行高 
                }

                //写入内容 
                for (int r = 0; r < dt.DefaultView.Count; r++)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
                        range.Font.Size = 9;//字体大小 
                        //加边框 
                        range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                        range.EntireColumn.AutoFit();//自动调整列宽 
                    }
                    rowRead++;
                    percent = ((float)(100 * rowRead)) / totalCount;
                    System.Windows.Forms.Application.DoEvents();
                }

                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                if (dt.Columns.Count > 1)
                {
                    range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                }

                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(fileName);
                }
                catch (Exception ex)
                {
                    XtraMessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                workbooks.Close();
                if (xlApp != null)
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    int generation = System.GC.GetGeneration(xlApp);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                    System.GC.Collect(generation);
                }
                GC.Collect();//强行销毁 

                #region 强行杀死最近打开的Excel进程
                System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                System.DateTime startTime = new DateTime();
                int m, killId = 0;
                for (m = 0; m < excelProc.Length; m++)
                {
                    if (startTime < excelProc[m].StartTime)
                    {
                        startTime = excelProc[m].StartTime;
                        killId = m;
                    }
                }
                if (excelProc[killId].HasExited == false)
                {
                    excelProc[killId].Kill();
                }
                #endregion
                XtraMessageBox.Show("导出成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
        }

希望对你有所帮助

Answer 2

一楼的方法看起来有点复杂，借助免费版Spire.XLS很容易实现数据导入，示例代码如下

using System.Data;
using Spire.Xls;

namespace WriteDataToExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //创建一个Workbook对象
            Workbook workbook = new Workbook();

            //加载一个现有的Excel文档
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\input.xlsx");

            //获取第一张工作表
            Worksheet sheet = workbook.Worksheets[0];

            //定义一个DataTable，写入数据（也可以直接从数据库获取DataTable)
            DataTable datatable = new DataTable();
            datatable.Columns.Add("名字");
            datatable.Columns.Add("年龄");
            datatable.Columns.Add("性别");
            datatable.Rows.Add(new string[] { "张三", "22", "女" });
            datatable.Rows.Add(new string[] { "李四", "25", "男" });
            datatable.Rows.Add(new string[] { "王五", "23", "男" });

            //从第五行第四列开始插入数据，true代表数据包含列名
            sheet.InsertDataTable(datatable, true, 5, 4);

            //保存文件
            workbook.SaveToFile("ExportDataToExcel.xlsx",ExcelVersion.Version2013);
        }
    }
}

结果文档：