Excel：导入导出原文02

1)      创建Excel写入简单数据，保存并打开全过程。

             

bool fileSaved = false;//是否存储成功的标志

SaveFileDialog saveDialog = new SaveFileDialog();

            saveDialog.DefaultExt = "xls";

            saveDialog.Filter = "Excel文件|*.xls";

            saveDialog.FileName = "Sheet1";

            saveDialog.ShowDialog();

            string saveFileName = saveDialog.FileName;

            if (saveFileName.IndexOf(":") < 0) return; //被点了取消

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

 

            if (xlApp == null)

            {

                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");

                return;

            }

 

            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

 

            #region  写内容

            //写入字段

            for (int i = 1; i <= 24; i++)

            {

                worksheet.Cells[1, i + 1] = i.ToString();

            }

 

//存储

            if (saveFileName != "")

            {

                try

                {

                    workbook.Saved = true;

                    workbook.SaveCopyAs(saveFileName);

                    fileSaved = true;

                }

                catch (Exception ex)

                {

                    fileSaved = false;

                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);

                }

            }

            else

            {

                fileSaved = false;

            }

            xlApp.Quit();

            GC.Collect();//强行销毁

            if (fileSaved && System.IO.File.Exists(saveFileName))

                System.Diagnostics.Process.Start(saveFileName); //打开EXCEL

 2）各种操作代码

//列宽自适应。

worksheet.Columns.EntireColumn.AutoFit();

//获取单元格某区域

            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]);

            Microsoft.Office.Interop.Excel.Range rng3 = worksheet.get_Range("C6", "C7");

 

//插入空白行

            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)worksheet.Rows[1, Type.Missing];

            range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,

                Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);  

//删除行

       //刪除表的指定行數操作
        range7.Select();
        range7.EntireRow.Delete(Excel.XlDirection.xlUp);       

//颜色

      range.Interior.ColorIndex = 6; //设置Range的背景色

  range1.Font.Color = ColorTranslator.ToOle(Color.White)

 

注意，Excel颜色都是用int表示，c#颜色转换为Excel颜色的方式为ColorTranslator.ToOle(Color.White)

//单元格相关

range.Merge(0);                           //合并

range.Borders.LineStyle=1;     //设置单元格边框的粗细
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());     //给单元格加边框

range.ColumnWidth=15;                     //设置单元格的宽度

range.NumberFormatLocal = "@";     //设置单元格格式为文本

 

//文字相关

         range.Value2 = "Hello";                     //设置值内容

          range1.Font.Size = 18;             //字体

          range1.Font.Bold = true;

         range1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中

        range1.Font.Name="黑体";      // 设置字体的种类

       range.Font.Underline=true;     //设置字体是否有下划线

       Range.WrapText=true;             //文本自动换行

//求和操作
         Excel.Range range2 = excelkk.get_Range("B25", Type.Missing);
         range2.Formula = "=SUM(B2:B24)";
         range2.Calculate();

    //行复制

         Excel.Range range4 = excelkk.get_Range("A2", "B25");
         Excel.Range range5 = excelkk.get_Range("E3", "F25");
         range4.Copy(range5);

    //停用警告信息
        excelkk.DisplayAlerts = false;