C#读取和写入Excel2003的代码-优快云博客

本文介绍了一种使用C#进行Excel文件数据加载与保存的方法。具体实现包括：打开指定的Excel文件，逐行读取教师信息，并将这些信息保存到Teacher类的实例中；之后再将新的教师数据追加到原始的Excel文件中。此过程涉及异常处理以确保操作的健壮性。

Application excelApp = new ApplicationClass();

Excel.Workbook workbook = null;

Excel.Worksheet worksheet = null;

Excel.Range range = null;

private List<Teacher> LoadAccpBenetExcel(string sourceFileName)

{

List<Teacher> teachers = new List<Teacher>();

try

{

workbook = excelApp.Workbooks.Open(sourceFileName

, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing

, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

}

catch (Exception e)

{

MessageBox.Show(e.Message, "打开Excel工作簿失败");

}

worksheet = (Excel.Worksheet)workbook.Sheets[1];

//从第6行开始是要读取的数据，所以从第6行开始取数据，一直读取到空行为止

for (int i = 6; true; i++)

{

range = (Excel.Range)worksheet.Cells[i, 2];

if (range.Value2 == null)

　　　　　　{
                    break;
                }
                try
                {
                    Teacher model = new Teacher();
                    model.college = GetExcelRangeValue(worksheet, 3, 3);
                    model.teacherName = GetExcelRangeValue(worksheet, i, 2);
                    teachers.Add(model);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "从源Excel中获取数据失败"+i);
                    break;
                }
            }
            excelApp.Quit();
            return teachers;
        }

       private string GetExcelRangeValue(Excel.Worksheet worksheet, int RowIndex, int ColumnIndex)
        {
            Excel.Range range = (Excel.Range)worksheet.Cells[RowIndex, ColumnIndex];
            if (range.Value2 == null)
            {
                return "";
            }

　　　　　　 return range.Text.ToString();
            }
        }

        private void CreateAccpBenetExcel(List<Teacher> teachers, string sourceFileName)
        {
            try
            {
                workbook = excelApp.Workbooks.Open(sourceFileName
                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception e)
            {

      MessageBox.Show(e.Message, "打开Excel工作簿失败");
            }
            worksheet = (Excel.Worksheet)workbook.Sheets[1];
            //下面获取从第几行开始附加数据，避免覆盖已有的数据
            int BlankRowNumber;
            for(int i=7;true;i++)
            {
                range = (Excel.Range)worksheet.Cells[i, 3];
                if(range.Value2==null)
                {
                    BlankRowNumber=i;
                    break;
                }
            }
            foreach(Teacher teacher in teachers)

　　　　　{
                try
                {
                    range = (Excel.Range)worksheet.Cells[BlankRowNumber, 2];
                    range.Value2 = teacher.college;
                    range = (Excel.Range)worksheet.Cells[BlankRowNumber, 3];
                    range.Value2 = teacher.teacherName;
                    ++BlankRowNumber;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "从源Excel中获取数据失败" + --BlankRowNumber);
                }
            }
            workbook.Save();
            excelApp.Quit();
            GC.Collect();
        }