- 导入Microsoft.Office.Interop.Excel.dll
- using System.IO;
- #region 创建Excel
- /// <summary>
- /// 创建Excel
- /// </summary>
- /// <param name="fileName">文件名称</param>
- /// <param name="sheetName">sheet名称</param>
- private void CreateExcel(string fileName,string sheetName)
- {
- Microsoft.Office.Interop.Excel.ApplicationClass my = new Microsoft.Office.Interop.Excel.ApplicationClass();
- my.Visible = true;
- object objMissing = System.Reflection.Missing.Value;
- //打开工作簿
- Microsoft.Office.Interop.Excel.Workbook mybook = (Microsoft.Office.Interop.Excel.Workbook)my.Workbooks.Add(1); // 1表示只建一个表
- //mybook.Worksheets.Add(objMissing,objMissing,1,objMissing);//添加sheet
- if (sheetName == "")
- {
- sheetName = "steet1";
- }
- else
- {
- ((Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1]).Name = sheetName;//将sheet1的名称改为zhu
- }
- Microsoft.Office.Interop.Excel.Worksheet mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
- ((Microsoft.Office.Interop.Excel.Range)mysheet.Cells[2, 3]).EntireRow.Insert(0,0); //添加行
- //保存
- mybook.SaveAs("d://" + fileName + ".xls", objMissing, objMissing, objMissing, //Excel.XlSaveAsAccessMode.xlShared
- objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
- objMissing, objMissing, objMissing,
- objMissing, objMissing);
- mybook = null;
- my.Quit();
- my = null;
- }
- #endregion
- //------------------------------------------------------------------
- #region 导入Excel
- /// <summary>
- /// 导入Excel
- /// </summary>
- /// <param name="dv">数据源</param>
- /// <param name="path">路径</param>
- /// <param name="title1">标题</param>
- /// <param name="heji">合计</param>
- public void inputExcel(DataSet dv, string path, string title1, string heji)
- {
- try
- {
- FileInfo fi = new FileInfo(path);
- fi.Delete();
- }
- catch
- {
- }
- Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
- object oMissing = System.Reflection.Missing.Value;
- Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Add(oMissing);
- int rowIndex = 3;
- int colIndex = 0;
- excel.Cells[1, 1] = title1;
- foreach (DataColumn dc in dv.Tables[0].Columns)
- {
- colIndex++;
- excel.Cells[rowIndex, colIndex] = dc.ColumnName;
- ((Microsoft.Office.Interop.Excel.Range)excel.Cells[rowIndex, colIndex]).ColumnWidth = 18.8;
- }
- foreach (DataRow row in dv.Tables[0].Rows)
- {
- rowIndex++;
- colIndex = 0;
- foreach (DataColumn dc in dv.Tables[0].Columns)
- {
- colIndex++;
- excel.Cells[rowIndex, colIndex] = row[dc.ColumnName].ToString();
- ((Microsoft.Office.Interop.Excel.Range)excel.Cells[rowIndex, colIndex]).ColumnWidth = 18.8;
- }
- }
- excel.Cells[rowIndex + 1, colIndex] = heji;
- Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
- Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
- Microsoft.Office.Interop.Excel.Range range;
- range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, colIndex]);
- range.MergeCells = true;//合并
- range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
- range.Font.Bold = true;
- range.Font.Size = 14;
- book.Saved = true;
- excel.UserControl = false;
- worksheet.SaveAs(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
- excel.Quit();
- oMissing = null;
- sheets = null;
- worksheet = null;
- book = null;
- excel = null;
- GC.Collect();
- }
- #endregion
- //---------------------------------------------------------------
- #region 绑定数据
- private DataSet bangding()
- {
- using (SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=;database=pubs"))
- {
- conn.Open();
- string sql = "select job_id as 编号,job_desc as 排序,min_lvl as 最小, max_lvl as 最大 from jobs";
- SqlDataAdapter dar = new SqlDataAdapter(sql, conn);
- DataSet ds = new DataSet();
- dar.Fill(ds);
- this.GridView1.DataSource = ds;
- this.GridView1.DataBind();
- return ds;
- }
- }
- #endregion
- //------------------------------------------------------------------
- #region 获取合计
- private int num()
- {
- int num = 0;
- for (int i = 0; i < bangding().Tables[0].Rows.Count; i++)
- {
- num += Convert.ToInt32(bangding().Tables[0].Rows[i][3].ToString());
- }
- return num;
- }
- #endregion
- //-------------------------------------------------------------------
c# 代码创建Excel文件 并且 将数据导出Excel
最新推荐文章于 2023-02-09 21:54:46 发布