public void ExportRectangle(List <string> txDataColumnc, List <string> tyProductColumnc, List <int> tyNumberColumnc, Excel.Application objExcel = null, Excel.Workbook objWorkbook = null, Excel.Worksheet objsheet = null)
{
List <string> txDataColumn = txDataColumnc;
List <string> tyDataOk = tyProductColumnc;
List <int> tyDataNo = tyNumberColumnc;
try
{
//设置属性标签
objsheet.Range["A:A"].ColumnWidth = 20; //设置宽度
objsheet.Range["A:A"].NumberFormatLocal = "@";
objsheet.Range["B:B"].ColumnWidth = 20; //设置宽度
objsheet.Range["C:C"].ColumnWidth = 20; //设置宽度
#region 管理人员
int col = 1;
objExcel.Cells[1, col] = "日期";
objExcel.Cells[1, col + 1] = "产品名称";
objExcel.Cells[1, col + 2] = "数量";
int row = 2; //row和 i得对应关系是row = i+2 ; i = row -2
int temp = row;
int cell = 0;
for (int i = 0; i < txDataColumn.Count; i++)
{
objExcel.Cells[row, col] = txDataColumn[i].ToString();
row++;
}
for (int i = 0; i < txDataColumn.Count; i++)
{
if (i == 0)
{
//objExcel.Cells[row, col] = txDataColumn[i];
}
else
{
if (txDataColumn[i] != txDataColumn[i - 1])
{
cell = i - 1 + 2;
Excel.Range rangeChange = objsheet.Range["A" + temp, "A" + cell];
rangeChange.Value2 = Type.Missing;
rangeChange.Merge(Type.Missing);
rangeChange.Value2 = txDataColumn[i - 1].ToString();
temp = i + 2;
}
}
row++;
}
row = 2;
for (int i = 0; i < tyDataOk.Count; i++)
{
objExcel.Cells[row, col + 1] = tyDataOk[i];
row++;
}
row = 2;
for (int i = 0; i < tyDataNo.Count; i++)
{
objExcel.Cells[row, col + 2] = tyDataNo[i];
row++;
}
#endregion
int num = txDataColumn.Count + 1;
Excel.Range rangeAll = objsheet.Range["A1", "C" + num];
rangeAll.Borders.Color = 0;
rangeAll.Borders.Weight = 2;
rangeAll.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rangeAll.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//设置属性标签
objsheet.Range["A:A"].ColumnWidth = 20; //设置宽度
objsheet.Range["B:B"].ColumnWidth = 20; //设置宽度
objsheet.Range["C:C"].ColumnWidth = 20; //设置宽度
//柱状图
Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[1, 1], (Excel.Range)objsheet.Cells[1 + txDataColumn.Count, 3]);
//1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
xlChart2.ChartWizard(cellRange,
Excel.XlChartType.xlColumnClustered, //2-图表类型
Type.Missing, //内置自动套用格式的选项编号。 可为从 1 到 10 的数字，其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
Excel.XlRowCol.xlColumns, //在图表上将列或行用作数据系列的方式
2, //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认；可以不写，默认的就很难看
1, //--这个2代表数据源的x轴由1个参数确认；可以不写，默认的就很难看
true, //图表是否有图例
"每日总量统计", //以下都是标题
Type.Missing,
Type.Missing,
"");
xlChart2.ChartStyle = 201;
xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd); // 设置图表上图表元素。 为可读/写属性。
xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); //将图表移动到新位置。
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。
objsheet.Shapes.Item("Chart 1").Top = 100; //调图表的位置上边距
objsheet.Shapes.Item("Chart 1").Left = 400;
objsheet.Shapes.Item("Chart 1").Width = txDataColumn.Count * 30; //调图表的宽度
objsheet.Shapes.Item("Chart 1").Height = 300; //调图表的高度
//保存
objsheet.Name = "每日占用";
objsheet.Tab.Color = 3394611;
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
/// <summary>
/// 柱形图
/// </summary>
/// <param name="txDataColumnc"></param>
/// <param name="tyProductColumnc"></param>
/// <param name="tyNumberColumnc"></param>
public void ExportRectangle(List <string> txDataColumnc, List <string> tyProductColumnc, List <int> tyNumberColumnc, string DateBegin, string DateEnd)
{
Open = true;
intExcelTempIndex++;
List <string> txDataColumn = txDataColumnc;
List <string> tyDataOk = tyProductColumnc;
List <int> tyDataNo = tyNumberColumnc;
Excel.Application objExcel = null;//创建一个excel的实例
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
string fileNameString = "";
try
{
//申明对象
objExcel = new Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置属性标签
objsheet.Range["A:A"].ColumnWidth = 20; //设置宽度
objsheet.Range["A:A"].NumberFormatLocal = "@";
objsheet.Range["B:B"].ColumnWidth = 20; //设置宽度
objsheet.Range["C:C"].ColumnWidth = 20; //设置宽度
#region 管理人员
int col = 1;
objExcel.Cells[1, col] = "日期";
objExcel.Cells[1, col + 1] = "产品名称";
objExcel.Cells[1, col + 2] = "数量";
int row = 2; //row和 i得对应关系是row = i+2 ; i = row -2
int temp = row;
int cell = 0;
for (int i = 0; i < txDataColumn.Count; i++)
{
objExcel.Cells[row, col] = txDataColumn[i].ToString();
row++;
}
for (int i = 0; i < txDataColumn.Count; i++)
{
if (i == 0)
{
//objExcel.Cells[row, col] = txDataColumn[i];
}
else
{
if (txDataColumn[i] != txDataColumn[i - 1])
{
cell = i - 1 + 2;
Excel.Range rangeChange = objsheet.Range["A" + temp, "A" + cell];
rangeChange.Value2 = Type.Missing;
rangeChange.Merge(Type.Missing);
rangeChange.Value2 = txDataColumn[i - 1].ToString();
temp = i + 2;
}
}
row++;
}
row = 2;
for (int i = 0; i < tyDataOk.Count; i++)
{
objExcel.Cells[row, col + 1] = tyDataOk[i];
row++;
}
row = 2;
for (int i = 0; i < tyDataNo.Count; i++)
{
objExcel.Cells[row, col + 2] = tyDataNo[i];
row++;
}
#endregion
int num = txDataColumn.Count + 1;
Excel.Range rangeAll = objsheet.Range["A1", "C" + num];
rangeAll.Borders.Color = 0;
rangeAll.Borders.Weight = 2;
rangeAll.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rangeAll.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//柱状图
Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[1, 1], (Excel.Range)objsheet.Cells[1 + txDataColumn.Count, 3]);
//1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
xlChart2.ChartWizard(cellRange,
Excel.XlChartType.xlColumnClustered, //2-图表类型
Type.Missing, //内置自动套用格式的选项编号。 可为从 1 到 10 的数字，其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
Excel.XlRowCol.xlColumns, //在图表上将列或行用作数据系列的方式
2, //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认；可以不写，默认的就很难看
1, //--这个2代表数据源的x轴由1个参数确认；可以不写，默认的就很难看
true, //图表是否有图例
"每日总量统计(" + DateBegin + "-" + DateEnd + ")", //以下都是标题
Type.Missing,
Type.Missing,
"");
xlChart2.ChartStyle = 201;
xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd); // 设置图表上图表元素。 为可读/写属性。
xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); //将图表移动到新位置。
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。
objsheet.Shapes.Item("Chart 1").Top = 100; //调图表的位置上边距
objsheet.Shapes.Item("Chart 1").Left = 400;
objsheet.Shapes.Item("Chart 1").Width = objsheet.Shapes.Item("Chart 1").Width = txDataColumn.Count * 100 > 400 ? txDataColumn.Count * 30 : 400; //调图表的宽度
objsheet.Shapes.Item("Chart 1").Height = 300; //调图表的高度
//保存
objsheet.Name = "每日总量统计" + intExcelTempIndex.ToString();
objsheet.Tab.Color = Excel.XlThemeColor.xlThemeColorLight1;
fileNameString = Application.StartupPath + "\\" + objsheet.Name + ".xlsx";
objExcel.DisplayAlerts = false;
objExcel.AlertBeforeOverwriting = false;
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show("该文件已打开，请关闭后重试！", "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Open = false;
return;
}
finally
{
//确认进程关闭
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
// 安全回收进程
var aa = System.GC.GetGeneration(objExcel);
objsheet = null;
objWorkbook = null;
objExcel = null;
if (Open == true)
{
Process.Start(fileNameString);
}
}
}
public void Export()
{
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xlsx ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx ";
//默然路径是系统当前路径
dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//合格率
objExcel.Cells[1, 1] = "开始时间";
objExcel.Cells[1, 3] = "结束时间";
objExcel.Cells[2, 1] = "不合格";
objExcel.Cells[3, 1] = "合格";
objExcel.Cells[2, 2] = tyDataPie[0];
objExcel.Cells[3, 2] = tyDataPie[1];
//饼图
Excel.Range oResizeRange;
Excel.Chart xlChart = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
xlChart.ChartType = Excel.XlChartType.xlPie;//设置图形
xlChart.SetSourceData(objsheet.get_Range("A2", "B3"), Excel.XlRowCol.xlColumns);
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, "合格率");
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
oResizeRange = (Excel.Range)objsheet.Rows.get_Item(7, Missing.Value);
objsheet.Shapes.Item("Chart 1").Top = 70; //调图表的位置上边距
objsheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
objsheet.Shapes.Item("Chart 1").Width = 200; //调图表的宽度
objsheet.Shapes.Item("Chart 1").Height = 150; //调图表的高度
#region 管理人员
int col = 6;
objExcel.Cells[2, col] = "用户名";
objExcel.Cells[2, col + 1] = "合格";
objExcel.Cells[2, col + 2] = "不合格";
int row = 3;
for (int i = 0; i < txDataColumn.Count; i++)
{
objExcel.Cells[row, col] = txDataColumn[i];
row++;
}
row = 3;
for (int i = 0; i < tyDataOk.Count; i++)
{
objExcel.Cells[row, col + 1] = tyDataOk[i];
row++;
}
row = 3;
for (int i = 0; i < tyDataNo.Count; i++)
{
objExcel.Cells[row, col + 2] = tyDataNo[i];
row++;
}
#endregion
//柱状图
Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[2, 6], (Excel.Range)objsheet.Cells[3 + txDataColumn.Count - 1, 8]);
//1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
xlChart2.ChartWizard(cellRange,
Excel.XlChartType.xlColumnStacked, Type.Missing,
Excel.XlRowCol.xlColumns, 1, 1, true,
"管理人员校准情况", "用户名", "校准个数",
"");
xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
Excel.Range oResizeRange1 = (Excel.Range)objsheet.Rows.get_Item(1);
Excel.Range oResizeRange2 = (Excel.Range)objsheet.Columns.get_Item(10);
objsheet.Shapes.Item("Chart 2").Top = (float)oResizeRange1.Top; //调图表的位置上边距--1行的高度
objsheet.Shapes.Item("Chart 2").Left = (float)(double)oResizeRange2.Left; //调图表的位置左边距--10列的宽度
objsheet.Shapes.Item("Chart 2").Width = 300; //调图表的宽度
objsheet.Shapes.Item("Chart 2").Height = 200; //调图表的高度
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
objsheet = null;
objWorkbook = null;
objExcel = null;
}
}
private void button1_Click(object sender, EventArgs e)
{
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xlsx ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLSX)|*.xlsx ";
//默然路径是系统当前路径
dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
Excel.Application objExcel = new Excel.Application();//创建一个excel的实例
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
//Excel.Sheets objsheets = objExcel.Workbooks.Item[1].Worksheets;
//objsheets.Item[1] = "";
//Excel.Worksheet ws = (Excel.Worksheet)objExcel.Worksheets.get_Item(1);
//ws.Name = "狐狸!";
// Excel.Sheets objsheets = objExcel.Sheets;
//var ss = objsheets.Item[1];
// var aa = (Excel.Worksheet)ss;
// aa.Name = "dasdasdasd";
//objsheets["Sheet1"].Nasdasda = "11";
//objsheet = (Excel.Worksheet)objsheets.get_Item(objsheets.Count);
//worksheet.Name = "sadas ";
////objsheets.get_Item(1
//objWorkbooks = objExcel.Workbooks;
////oBooks.Open(sTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//objWorkbook = objWorkbooks.get_Item(1);
//objsheets = objWorkbook.Worksheets;
//objsheet = (Excel.Worksheet)objsheets.get_Item(1);
////命名该sheet
//objsheet.Name = "Sheet1";
try
{
//申明对象
objExcel = new Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//合格率
objExcel.Cells[1, 1] = "客户";
objExcel.Cells[1, 2] = "数量";
objExcel.Cells[1, 3] = "占比";
objExcel.Cells[2, 1] = "不合格";
objExcel.Cells[3, 1] = "合格";
objExcel.Cells[2, 2] = tyDataPie[0];
objExcel.Cells[3, 2] = tyDataPie[1];
objExcel.Cells[2, 3] = @"=B2 / B4";
objExcel.Cells[3, 3] = @"=B3 / B4";
//求和
Excel.Range rangesummary73 = objsheet.Range["B4"];//--ActiveCell = rangesummary110
rangesummary73.Formula = "=SUM(R[-2]C:R[-1]C)";
rangesummary73.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)";
// Range("A2:A3,C2:C3").Select
//Range("C2").Activate
//ActiveSheet.Shapes.AddChart2(251, xlPie).Select
//ActiveChart.SetSourceData Source:= Range("Sheet1!$A$2:$A$3,Sheet1!$C$2:$C$3")
//ActiveChart.SetElement(msoElementDataLabelBestFit)
// Excel.Range rangesummary5 = objsheet.Range["A2:A3", "C2:C3"];
//设置百分比格式
Excel.Range rangesummary4 = objsheet.Range["C2", "C3"];
//Range("C2:C3").Select;
rangesummary4.NumberFormatLocal = "0.00%";
//饼图
//新建一个饼图
Excel.Chart xlChart = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
xlChart.ChartType = Excel.XlChartType.xlPie; //设置图形
xlChart.SetSourceData(objsheet.get_Range("A1:A3, C1:C3"), Excel.XlRowCol.xlColumns); //两种方法都可以
// xlChart.SetSourceData(objsheet.Range["A1:A3", "C1:C3"], Excel.XlRowCol.xlColumns);
//加border和居中设置
Excel.Range rangesummary110 = objsheet.Range["A1", "C3"];
rangesummary110.Borders.Color = 0;
rangesummary110.Borders.Weight = 2;
rangesummary110.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//设置属性标签
xlChart.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd); //数据标签
xlChart.SetElement(MsoChartElementType.msoElementLegendBottom); //设为底部显示
xlChart.SetElement(MsoChartElementType.msoElementChartTitleAboveChart); //设置标题
objsheet.Range["F:F"].ColumnWidth = 20.5; //设置宽度
xlChart.ChartTitle.Text = "客户占比(2020.3.23-3.28)";
//objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, "合格率");//xlLocationAutomatic :Excel 控制图表位置。
objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name); // xlLocationAsObject,将图表嵌入到现有工作表中。
// oResizeRange = (Excel.Range)objsheet.Rows.get_Item(7, Missing.Value);
objsheet.Shapes.Item("Chart 1").Top = 150; //调图表的位置上边距
objsheet.Shapes.Item("Chart 1").Left = 10;
objsheet.Shapes.Item("Chart 1").Width = 200; //调图表的宽度
objsheet.Shapes.Item("Chart 1").Height = 250; //调图表的高度
///////////////////////////////////////////////////////////////
#region 管理人员
int col = 6;
objExcel.Cells[2, col] = "日期";
objExcel.Cells[2, col + 1] = "产品名称";
objExcel.Cells[2, col + 2] = "数量";
int row = 3;
for (int i = 0; i < txDataColumn.Count; i++)
{
objExcel.Cells[row, col] = txDataColumn[i];
row++;
}
row = 3;
for (int i = 0; i < tyDataOk.Count; i++)
{
objExcel.Cells[row, col + 1] = tyDataOk[i];
row++;
}
row = 3;
for (int i = 0; i < tyDataNo.Count; i++)
{
objExcel.Cells[row, col + 2] = tyDataNo[i];
row++;
}
#endregion
//假定要合并excel文件中第2行的1~3列，并且显示黑色边框7a686964616fe4b893e5b19e31333264656666
//objExcel.ActiveSheet.Columns[7] = 5;
Microsoft.Office.Interop.Excel.Range rangesummary1 = objsheet.Range["F3", "F5"];
Microsoft.Office.Interop.Excel.Range rangesummary2 = objsheet.Range["F6", "F8"];
Microsoft.Office.Interop.Excel.Range rangesummary3 = objsheet.Range["F9", "F12"];
Excel.Range rangesummary120 = objsheet.Range["F2", "H12"];
rangesummary120.Borders.Color = 0;
rangesummary120.Borders.Weight = 2;
rangesummary120.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rangesummary120.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
rangesummary1.Value2 = Type.Missing;
rangesummary1.Merge(Type.Missing);
rangesummary1.Value2 = "2020-03-23";
rangesummary2.Value2 = Type.Missing;
rangesummary2.Merge(Type.Missing);
rangesummary2.Value2 = "2020-03-24";
rangesummary3.Value2 = Type.Missing;
rangesummary3.Merge(Type.Missing);
rangesummary3.Value2 = "2020-03-25";
//柱状图
Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[2, 6], (Excel.Range)objsheet.Cells[3 + txDataColumn.Count - 1, 8]);
//1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
xlChart2.ChartWizard(cellRange,
Excel.XlChartType.xlColumnClustered, //2-图表类型
Type.Missing, //内置自动套用格式的选项编号。 可为从 1 到 10 的数字，其取值依赖于库的类型。 如果省略此参数, 则 Excel 根据库的类型和数据源选择默认值。
Excel.XlRowCol.xlColumns, //在图表上将列或行用作数据系列的方式
2, //第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行--这个2代表数据源的x轴由两个参数确认；可以不写，默认的就很难看
1, //--这个2代表数据源的x轴由1个参数确认；可以不写，默认的就很难看
true, //图表是否有图例
"每日总量统计", //以下都是标题
Type.Missing,
Type.Missing,
"");
xlChart2.SetElement(MsoChartElementType.msoElementDataLabelOutSideEnd);
xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
Excel.Range oResizeRange1 = (Excel.Range)objsheet.Rows.get_Item(1);
Excel.Range oResizeRange2 = (Excel.Range)objsheet.Columns.get_Item(10);
objsheet.Shapes.Item("Chart 2").Top = (float)oResizeRange1.Top; //调图表的位置上边距--1行的高度
objsheet.Shapes.Item("Chart 2").Left = (float)(double)oResizeRange2.Left; //调图表的位置左边距--10列的宽度
objsheet.Shapes.Item("Chart 2").Width = 300; //调图表的宽度
objsheet.Shapes.Item("Chart 2").Height = 200; //调图表的高度
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
////关闭Excel应用
//if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
//if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
//if (objExcel != null) objExcel.Quit();
//objsheet = null;
//objWorkbook = null;
//objExcel = null;
objsheet.Name = "牙模盒使用记录";
objsheet.Tab.Color = Excel.XlThemeColor.xlThemeColorLight1;
ClosePro(fileNameString, objExcel, objWorkbook);
System.Diagnostics.Process.Start(fileNameString);
}
}
