将List数据写到EXCEL

最新推荐文章于 2024-01-30 16:12:15 发布

原创最新推荐文章于 2024-01-30 16:12:15 发布 · 1.4k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#list #excel #null #object #string

本文介绍了一种使用C#进行Excel文件导出的方法。通过实例演示了如何创建Excel文件并填充数据，包括设置单元格内容、样式以及条件格式等。此外还提供了释放资源和关闭Excel进程的方式。

/// <summary> /// 导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExport_Click(object sender, EventArgs e) { SetDateValue(); if (datas == null) { datas = Query(); } //创建Excel文件 Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook book; Microsoft.Office.Interop.Excel.Worksheet sheet; excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象 book = excel.Application.Workbooks.Add(true); //引用Excel工作簿 if (book == null) return; excel.Visible = true; //使Excel可视 sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets["Sheet1"]; // 选中当前新建Sheet（一般为Sheet1） /*-----填写表格-----*/ sheet.Cells[1, 1] = "能种"; sheet.Cells[1, 2] = "采集点名称"; sheet.Cells[1, 3] = sDate.ToString("yyyy-MM-dd"); sheet.Cells[1, 4] = eDate.ToString("yyyy-MM-dd"); sheet.Cells[1, 5] = "校验差值"; sheet.Cells[1, 6] = "备注"; for (int i = 0; i < datas.Count; i++) { sheet.Cells[i + 2, 1] = datas[i].EnergyType; sheet.Cells[i + 2, 2] = datas[i].TagName; sheet.Cells[i + 2, 3] = datas[i].StartData; sheet.Cells[i + 2, 4] = datas[i].EndData; sheet.Cells[i + 2, 5] = datas[i].DiffValue; //((Microsoft.Office.Interop.Excel.Range) sheet.Cells[i + 2, 5]).Interior.ColorIndex = 3; if (datas[i].DiffValue < 0) //差值小于0标红 { sheet.get_Range(sheet.Cells[i + 2, 1], sheet.Cells[i + 2, 5]).Interior.ColorIndex = 3; } } sheet.Columns.AutoFit(); sheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //有两种插入方法 //逐格插入数据 //sheet.Cells[1, 1] = "Vale"; // 左上角第一格的坐标是[1, 1] //sheet.Cells[irow, icolumn] = value; //按块插入数据 //object[,] objVal = new object[Width, Height]; //string[] strs = new string[] { "生产日期：2009-1-1", "出丝柜重量：3310", "班次：甲", "班别：早" }; // 设置数据块 //Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(sheet.Cells[1, 1], //sheet.Cells[1, 1 + 1]); //range.Value2 = strs; ////清理内存和恢复环境 //System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(book); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); //while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excel) > 0) //range = null; //sheet = null; //book = null; //excel = null; //GC.Collect(); //System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI; //需要关闭EXCEL进程 //excel = null; }