业务QQ:1470938791 电话:(+86)15198288543 RSS

首页 - 正文

winform导出excel 生成图表

技术分享, 文章目录
21/10/13
本文关键字:

图表

1 引用

Microsoft.Office.Interop.Excel

2 代码块

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication3
{
//替代名称
using Excel = Microsoft.Office.Interop.Excel;//替代名称
using Missing = System.Reflection.Missing;
class ExcelManage
{
public System.Data.DataSet dataSet = new System.Data.DataSet();

public void InitalTable()
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(“序号”, typeof(int));
table.Columns.Add(“数据1”, typeof(int));
table.Columns.Add(“数据2”, typeof(int));
Random r = new Random();
for (int i = 0; i < 200; i++)
{
if (i == 6 || i == 16) continue;
table.Rows.Add(i + 1, r.Next(50), r.Next(60));
}

dataSet.Tables.Add(table);

//ExportExcel(dataSet);
}

public void ExportExcel(System.Data.DataSet ds)
{
//新建EXCEL应用
Excel.Application excelApp = new Excel.Application();
if (excelApp == null)
return;

//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excelApp.Visible = false;
//初始化工作簿
Excel.Workbooks workbooks = excelApp.Workbooks;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
//Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
Excel.Workbook workbook = workbooks.Add(true);

//Excel.Worksheet c_worksheet = workbook.Worksheets[1];
//int a = workbook.Worksheets.Count;

//workbook.Sheets.Add(Missing.Value, workbook.Sheets[1], ds.Tables.Count – 1, Missing.Value);

for (int index = 0; index < ds.Tables.Count; index++)
{
System.Data.DataTable dt = ds.Tables[index];
Excel.Worksheet worksheet = workbook.Worksheets.Add();
//Excel.Worksheet worksheet = workbook.Worksheets[index + 1];

//创建一个单元格
Excel.Range range;
int rowIndex = 1; //行的起始下标为 1
int colIndex = 1; //列的起始下标为 1
for (int i = 0; i < dt.Columns.Count; i++)
{
//设置第一行，即列名
worksheet.Cells[rowIndex, colIndex + i] = dt.Columns[i].ColumnName;
//获取第一行的每个单元格
range = worksheet.Cells[rowIndex, colIndex + i];
//字体加粗
range.Font.Bold = true;
//设置为黑色
range.Font.Color = 0;
//设置为宋体
range.Font.Name = “Arial”;
//设置字体大小
range.Font.Size = 12;
//水平居中
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//垂直居中
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

}
//跳过第一行，第一行写入了列名
rowIndex++;
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cells[rowIndex + i, colIndex + j] = dt.Rows[i][j].ToString();

range = worksheet.Cells[rowIndex + i, colIndex + j];
range.Interior.Color = System.Drawing.Color.Yellow;
range.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.Weight = Excel.XlBorderWeight.xlHairline;//边框常规粗细
}
}

//设置所有单元格列宽为自动列宽
worksheet.Cells.Columns.AutoFit();

#region 冻结行

worksheet.Select();
excelApp.ActiveWindow.SplitRow = 1;
excelApp.ActiveWindow.SplitColumn = 0;
excelApp.ActiveWindow.FreezePanes = true;

#endregion

#region 合并行
//Excel.Range mergeRange = worksheet.get_Range(“A25”, “B25”);
//mergeRange.Merge();
/////合并单元格之后，设置其中的文本
//mergeRange.Value = “mergeRange”;

#endregion

#region 绘制CHART图表
int num = dt.Rows.Count + 1;
Excel.Chart xlChart = workbook.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);
Excel.Range ranges1 = worksheet.Cells[1, 1];
Excel.Range ranges2 = worksheet.Cells[num, dt.Columns.Count];
Excel.Range chartRage = worksheet.get_Range(ranges1, ranges2);

xlChart.ChartWizard(chartRage, Excel.XlChartType.xlLine, Missing.Value,
Excel.XlRowCol.xlColumns, 1, 1,
Missing.Value, Missing.Value, “X值”, “Y值”, Missing.Value);

workbook.ActiveChart.HasTitle = true;
workbook.ActiveChart.ChartTitle.Text = “图表名称”;
workbook.ActiveChart.HasDataTable = false;
//给图表放在指定位置
//workbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, worksheet.Name);

xlChart.Name = “CC1″;
#endregion

//workbook.Worksheets.Item(

}

//是否提示，如果想删除某个sheet页，首先要将此项设为fasle。
excelApp.DisplayAlerts = false;

