C# 对Excel操作

1.引用 Microsoft.Office.Interop.Excel.dll 类库

//导出内容，创建Excel表格
public class ExcelExport
    {
        public static string CurFileName = "";
        public static bool MarkExport(List<MarkInfoDTO> markInfoDTOs)
        {
            try
            {
                int rowNumber = markInfoDTOs.Count;
                if (rowNumber == 0)
                {
                    return false;
                }

                string originalPicFolderPath = GlobalData.g_strDesignRootPath + @"\截图\原始截图\";
                string modifiedPicFolderPath = GlobalData.g_strDesignRootPath + @"\截图\修改截图\";

                string path = CurFileName + "-" + DateTime.Now.ToString("MM月dd日HH时mm分"); 
                SaveFileDialog save = new SaveFileDialog();
                save.FileName = path;
                save.DefaultExt = "xlsx";
                save.Filter = "Excel File(*.xlsx)|*.xlsx";
                if (save.ShowDialog() != DialogResult.OK)
                {
                    return false;
                }

                path = save.FileName;
                DealWithSpecialCharacter(ref path);

                List<string> columnNames = new List<string>();
                columnNames.Add("序号");
                columnNames.Add("批注内容");
                columnNames.Add("错误类型");
                columnNames.Add("原始截图");
                columnNames.Add("修改截图");
                columnNames.Add("提出人");
                columnNames.Add("提出时间");
                int columnNumber = columnNames.Count;

                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                worksheet.Name = "批注信息";

                int colIndex = 0;
                foreach (string colName in columnNames)
                {
                    colIndex++;
                    worksheet.Cells[1, colIndex] = colName;
                }
                int colNum = colIndex;

                int rowIndex = 1;
                foreach (MarkInfoDTO itemDTO in markInfoDTOs)
                {
                    rowIndex++;
                    worksheet.Cells[rowIndex, 1] = (rowIndex-1).ToString();
                    worksheet.Cells[rowIndex, 2] = itemDTO.AuditContent;
                    string mistakeType = string.Empty;
                    GlobalData.MistakeLevels.TryGetValue(itemDTO.AuditMistakeType,out mistakeType);
                    worksheet.Cells[rowIndex, 3] = mistakeType;
                    worksheet.Cells[rowIndex, 6] = itemDTO.ReviseUserName;
                    worksheet.Cells[rowIndex, 7] = itemDTO.UpdateTime.ToString("yyyy/MM/dd HH:mm:ss");
                }
                int rowNum = markInfoDTOs.Count+1;

                Range curRange = worksheet.get_Range(worksheet.Cells[1,1], worksheet.Cells[1, colNum]);
                curRange.Rows.RowHeight = 30;
                curRange.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                curRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                curRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]);
                curRange.ColumnWidth = 5;
                curRange = worksheet.get_Range(worksheet.Cells[1, 2], worksheet.Cells[1, 2]);
                curRange.ColumnWidth = 50;
                curRange = worksheet.get_Range(worksheet.Cells[1, 3], worksheet.Cells[1, 3]);
                curRange.ColumnWidth = 20;
                curRange = worksheet.get_Range(worksheet.Cells[1, 4], worksheet.Cells[1, 4]);
                curRange.ColumnWidth = 20;
                curRange = worksheet.get_Range(worksheet.Cells[1, 5], worksheet.Cells[1, 5]);
                curRange.ColumnWidth = 20;
                curRange = worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[1, 6]);
                curRange.ColumnWidth = 10;
                curRange = worksheet.get_Range(worksheet.Cells[1, 7], worksheet.Cells[1, 7]);
                curRange.ColumnWidth = 15;

                curRange = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowNum, colNum]);
                curRange.Rows.RowHeight = 100;
                curRange.HorizontalAlignment = XlVAlign.xlVAlignCenter;
                curRange.VerticalAlignment = XlVAlign.xlVAlignCenter;

                curRange = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[rowNum, 2]);
                curRange.HorizontalAlignment = XlVAlign.xlVAlignJustify;
                curRange.VerticalAlignment = XlVAlign.xlVAlignCenter;

                rowIndex = 2;
                foreach (MarkInfoDTO itemDTO in markInfoDTOs)
                {
                    string originalPicPath = originalPicFolderPath + itemDTO.ID + ".bmp";
                    if (File.Exists(originalPicPath))
                    {
                        InsertPicture(worksheet.get_Range(worksheet.Cells[rowIndex, 4], worksheet.Cells[rowIndex, 4]), worksheet, originalPicPath);
                    }
                    string modifiedPicPath = modifiedPicFolderPath + itemDTO.ID + ".bmp";
                    if (File.Exists(modifiedPicPath))
                    {
                        InsertPicture(worksheet.get_Range(worksheet.Cells[rowIndex, 5], worksheet.Cells[rowIndex, 5]), worksheet, modifiedPicPath);
                    }
                    rowIndex++;
                }

                worksheet.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                excel.Quit();

                MessageBox.Show("导出成功！", "信息", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return true;
            }
            catch (System.Exception ex)
            {
                throw new Exception("导出失败！原因："+ ex.Message);
            }
        }
		//插入图片到Excel中
        public static void InsertPicture(Range rng,Worksheet sheet, string PicturePath)
        {
            rng.Select();
            float PicLeft, PicTop, PicWidth, PicHeight;
            PicTop = Convert.ToSingle(rng.Top);
            PicLeft = Convert.ToSingle(rng.Left);
            PicHeight = Convert.ToSingle(rng.Height);
            PicWidth = Convert.ToSingle(rng.Width);
            try
            {
                Pictures pics = (Pictures)sheet.Pictures(Type.Missing);
                Picture pic = pics.Insert(PicturePath, Type.Missing);
                pic.Left = (double)rng.Left;
                pic.Top = (double)rng.Top;
                pic.Width = (double)rng.Width;
                pic.Height = (double)rng.Height;
            }
            catch (Exception ex)
            {
               throw new Exception("插入图片失败！原因：" + ex.Message);
            }
        }


        private static void DealWithSpecialCharacter(ref string str)
        {
            //现只处理[]
            str = str.Replace("[", "【");
            str = str.Replace("]", "】");
        }

    }