C#实现数据导出Excel工作表
来源:互联网 发布:阿里云系统官网 编辑:程序博客网 时间:2024/05/07 07:47
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace SunflowerSaleBook.BLL
{
/// <summary>
/// 打印和导出中间处理类
/// </summary>
public class Printwithderivative
{
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">要导出的DataTable</param>
public static void ExportToExcel(System.Data.DataTable dt)
{
if (dt == null) return;
//实例化Excel全局(本地服务器)
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel");
return;
}
//选择文件保存位置
System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
saveDia.Filter = "Excel|*.xls";//文件格式
saveDia.Title = "导出为Excel文件";//标题
//确定保存
if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK
&& !string.Empty.Equals(saveDia.FileName))
{
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
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;//行总数
long rowRead = 0;
float percent = 0;
string fileName = saveDia.FileName;//文件名称
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.DefaultView.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
return;
}
//关闭导出进程,回收
workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
//强行释放最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
MessageBox.Show("导出成功!");
}
}
internal static void ExportToExcel()
{
throw new NotImplementedException();
}
}
}
- C#实现数据导出Excel工作表
- C#实现数据导出Excel工作表
- C#实现EXCEL数据的导入导出
- C# Excel数据导出
- C# 导出Excel数据
- c# 实现导出excel
- C#中ListView数据导出excel表
- C# 导出DataGridView数据到Excel表
- C#实现从EXCEL将数据导出到datagridview
- c# 实现将dataGridView中的数据导出到Excel
- C# 实现Excel导出 加载模板 导入数据到模板
- c#导出数据到Excel
- C#数据导出到Excel
- C#数据导出到Excel
- C#导出数据到excel
- C#导出Excel cvs数据
- C#导出Excel cvs数据
- C# 导出数据到Excel
- Python3程序开发指南(第三版)学习笔记
- 求开方
- What is PE ?
- 存在的文件写在目标文件
- 【html5】HTML5学习(一)
- C#实现数据导出Excel工作表
- android knowladge
- AWK简单统计apache/nginx日志IP数
- 嵌入式学习步骤
- poj 2299 Ultra-QuickSort(线段树,离散化求逆序数)
- Java中的MVC架构模型
- jdbc事务
- Javascript高级
- asp动态生成xml