private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
if (checkBox1.Checked)
{
chart.ChartType = ExcelChartType.LineMarkersStacked;
}
else
{
chart.ChartType = ExcelChartType.LineStacked;
}
//Set region of chart data
chart.DataRange = sheet.Range["A1:E5"];
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
//Chart title
chart.ChartTitle = "Sales market by country";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Month";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MinValue = 1000;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
foreach (Charts.ChartSerie cs in chart.Series)
{
cs.Format.Options.IsVaryColor = true;
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
}
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
CreateChartData(sheet);
CreateChart(sheet, chart);
if (checkBox1.Checked)
{
chart.ChartType = ExcelChartType.ScatterSmoothedLineMarkers;
}
else
{
chart.ChartType = ExcelChartType.ScatterLineMarkers;
}
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void graphCreate_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = true;
CreateChartData(sheet);
Chart chart = sheet.Charts.Add();
ChartColoring(sheet, chart);
try
{
workbook.SaveToFile(Application.StartupPath + "\\results\\Result.xls");
taskText.Text += "chart created\r\n";
}
catch (IOException)
{
MessageBox.Show("Already opened...");
taskText.Text += "chart creation failed... try again later\r\n";
}
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.SetColumnWidth(1, 25);
//Creates font
ExcelFont fontBlue = workbook.CreateFont();
fontBlue.KnownColor = ExcelColors.LightBlue;
ExcelFont fontGreen = workbook.CreateFont();
fontGreen.KnownColor = ExcelColors.Red;
CellRange range = sheet.Range["A1"];
range.Text = "Regular comment";
range.Comment.Text = "Regular comment";
//Regular comment
range = sheet.Range["A2"];
range.Text = "Rich text comment";
//Rich text comment
range.Comment.RichText.Text = "Rich text comment";
range.Comment.RichText.SetFont(0, 4, fontGreen);
range.Comment.RichText.SetFont(5, 9, fontBlue);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Add a new chart worsheet to workbook
Chart chart = null;
if (checkBox1.Checked)
{
chart = sheet.Charts.Add(ExcelChartType.Pie3DExploded);
}
else
{
chart = sheet.Charts.Add(ExcelChartType.PieExploded);
}
CreateChartData(sheet);
CreateChart(sheet, chart);
chart.PlotArea.Fill.Visible = false;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set region of chart data
chart.DataRange = sheet.Range["B1:B5"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
if (checkBox1.Checked)
{
chart.ChartType = ExcelChartType.Cone3DClustered;
}
else
{
chart.ChartType = ExcelChartType.ConeClustered;
}
//Chart title
chart.ChartTitle = "Sales by year";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Year";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.MinValue = 1000;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
Charts.ChartSerie cs = chart.Series[0];
cs.CategoryLabels = sheet.Range["A2:A5"];
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRead_Click(object sender, RoutedEventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set region of chart data
chart.DataRange = sheet.Range["A1:C5"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 7;
chart.RightColumn = 11;
chart.BottomRow = 30;
if (chk3D.IsChecked==true)
{
chart.ChartType = ExcelChartType.Area3D;
}
else
{
chart.ChartType = ExcelChartType.Area;
}
//Chart title area
chart.ChartTitle = "Sales market by country";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Country";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
chart.PrimaryValueAxis.HasMajorGridLines = true;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MinValue = 1000;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series)
{
cs.Format.Options.IsVaryColor = true;
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
}
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Protect worksheet with a password.
sheet.Protect("Password", SheetProtectionType.All);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Writes sample data
CreateSampleData(sheet);
sheet.FreezePanes(2, 1);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set region of chart data
chart.DataRange = sheet.Range["A1:D8"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
chart.ChartType = ExcelChartType.StockVolumeHighLowClose;
//Chart title
chart.ChartTitle = "Stock chart";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Stock";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.Title = "Price(in Dollars)";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MinValue = 1000;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
chart.ChartType = ExcelChartType.DoughnutExploded;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
//Writes chart data
CreateChartData(sheet);
//Set region of chart data
chart.DataRange = sheet.Range["A1:B5"];
chart.SeriesDataFromRange = false;
//Chart title
chart.ChartTitle = "Sales market by country";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
foreach (Charts.ChartSerie cs in chart.Series)
{
cs.Format.Options.IsVaryColor = true;
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
}
chart.PlotArea.Fill.Visible = false;
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
//Set region of chart data
chart.DataRange = sheet.Range["A1:C5"];
chart.SeriesDataFromRange = false;
if (checkBox1.Checked)
{
chart.ChartType = ExcelChartType.RadarMarkers;
}
else
{
chart.ChartType = ExcelChartType.Radar;
}
//Chart title
chart.ChartTitle = "Sale market by region";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PlotArea.Fill.Visible = false;
chart.Legend.Position = LegendPositionType.Corner;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.GridLinesVisible = false;
//Writes filter data
CreateFilterData(sheet);
sheet.AutoFilters.Range = sheet.Range["A1:C1"];
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.GridLinesVisible = false;
//Writes Sample data
CreateSampleData(sheet);
sheet.GroupByRows(1, 5, false);
sheet.GroupByColumns(1, 3, false);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Protect workshet
sheet.Protect(this.textBox1.Text);
//Protect cells
sheet.Range["A1"].Text = "Locked";
sheet.Range["B1"].Text = "Unlocked";
sheet.Range["A1"].Style.Locked = true;
sheet.Range["B1"].Style.Locked = false;
//Protect workbook
workbook.Protect(this.textBox1.Text);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add(ExcelChartType.PieBar);
CreateChartData(sheet);
CreateChart(sheet, chart);
chart.PlotArea.Fill.Visible = false;
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.GridLinesVisible = false;
//Writes sample data
CreateSampleData(sheet);
//Sets page setup properties
sheet.PageSetup.Orientation = PageOrientationType.Landscape;
sheet.PageSetup.PaperSize = PaperSizeType.PaperA3;
//Sets page break
sheet.VPageBreaks.Add(sheet.Range["A5"]);
sheet.HPageBreaks.Add(sheet.Range["C6"]);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
/// <summary>
/// 工作簿合并
/// </summary>
/// <param name="obj"></param>
private void WorkBookMergeMethod(object obj)
{
FileMergeModel fileMergeModel = obj as FileMergeModel;
Workbook workBook = new Workbook();
try
{
workBook.CreateEmptySheets(1);
if (fileMergeModel != null && fileMergeModel.mergeFileList != null)
{
foreach (var mergeFile in fileMergeModel.mergeFileList)
{
Workbook subWorkBook = new Workbook();
subWorkBook.LoadFromFile(mergeFile.fileSrc);
workBook.Worksheets.AddCopy(subWorkBook.Worksheets);
subWorkBook.Dispose();
}
}
workBook.Worksheets.RemoveAt(0);
if (fileMergeModel.mergeFileExtension == ".xls")
{
workBook.SaveToFile(fileMergeModel.mergeFileSrc, ExcelVersion.Version97to2003);
}
else
{
workBook.SaveToFile(fileMergeModel.mergeFileSrc, ExcelVersion.Version2007);
}
workBook.Dispose();
ShowMessage.ShowContent(ChangeLanguage.getLanguage("ExecutionCompleted"), showTitle);
}
catch (Exception ex)
{
workBook.Dispose();
ShowMessage.ShowError(ex.Message, showTitle);
}
}
private void btnSplit_Click(object sender, RoutedEventArgs e)
{
//读取将要拆分的execl
Workbook bookOriginal = new Workbook();
bookOriginal.LoadFromFile(FilePath);
Worksheet sheet = bookOriginal.Worksheets[0];
//新建一个Workbook
Workbook newBook = new Workbook();
newBook.CreateEmptySheets(1);
Worksheet newSheet = newBook.Worksheets[0];
int j = 1;
int rangeIndex = Convert.ToInt32(txtRange.Text);
for (int i = 1; i < sheet.LastRow; i += rangeIndex)
{
CellRange range;
if (i + rangeIndex > sheet.LastRow)
{
range = sheet.Range[i, 1, sheet.LastRow, sheet.LastColumn];
}
else
{
range = sheet.Range[i, 1, i + rangeIndex, sheet.LastColumn];
}
newSheet.Copy(range, newSheet.Range[1, 1]);
newBook.SaveToFile("D:\\execl\\" + FileName + "_" + j + ".xlsx", ExcelVersion.Version2007);
j++;
}
System.Windows.MessageBox.Show("文件切割成功！");
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.GridLinesVisible = false;
//Writes Sample data
CreateSampleData(sheet);
sheet.GroupByRows(1,5,false);
sheet.GroupByColumns(1,3,false);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
if (checkBox1.Checked)
{
CreateStackedChartData(sheet);
CreateStackedColumnChart(sheet, chart);
chart.ChartType = ExcelChartType.CylinderBarStacked;
}
else
{
CreateChartData(sheet);
CreateColumnChart(sheet, chart);
chart.ChartType = ExcelChartType.CylinderBarClustered;
}
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.GridLinesVisible = false;
//Writes filter data
CreateFilterData(sheet);
sheet.AutoFilters.Range = sheet.Range["A1:C1"];
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
//Set region of chart data
chart.DataRange = sheet.Range["A1:C5"];
chart.SeriesDataFromRange = false;
if (checkBox1.Checked)
{
chart.ChartType = ExcelChartType.RadarMarkers;
}
else
{
chart.ChartType = ExcelChartType.Radar;
}
//Chart title
chart.ChartTitle = "Sale market by region";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PlotArea.Fill.Visible = false;
chart.Legend.Position = LegendPositionType.Corner;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.SetColumnWidth(1,25);
//Creates font
ExcelFont fontBlue = workbook.CreateFont();
fontBlue.KnownColor = ExcelColors.LightBlue;
ExcelFont fontGreen = workbook.CreateFont();
fontGreen.KnownColor = ExcelColors.Red;
CellRange range = sheet.Range["A1"];
range.Text = "Regular comment";
range.Comment.Text = "Regular comment";
//Regular comment
range = sheet.Range["A2"];
range.Text = "Rich text comment";
//Rich text comment
range.Comment.RichText.Text = "Rich text comment";
range.Comment.RichText.SetFont(0,4, fontGreen);
range.Comment.RichText.SetFont(5,9, fontBlue);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer( workbook.FileName );
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Protect workshet
sheet.Protect( this.textBox1.Text );
//Protect cells
sheet.Range["A1"].Text = "Locked";
sheet.Range["B1"].Text = "Unlocked";
sheet.Range["A1"].Style.Locked = true;
sheet.Range["B1"].Style.Locked = false;
//Protect workbook
workbook.Protect(this.textBox1.Text);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
if (checkBox1.Checked)
{
chart.ChartType = ExcelChartType.LineMarkers100PercentStacked;
}
else
{
chart.ChartType = ExcelChartType.Line100PercentStacked;
}
//Set region of chart data
chart.DataRange = sheet.Range["A1:E5"];
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
//Chart title
chart.ChartTitle = "Sales market by country";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Month";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.Title = "Sales(%)";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
foreach (Charts.ChartSerie cs in chart.Series)
{
cs.Format.Options.IsVaryColor = true;
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
}
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
/// <summary>
/// 类集合，导出到Excel，返回excel文件名
/// </summary>
/// <typeparam name="T">类</typeparam>
/// <param name="tList">List集合</param>
/// <param name="filePath">存放文件目录</param>
/// <returns>excel文件名</returns>
public static string ListToExcel <T>(List <T> tList, string filePath) where T : class, new()
{
//创建一个Excel文件
try
{
T t = new T();
Type type = t.GetType();
PropertyInfo[] p_list = type.GetProperties();
FieldInfo[] fieldInfos = type.GetFields();
DataTable dt = new DataTable();
if (p_list != null && p_list.Length > 0)
{
/**利用attribute将属性转成列名*/
for (int i = 0; i < p_list.Length; i++)
{
Attribute datareaderattr = p_list[i].GetCustomAttribute(typeof(DA_GROSSDtos1), false);
if (datareaderattr != null)
{
DA_GROSSDtos1 dataattr = new DA_GROSSDtos1();
string columName = dataattr.DT_BOM;
dt.Columns.Add(columName, Type.GetType("System.String"));
}
}
}
/**List数据转DataTable*/
for (int j = 0; j < tList.Count; j++)
{
DataRow row = dt.NewRow();
T tem = tList[j];
Type jtype = tem.GetType();
PropertyInfo[] j_list = type.GetProperties();
FieldInfo[] jfieldInfos = type.GetFields();
if (j_list != null && j_list.Length > 0)
{
/**利用循环将List中类的值填到dt中，一行值对应一个类*/
foreach (var item in j_list)
{
Attribute datareaderattr = item.GetCustomAttribute(typeof(DA_GROSSDtos1), false);
if (datareaderattr != null)
{
DA_GROSSDtos1 dataattr = new DA_GROSSDtos1();
row[dataattr.DT_CLIENTNAME] = item.GetValue(tem, null).ToString();
}
}
dt.Rows.Add(row);
}
}
/**新建excel*/
Workbook newBook1 = new Workbook();
newBook1.CreateEmptySheets(1);
Worksheet newSheet1 = newBook1.Worksheets[0];
/**利用双循环将dt值填到excel表中*/
for (int k = 0; k < dt.Columns.Count; k++)
{
newSheet1.Range[1, k + 1].Text = dt.Columns[k].ColumnName;
for (int z = 0; z < dt.Rows.Count; z++)
{
newSheet1.Range[z + 2, k + 1].Text = dt.Rows[z][k].ToString();
}
}
/**设置样式*/
newSheet1.AllocatedRange.AutoFitColumns(); //列宽自适应
newSheet1.Range[1, 1, 1, newSheet1.LastColumn].Style.Font.IsBold = true; //首行字体加粗
newSheet1.FreezePanes(2, 1); //首行冻结
/**保存*/
string strName = @"\Export" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
string path = filePath + strName;
newBook1.SaveToFile(path, ExcelVersion.Version2013);
return(strName);
}
catch (Exception ex)
{
//LogTool.ExceptionLog(ex, ex.StackTrace);
return(ex.Message);
}
}
/// <summary>
/// 工作表合并
/// </summary>
/// <param name="obj"></param>
private void WorkSheetMergeMethod(object obj)
{
FileMergeModel fileMergeModel = obj as FileMergeModel;
try
{
if (fileMergeModel != null && fileMergeModel.mergeFileList != null)
{
int index = 0;
foreach (var mergeFile in fileMergeModel.mergeFileList)
{
string filePath = fileMergeModel.mergeFilePath + @"\" + fileMergeModel.mergeFileName + index + fileMergeModel.mergeFileExtension;
//新建工作簿
Workbook workBook = new Workbook();
workBook.CreateEmptySheets(1);
//需合并的工作簿
Workbook subWorkBook = new Workbook();
subWorkBook.LoadFromFile(mergeFile.fileSrc);
//同步第一个工作表并生成
workBook.Worksheets.AddCopy(subWorkBook.Worksheets);
int sheetCount = workBook.Worksheets.Count;
for (int i = sheetCount - 1; i >= 0; i--)
{
if (i != 1)
{
workBook.Worksheets.RemoveAt(i);
}
}
if (fileMergeModel.mergeFileExtension == ".xls")
{
workBook.SaveToFile(filePath, ExcelVersion.Version97to2003);
}
else
{
workBook.SaveToFile(filePath, ExcelVersion.Version2007);
}
workBook.LoadFromFile(filePath);
Worksheet workSheet = workBook.Worksheets[0];
//遍历工作簿中的工作表
int subIndex = 0;
foreach (Worksheet subSheet in subWorkBook.Worksheets)
{
if (subIndex > 0)
{
//复制subSheet工作表内容到合并工作表(workSheet)的指定区域中->追加
subSheet.AllocatedRange.Copy(workSheet.Range[workSheet.LastRow + 1, 1]);
}
subIndex++;
}
subWorkBook.Dispose();
workSheet.Name = "Merged sheet";
if (fileMergeModel.mergeFileExtension == ".xls")
{
workBook.SaveToFile(filePath, ExcelVersion.Version97to2003);
}
else
{
workBook.SaveToFile(filePath, ExcelVersion.Version2007);
}
workBook.Dispose();
index++;
}
ShowMessage.ShowContent(ChangeLanguage.getLanguage("ExecutionCompleted"), showTitle);
}
}
catch (Exception ex)
{
ShowMessage.ShowError(ex.Message, showTitle);
}
}
private void exportExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
//export to excel
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\FinalData.mdf;Integrated Security=True");
SqlCommand commandor = new SqlCommand("SELECT * FROM Table12", con); //uncleaned
SqlCommand command = new SqlCommand("SELECT * FROM clean", con); //Clean data
SqlCommand comm = new SqlCommand("SELECT * FROM duplicate", con); //Duplicats
SqlCommand com = new SqlCommand("SELECT * FROM InvalidID", con); //invalidID
SqlDataAdapter firstadapter = new SqlDataAdapter(commandor.CommandText, con);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command.CommandText, con);
SqlDataAdapter dataAdapter1 = new SqlDataAdapter(comm.CommandText, con);
SqlDataAdapter datareaderinvalidId = new SqlDataAdapter(com.CommandText, con);
string file = open.FileName;
//datatables tobe filled with data from sql
DataTable t = new DataTable();
DataTable table = new DataTable();
DataTable table2 = new DataTable();
DataTable tableinvalid = new DataTable();
dataAdapter.Fill(t);
firstadapter.Fill(table);
dataAdapter1.Fill(table2);
datareaderinvalidId.Fill(tableinvalid);
// workbooks to export excel
Workbook book = new Workbook();
book.CreateEmptySheets(4);
Worksheet sheet1 = book.Worksheets[0];
sheet1.Name = textBox2.Text;
//style sheet
sheet1.Range["A1:N1"].Style.Font.IsBold = true;
sheet1.Range["A1:N1"].Style.Color = Color.Gray;
sheet1.InsertDataTable(table, true, 1, 1);
//------------------------------------------------------------
Worksheet sheet = book.Worksheets[1];
sheet.Name = "clean";
//style sheet
sheet.Range["A1:N1"].Style.Font.IsBold = true;
sheet.Range["A1:N1"].Style.Color = Color.Gray;
sheet.InsertDataTable(t, true, 1, 1);
Worksheet sheet2 = book.Worksheets[2];
sheet2.Name = "Duplicates";
//style sheet
sheet2.Range["A1:N1"].Style.Font.IsBold = true;
sheet2.Range["A1:N1"].Style.Color = Color.Gray;
sheet2.InsertDataTable(table2, true, 1, 1);
Worksheet sheet3 = book.Worksheets[3];
sheet3.Name = "InvalidID";
//style sheet
sheet3.Range["A1:N1"].Style.Font.IsBold = true;
sheet3.Range["A1:N1"].Style.Color = Color.Gray;
sheet3.InsertDataTable(tableinvalid, true, 1, 1);
book.SaveToFile(file, ExcelVersion.Version2010);
System.Diagnostics.Process.Start(file);
MessageBox.Show("successfully exported to excel ", "Export To DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information);
//string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Demo";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan";
sheet.Range["A3"].Value = "Feb";
sheet.Range["A4"].Value = "Mar";
sheet.Range["A5"].Value = "Apr";
sheet.Range["A6"].Value = "May";
sheet.Range["A7"].Value = "Jun";
sheet.Range["A8"].Value = "Jul";
sheet.Range["A9"].Value = "Aug";
sheet.Range["B1"].Value = "Planned";
sheet.Range["B2"].NumberValue = 38;
sheet.Range["B3"].NumberValue = 47;
sheet.Range["B4"].NumberValue = 39;
sheet.Range["B5"].NumberValue = 36;
sheet.Range["B6"].NumberValue = 27;
sheet.Range["B7"].NumberValue = 25;
sheet.Range["B8"].NumberValue = 36;
sheet.Range["B9"].NumberValue = 48;
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["B1:B9"];
chart.SeriesDataFromRange = false;
chart.PlotArea.Visible = false;
chart.TopRow = 6;
chart.BottomRow = 25;
chart.LeftColumn = 2;
chart.RightColumn = 9;
chart.ChartTitle = "Chart with Customized Axis";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A9"];
//format axis
chart.PrimaryValueAxis.MajorUnit = 8;
chart.PrimaryValueAxis.MinorUnit = 2;
chart.PrimaryValueAxis.MaxValue = 50;
chart.PrimaryValueAxis.MinValue = 0;
chart.PrimaryValueAxis.IsReverseOrder = false;
chart.PrimaryValueAxis.MajorTickMark = TickMarkType.TickMarkOutside;
chart.PrimaryValueAxis.MinorTickMark = TickMarkType.TickMarkInside;
chart.PrimaryValueAxis.TickLabelPosition = TickLabelPositionType.TickLabelPositionNextToAxis;
chart.PrimaryValueAxis.CrossesAt = 0;
//set NumberFormat
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryValueAxis.IsSourceLinked = false;
foreach (ChartSerie serie in chart.Series)
{
//format Series
serie.DataPoints.DefaultDataPoint.DataFormat.Fill.FillType = ShapeFillType.SolidColor;
serie.DataPoints.DefaultDataPoint.DataFormat.Fill.ForeColor = Color.Gray;
serie.DataPoints.DefaultDataPoint.DataFormat.Fill.Transparency = 0.5;
//format DataPoints
serie.DataPoints[2].DataFormat.Fill.ForeColor = Color.Red;
}
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.Version = ExcelVersion.Version2010;
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Country
sheet.Range["A1"].Value = "Country";
sheet.Range["A2"].Value = "Cuba";
sheet.Range["A3"].Value = "Mexico";
sheet.Range["A4"].Value = "France";
sheet.Range["A5"].Value = "German";
//Jun
sheet.Range["B1"].Value = "Jun";
sheet.Range["B2"].NumberValue = 0.23;
sheet.Range["B3"].NumberValue = 0.37;
sheet.Range["B4"].NumberValue = 0.15;
sheet.Range["B5"].NumberValue = 0.25;
//Jul
sheet.Range["C1"].Value = "Jul";
sheet.Range["C2"].NumberValue = 0.1;
sheet.Range["C3"].NumberValue = 0.35;
sheet.Range["C4"].NumberValue = 0.22;
sheet.Range["C5"].NumberValue = 0.33;
//Aug
sheet.Range["D1"].Value = "Aug";
sheet.Range["D2"].NumberValue = 0.14;
sheet.Range["D3"].NumberValue = 0.36;
sheet.Range["D4"].NumberValue = 0.25;
sheet.Range["D5"].NumberValue = 0.25;
//Aug
sheet.Range["E1"].Value = "Sep";
sheet.Range["E2"].NumberValue = 0.17;
sheet.Range["E3"].NumberValue = 0.28;
sheet.Range["E4"].NumberValue = 0.39;
sheet.Range["E5"].NumberValue = 0.32;
//Style
sheet.Range["A1:E1"].Style.Font.IsBold = true;
sheet.Range["A2:E2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:E4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:E5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
sheet.Range["B2:D5"].Style.NumberFormatIndex = 9;
SparklineGroup sparklineGroup
= sheet.SparklineGroups.AddGroup(SparklineType.Line);
SparklineCollection sparklines = sparklineGroup.Add();
sparklines.Add(sheet["B2:E2"], sheet["F2"]);
sparklines.Add(sheet["B3:E3"], sheet["F3"]);
sparklines.Add(sheet["B4:E4"], sheet["F4"]);
sparklines.Add(sheet["B5:E5"], sheet["F5"]);
workbook.SaveToFile("Sample.xlsx");
ExcelDocViewer(workbook.FileName);
}
private void SplitSheetMethod(object obj)
{
FileSplitModel fileSplitModel = obj as FileSplitModel;
Workbook workBook = new Workbook();
try
{
workBook.LoadFromFile(fileSplitModel.filePath);
foreach (Worksheet sheet in workBook.Worksheets)
{
//实例化一个新的Workbook对象，创建一个空的工作表
Workbook newWorkBook = new Workbook();
newWorkBook.CreateEmptySheets(1);
string filePath = fileSplitModel.fileOutPath + @"\" + fileSplitModel.fileName + sheet.Index + fileSplitModel.fileExtension;
//拆分
if (fileSplitModel.fileExcel.splitType == 1)
{
if (fileSplitModel.fileExtension == ".xls")
{
newWorkBook.SaveToFile(filePath, ExcelVersion.Version97to2003);
}
else
{
newWorkBook.SaveToFile(filePath, ExcelVersion.Version2007);
}
newWorkBook.LoadFromFile(filePath);//加载工作簿
//获取源excel工作表中的工作表，将它们复制到newWorkBook中的工作表
Worksheet newSheet = newWorkBook.Worksheets.AddCopy(sheet);
newWorkBook.Worksheets.RemoveAt(0);
newSheet.Name = sheet.Name;
}
else if (fileSplitModel.fileExcel.splitType == 2)
{
if (sheet.FirstRow == -1)
{
continue;
}
string sheetName = fileSplitModel.fileExcel.sheetName;
int begin = fileSplitModel.fileExcel.beginRow;
int end = fileSplitModel.fileExcel.endRow;
if (end == 0)
{
end = sheet.LastRow;
}
Worksheet newWorkSheet = newWorkBook.Worksheets[0];
CellRange range = sheet.Range[begin, 1, end, sheet.LastColumn];
if (!string.IsNullOrWhiteSpace(sheetName))
{
if (sheet.Name == sheetName)
{
newWorkSheet.Copy(range, newWorkSheet.Range[1, 1]);
}
}
else
{
newWorkSheet.Copy(range, newWorkSheet.Range[1, 1]);
}
newWorkSheet.Name = sheet.Name;
}
if (fileSplitModel.fileExtension == ".xls")
{
newWorkBook.SaveToFile(filePath, ExcelVersion.Version97to2003);
}
else
{
newWorkBook.SaveToFile(filePath, ExcelVersion.Version2007);
}
newWorkBook.Dispose();
}
workBook.Dispose();
ShowMessage.ShowContent(ChangeLanguage.getLanguage("ExecutionCompleted"), showTitle);
}
catch (Exception ex)
{
workBook.Dispose();
ShowMessage.ShowError(ex.Message, showTitle);
}
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.GridLinesVisible = false;
//Writes sample data
CreateSampleData(sheet);
//Sets page setup properties
sheet.PageSetup.Orientation = PageOrientationType.Landscape;
sheet.PageSetup.PaperSize = PaperSizeType.PaperA3;
//Sets page break
sheet.VPageBreaks.Add(sheet.Range["A5"]);
sheet.HPageBreaks.Add(sheet.Range["C6"]);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set region of chart data
chart.DataRange = sheet.Range["A1:C5"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
chart.ChartType = ExcelChartType.Doughnut;
//Chart title
chart.ChartTitle = "Sales market by country";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
foreach (Charts.ChartSerie cs in chart.Series)
{
cs.Format.Options.IsVaryColor = true;
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
}
chart.PlotArea.Fill.Visible = false;
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
ChartSheet chartsheet = workbook.Charts.Add("chart worksheet");
//Set region of chart data
chartsheet.DataRange = sheet.Range["A1:B5"];
chartsheet.SeriesDataFromRange = false;
//Chart title
chartsheet.ChartTitle = "Sales market by country";
chartsheet.ChartTitleArea.IsBold = true;
chartsheet.ChartTitleArea.Size = 12;
chartsheet.PrimaryCategoryAxis.Title = "Country";
chartsheet.PrimaryCategoryAxis.Font.IsBold = true;
chartsheet.PrimaryCategoryAxis.TitleArea.IsBold = true;
chartsheet.PrimaryValueAxis.Title = "Sales(in Dollars)";
chartsheet.PrimaryValueAxis.HasMajorGridLines = false;
chartsheet.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chartsheet.PrimaryValueAxis.MinValue = 1000;
chartsheet.PrimaryValueAxis.TitleArea.IsBold = true;
foreach (Charts.ChartSerie cs in chartsheet.Series)
{
cs.Format.Options.IsVaryColor = true;
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
}
chartsheet.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.Version = ExcelVersion.Version2010;
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Country
sheet.Range["A1"].Value = "Country";
sheet.Range["A2"].Value = "Cuba";
sheet.Range["A3"].Value = "Mexico";
sheet.Range["A4"].Value = "France";
sheet.Range["A5"].Value = "German";
//Jun
sheet.Range["B1"].Value = "Jun";
sheet.Range["B2"].NumberValue = 0.23;
sheet.Range["B3"].NumberValue = 0.37;
sheet.Range["B4"].NumberValue = 0.15;
sheet.Range["B5"].NumberValue = 0.25;
//Jul
sheet.Range["C1"].Value = "Jul";
sheet.Range["C2"].NumberValue = 0.1;
sheet.Range["C3"].NumberValue = 0.35;
sheet.Range["C4"].NumberValue = 0.22;
sheet.Range["C5"].NumberValue = 0.33;
//Aug
sheet.Range["D1"].Value = "Aug";
sheet.Range["D2"].NumberValue = 0.14;
sheet.Range["D3"].NumberValue = 0.36;
sheet.Range["D4"].NumberValue = 0.25;
sheet.Range["D5"].NumberValue = 0.25;
//Aug
sheet.Range["E1"].Value = "Sep";
sheet.Range["E2"].NumberValue = 0.17;
sheet.Range["E3"].NumberValue = 0.28;
sheet.Range["E4"].NumberValue = 0.39;
sheet.Range["E5"].NumberValue = 0.32;
//Style
sheet.Range["A1:E1"].Style.Font.IsBold = true;
sheet.Range["A2:E2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:E4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:E5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
sheet.Range["B2:D5"].Style.NumberFormatIndex = 9;
SparklineGroup sparklineGroup
= sheet.SparklineGroups.AddGroup(SparklineType.Line);
SparklineCollection sparklines = sparklineGroup.Add();
sparklines.Add(sheet["B2:E2"], sheet["F2"]);
sparklines.Add(sheet["B3:E3"], sheet["F3"]);
sparklines.Add(sheet["B4:E4"], sheet["F4"]);
sparklines.Add(sheet["B5:E5"], sheet["F5"]);
workbook.SaveToFile("Sample.xlsx");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set region of chart data
chart.DataRange = sheet.Range["B1:B5"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
if (checkBox1.Checked)
{
chart.ChartType = ExcelChartType.Cylinder3DClustered;
}
else
{
chart.ChartType = ExcelChartType.CylinderClustered;
}
//Chart title
chart.ChartTitle = "Sales by year";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Year";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.MinValue = 1000;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
Charts.ChartSerie cs = chart.Series[0];
cs.CategoryLabels = sheet.Range["A2:A5"];
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Add a new chart worsheet to workbook
Chart chart = null;
if (checkBox1.Checked)
{
chart = sheet.Charts.Add(ExcelChartType.Pie3DExploded);
}
else
{
chart = sheet.Charts.Add(ExcelChartType.PieExploded);
}
CreateChartData(sheet);
CreateChart(sheet, chart);
chart.PlotArea.Fill.Visible = false;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Writes sample data
CreateSampleData(sheet);
sheet.FreezePanes(2,1);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
CreateChartData(sheet);
CreateChart(sheet, chart);
chart.ChartType = ExcelChartType.ScatterMarkers;
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set region of chart data
chart.DataRange = sheet.Range["A1:E8"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
chart.ChartType = ExcelChartType.StockVolumeOpenHighLowClose;
//Chart title
chart.ChartTitle = "Stock chart";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Stock";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.Title = "Price(in Dollars)";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MinValue = 1000;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
Charts.ChartSerie cs = chart.Series[0];
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
static void Main(string[] args)
{
Workbook sourceBook = new Workbook();
sourceBook.LoadFromFile(@"..\..\Exercise.xls", ExcelVersion.Version97to2003);
Worksheet sourceSheet = sourceBook.Worksheets[0];
DataTable SourceTable = sourceSheet.ExportDataTable();
for (int i = 0; i < sourceSheet.Pictures.Count; i++)
{
ExcelPicture pic = sourceSheet.Pictures[i];
}
List <Country> countryInfo = new List <Country>();
for (int i = 1, k = 0; i < sourceSheet.Rows.Length && k < sourceSheet.Pictures.Count; i++, k++)
{
int j = 0;
countryInfo.Add(
new Country
{
Name = sourceSheet.Rows[i].Columns[j].Text,
Capital = sourceSheet.Rows[i].Columns[j + 1].Text,
Continent = sourceSheet.Rows[i].Columns[j + 2].Text,
Area = Convert.ToSingle(sourceSheet.Rows[i].Columns[j + 3].NumberValue),
Population = Convert.ToSingle(sourceSheet.Rows[i].Columns[j + 4].NumberValue),
Flag = sourceSheet.Pictures[k].Picture
}
);
}
//Sort the List
countryInfo.Sort(Country.compare);
Workbook destBook = new Workbook();
//Initailize worksheet
destBook.CreateEmptySheets(1);
Worksheet destSheet = destBook.Worksheets[0];
destSheet.Name = "Country Information";
//inset the first row
for (int i = 1; i <= sourceSheet.Columns.Length; i++)
{
destSheet.Range[1, i].Text = sourceSheet.Range[1, i].Text;
}
//insert data
int rowIndex = 2;
for (int i = 0; i < countryInfo.Count; i++)
{
int j = 0;
destSheet.Range[rowIndex, 1].Text = countryInfo[i].Name;
destSheet.Range[rowIndex, 2].Text = countryInfo[i].Capital;
destSheet.Range[rowIndex, 3].Text = countryInfo[i].Continent;
destSheet.Range[rowIndex, 4].NumberValue = countryInfo[i].Area;
destSheet.Range[rowIndex, 5].NumberValue = countryInfo[i].Population;
destSheet.Pictures.Add(rowIndex, 6, countryInfo[i].Flag);
rowIndex++;
}
//set style
destSheet = SetStyle(destBook, destSheet);
destSheet.AllocatedRange.AutoFitColumns();
destBook.SaveToFile(@"..\..\CountryInfo.xls", ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start(@"..\..\CountryInfo.xls");
}
