using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Core;
using Aspose.Cells;
using System.IO;
using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 生成EXCEL
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("Product", Type.GetType("System.String"));
dt.Columns.Add("Version", Type.GetType("System.String"));
dt.Columns.Add("Description", Type.GetType("System.String"));
DataRow newRow;
newRow = dt.NewRow();
newRow["Product"] = "大话西游";
newRow["Version"] = "2.0";
newRow["Description"] = "我很喜欢";
dt.Rows.Add(newRow);
newRow = dt.NewRow();
newRow["Product"] = "大话西游";
newRow["Version"] = "2.0";
newRow["Description"] = "我很喜欢";
dt.Rows.Add(newRow);
newRow = dt.NewRow();
newRow["Product"] = "大话西游";
newRow["Version"] = "4.0";
newRow["Description"] = "我很喜欢";
dt.Rows.Add(newRow);
newRow = dt.NewRow();
newRow["Product"] = "梦幻西游";
newRow["Version"] = "3.0";
newRow["Description"] = "比大话更幼稚";
dt.Rows.Add(newRow);
newRow = dt.NewRow();
newRow["Product"] = "梦幻西游";
newRow["Version"] = "3.1";
newRow["Description"] = "比大话更幼稚";
dt.Rows.Add(newRow);
newRow = dt.NewRow();
newRow["Product"] = "梦幻西游";
newRow["Version"] = "3.1";
newRow["Description"] = "比大话更幼稚";
dt.Rows.Add(newRow);
newRow = dt.NewRow();
newRow["Product"] = "坤灵网游";
newRow["Version"] = "1.1";
newRow["Description"] = "比大话更幼稚";
dt.Rows.Add(newRow);
newRow = dt.NewRow();
newRow["Product"] = "坤灵网游";
newRow["Version"] = "1.1";
newRow["Description"] = "比大话更幼稚";
dt.Rows.Add(newRow);
newRow = dt.NewRow();
newRow["Product"] = "坤灵网游";
newRow["Version"] = "1.1";
newRow["Description"] = "比大话更幼稚";
dt.Rows.Add(newRow);
string path = System.IO.Path.Combine(Application.StartupPath, "test01.xls");
if (File.Exists(path))
{
File.Delete(path);
}
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//先设置标题项目：如大修件，日常备件等
cells.Merge(1, 0, 1, 4);
cells[1, 0].PutValue("XX月份供应商绩效考核报表");
cells[1, 0].SetStyle(set_title_style(workbook,TextAlignmentType.Center));
cells.Merge(2, 0, 1,3);
cells[2, 0].PutValue("供应商代码： E001");
cells.Merge(3, 0, 1, 3);
cells[3, 0].PutValue("供应商名称: 艾睿电子（深圳）有限公司");
cells.Merge(4, 0, 1, 3);
cells[4, 0].PutValue("供应商性质: 代理商");
cells.Merge(5, 0, 1, 2);
cells[5, 0].PutValue("总分: ");
cells.Merge(5, 2, 1, 2);
cells[5, 2].PutValue("考核等级: ");
string strCell1 = "";
string strCell2 = "";
for (int i = 0; i < dt.Rows.Count;i++)
{
if (strCell1 != dt.Rows[i][0].ToString())
{
strCell1 = dt.Rows[i][0].ToString();
int number = 0;
for (int j = i; j < dt.Rows.Count; j++)
{
if (strCell1 == dt.Rows[j][0].ToString())
{
number++;
}
else
{
break;
}
}
cells.Merge(i + 6, 0, number, 1);
cells[i + 6, 0].PutValue(dt.Rows[i][0]);
}
if (strCell2 != dt.Rows[i][1].ToString())
{
strCell2 = dt.Rows[i][1].ToString();
int number = 0;
for (int j = i; j < dt.Rows.Count; j++)
{
if (strCell2 == dt.Rows[j][1].ToString())
{
number++;
}
else
{
break;
}
}
cells.Merge(i + 6, 1, number, 1);
cells[i + 6, 1].PutValue(dt.Rows[i][1]);
}
cells[i + 6, 2].PutValue(dt.Rows[i][2]);
}
Cells cells2 = worksheet.Cells;
int columnCount = cells2.MaxColumn; //获取表页的最大列数
int rowCount = cells.MaxRow; //获取表页的最大行数
for (int col = 0; col < columnCount; col++)
{
worksheet.AutoFitColumn(col, 0, rowCount);
}
cells2.SetColumnWidth(0, 15);
cells2.SetColumnWidth(1, 15);
cells2.SetColumnWidth(2, 30);
cells2.SetColumnWidth(3, 4);
cells2.SetColumnWidth(4, 4);
for (int col = 0; col < rowCount; col++)
{
cells2.SetRowHeight(col+1, 26);
}
workbook.Save(System.IO.Path.Combine(Application.StartupPath, "test01.xls"));
workbook.Save(System.IO.Path.Combine(Application.StartupPath, "test01.pdf"), SaveFormat.Pdf);
}
//一般标题样式
protected Aspose.Cells.Style get_title_style(Workbook workbook, Color clrmy)
{
Aspose.Cells.Style styleTitle = workbook.Styles[workbook.Styles.Add()];
styleTitle.HorizontalAlignment = TextAlignmentType.Center; //标题居中对齐
styleTitle.VerticalAlignment = TextAlignmentType.Bottom; //垂直底对齐
styleTitle.Font.Name = "Arial"; //字体
styleTitle.Font.Size = 11; //字体大小
styleTitle.IsTextWrapped = true; //自动换行
styleTitle.Font.Color = clrmy;
return styleTitle;
}
//------------------------------------------------------------------------
// 工作表标题行,第一行样式
//------------------------------------------------------------------------
protected Aspose.Cells.Style set_title_style(Workbook workbook, TextAlignmentType aliCenter)
{
Aspose.Cells.Style style_top = workbook.Styles[workbook.Styles.Add()];
style_top.HorizontalAlignment = aliCenter; //标题居中对齐
style_top.Font.Size = 18; //字体大小
style_top.Font.Color = System.Drawing.Color.Blue;
style_top.Font.IsBold = true;
return style_top;
}
}
}