ExcelHelp 学习笔记二： C# 往Excel中写入数据

1.定义一个ExcelHelp静态文件，提供系统全体调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;

namespace ImportDataFromExcels
{
    public static class ExcelHelp
    {
        public static void WriteValueDataIntoExcel(string filePath, string sheetName, Dictionary<string, List<string>> AllDataDic, System.Data.DataTable result)
        {
            var excelApplication = new Microsoft.Office.Interop.Excel.Application();
            excelApplication.DisplayAlerts = false;

            try
            {
                Workbooks workBook = excelApplication.Workbooks;

                var objExcelWorkbook = workBook.Open(filePath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                var objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets[sheetName];
                objExcelWorkSheet.Select(Type.Missing);
                Worksheet objExcelWorkSheetTemp = (Worksheet)excelApplication.ActiveSheet;

　　　　　　　　　　//////以下部分是属于特殊的业务逻辑处理，在该处进行一些赋值操作
                var startRow = 18;
                for (var j = 2; j < result.Columns.Count; j++)
                {
                    var stationName = result.Rows[startRow][j].ToString().Trim();

                    if (AllDataDic.Keys.Contains(stationName))
                    {
                        var count = 2;
                        foreach (var item in AllDataDic[stationName])
                        {
                            objExcelWorkSheetTemp.Cells[startRow + count, j + 1] = item;
                            count++;
                        }
                    }
                }

　　　　　　　　　　/////将更新后的Excel内容进行保存操作
                objExcelWorkbook.Save();
            }
            finally
            {
                excelApplication.Quit();
            }
        }
    }
}

2.在需要的地方调用该方法获取Excel中的数据

ExcelHelp.WriteValueDataIntoExcel(filePath, "Sheet1", AllDataDic, result);

 

注意：因为本处为实际完成某个工作任务，故还未将该全局静态类ExcelHelp进行抽象化（把实际的业务逻辑处理暂时写在了此处），之后进行整理后再进行修改