Spire.XLS试用手记

Spire.XLS试用手记

    Spire.XLS是一个.Net操作Excel的组件。

一 首先下载该软件
下载地址
http://www.e-iceblue.com/downloads/spire.xls_7.6.33.zip


    下载解压以后，双击spire.xls_7.6.33.msi进行安装；

    一路next安装完成。


二 运行Demo
    如下图。左侧是Demo列表，可以看到Demo例子比较多。



三 运行第一个编辑工作表的例子

new一个工作表，
Workbook workbook = new Workbook();

从模板载入工作表，
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\EditSheetSample.xls");

对单元格赋值，
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["B1"].Text = "Hello,World!";
sheet.Range["B2"].NumberValue = 1234.5678;
sheet.Range["B3"].DateTimeValue = System.DateTime.Now;
sheet.Range["B4"].Formula = "=1111*11111";

保存为需要的xls文件，
workbook.SaveToFile("Sample.xls");

运行结果如下图；



四 运行一个生成图表的例子

初始化图表，
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";

调用createChartData插入图表数据，
createChartData(sheet);

设置图表所占单元格范围，标题，坐标轴等属性，
ChartSheet chartsheet = workbook.Charts.Add("chart worksheet");
chartsheet.DataRange = sheet.Range["A1:B5"];
chartsheet.SeriesDataFromRange = false;

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");

创建图表数据，给单元格赋值；
private void CreateChartData(Worksheet sheet)
{
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";


//Sales
sheet.Range["B1"].Value = "Sales";
sheet.Range["B2"].NumberValue = 6000;
sheet.Range["B3"].NumberValue = 8000;
sheet.Range["B4"].NumberValue = 9000;
sheet.Range["B5"].NumberValue = 8500;

//Style
sheet.Range["A1:B1"].Style.Font.IsBold = true;
sheet.Range["A2:B2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:B3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:B4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:B5"].Style.KnownColor = ExcelColors.LightTurquoise;

//Border
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

sheet.Range["B2:B5"].Style.NumberFormat = "\"$\"#,##0";
}


运行结果如下图；


五 做一个数据导出的例子
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\DataTableSample.xls");
Worksheet sheet = workbook.Worksheets[0];
this.dataGrid1.DataSource =  sheet.ExportDataTable();

运行结果如下图；


比较方便，一条语句可以把excel表格内容导入到DataTable中；


六 做一个读XML的例子
Workbook workbook = new Workbook();
using (FileStream fileStream = File.OpenRead(@"E:\sample.xml"))
{
      workbook.LoadFromXml(fileStream);
}
workbook.SaveToFile("Sample.xls");

运行结果如下图；



比较方便，一条语句可以读取XML文档到excel表格；

七 做一个插入行的例子

Worksheet worksheet = workbook.Worksheets[0];
worksheet.InsertRow(2);
worksheet.Copy(worksheet.Range["A1:E1"], worksheet.Range["A2:E2"], true);
worksheet.InsertRow(5, 2);
worksheet.Copy(worksheet.Range["A3:E4"], worksheet.Range["A5:E6"], true);


把一个行的内容和风格拷贝到另一个行，结果如下；



    这个软件还是不错的，操作Excel的功能丰富，编程比较方便，也不太大。