//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;

workbook.SaveCopyAs(@”C:\Users\Lenovo\Desktop\sample.xlsx”);
workbook.Close();
excelApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

workbook = null;
//worksheet = null;
//shapeSheet = null;
excelApp = null;
GC.Collect();
}
}
}

3， 最后由于此方法写入时非常耗时，我们可以采用先把数据转成字符串拷贝到剪贴板中，然后在复制到sheet表中，这样十万数据只要几秒钟
代码

public void ExportExcel()
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add(“序号”, typeof(int));
dt.Columns.Add(“数据1”, typeof(int));
dt.Columns.Add(“数据2”, typeof(int));
Random r = new Random();
for (int i = 0; i < 20000; i++)
{
if (i == 6 || i == 16) continue;
dt.Rows.Add(i + 1, r.Next(50), r.Next(60));
}

StringBuilder strbu = new StringBuilder();

//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
strbu.Append(dt.Columns[i].ColumnName.ToString() + “\t”);
}

//加入换行字符串
strbu.Append(Environment.NewLine);
//写入内容
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
strbu.Append(dt.Rows[i][j].ToString() + “\t”);
}
strbu.Append(Environment.NewLine);
}

System.Windows.Forms.Clipboard.SetText(strbu.ToString());

//新建EXCEL应用
Excel.Application excelApp = new Excel.Application();
if (excelApp == null)
return;

//设置为不可见，操作在后台执行，为 true 的话会打开 Excel
excelApp.Visible = false;
//初始化工作簿
Excel.Workbooks workbooks = excelApp.Workbooks;
//新增加一个工作簿，Add（）方法也可以直接传入参数 true
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
//同样是新增一个工作簿，但是会弹出保存对话框
//Excel.Workbook workbook = workbooks.Add(true);

//Excel.Worksheet worksheet = workbook.Worksheets[1];
Excel.Worksheet worksheet = workbook.Worksheets.Add();

//Excel.Range ranges1 = worksheet.Cells[1, 1];
//Excel.Range ranges2 = worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count];
//Excel.Range chartRage = worksheet.get_Range(ranges1, ranges2);
//Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[dt.Rows.Count+1, dt.Columns.Count]);
//chartRage.Copy(strbu.ToString());
//ranges1.Value = System.Windows.Forms.Clipboard.GetText();
//worksheet.Activate();
worksheet.Paste();
//worksheet.PasteSpecial(System.Windows.Forms.Clipboard.GetText(), false, false);

//新建一个 Excel 文件
string filePath = @”C:\Users\Lenovo\Desktop\” + DateTime.Now.ToString(“yyyy-MM-dd-HH-mm-ss”) + “.xlsx”;
//创建文件
FileStream file = new FileStream(filePath, FileMode.CreateNew);
//关闭释放流，不然没办法写入数据
file.Close();
file.Dispose();

//保存写入的数据，这里还没有保存到磁盘
workbook.Saved = true;
//保存到指定的路径
workbook.SaveCopyAs(filePath);
}

private void WriteDataToExcel
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
DataTable dataTable1 = this.GetTabel1();//获取表格2

Microsoft.Office.Interop.Excel.Application excelApp;
Microsoft.Office.Interop.Excel._Workbook workBook;
Microsoft.Office.Interop.Excel._Worksheet workSheet;
Microsoft.Office.Interop.Excel._Worksheet workSheet1;
object misValue = System.Reflection.Missing.Value;
workBook = excelApp.Workbooks.Add(misValue);//加载模型

workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.Sheets.get_Item(1);//第一个工作薄。

workSheet1 = (Microsoft.Office.Interop.Excel._Worksheet)workBook.Sheets.get_Item(2);

int rowIndex = 0;
int colIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
workSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();

}
}

rowIndex = 0;
colIndex = 0;
foreach (DataRow row in dataTable1.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dataTable1.Columns)
{
colIndex++;
workSheet1.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();

}
}

workSheet.Protect(“MyPassword”, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, true, Type.Missing, Type.Missing);

//保护工作表
workSheet1.Protect(“MyPassword”, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, true, Type.Missing, Type.Missing);

/**/

excelApp.Visible = false;

workBook.SaveAs(@”D:\outputFormDataBase1.xls”, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue, misValue, misValue, misValue, misValue);

dataTable = null;

workBook.Close(true, misValue, misValue);

excelApp.Quit();

PublicMethod.Kill(excelApp);//调用kill当前excel进程

}

出处：https://www.cnblogs.com/wangyonglai/p/8329094.html