Spire.DataExport for .NET
Spire.DataExport for .NET (33)
Children categories
Not needing to have Microsoft Excel installed on the machine, The Spire.DataExport can create Excel spreadsheet. This sample demonstrates how to export data table into xls and create pie-3d chart.
private void button1_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb"; System.Data.OleDb.OleDbCommand oleDbCommand1 = new System.Data.OleDb.OleDbCommand(); oleDbCommand1.CommandText = "select * from parts"; oleDbCommand1.Connection = oleDbConnection1; System.Data.OleDb.OleDbCommand oleDbCommand2 = new System.Data.OleDb.OleDbCommand(); oleDbCommand2.CommandText = "select * from country"; oleDbCommand2.Connection = oleDbConnection1; Spire.DataExport.XLS.CellExport cellExport2 = new Spire.DataExport.XLS.CellExport(); Spire.DataExport.XLS.WorkSheet workSheet3 = new Spire.DataExport.XLS.WorkSheet(); Spire.DataExport.XLS.Chart chart1 = new Spire.DataExport.XLS.Chart(); Spire.DataExport.XLS.ChartSeries chartSeries1 = new Spire.DataExport.XLS.ChartSeries(); Spire.DataExport.XLS.StripStyle stripStyle5 = new Spire.DataExport.XLS.StripStyle(); Spire.DataExport.XLS.StripStyle stripStyle6 = new Spire.DataExport.XLS.StripStyle(); cellExport2.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView; cellExport2.AutoFitColWidth = true; cellExport2.DataFormats.CultureName = "zh-CN"; cellExport2.DataFormats.Currency = "¥#,###,##0.00"; cellExport2.DataFormats.DateTime = "yyyy-M-d H:mm"; cellExport2.DataFormats.Float = "#,###,##0.00"; cellExport2.DataFormats.Integer = "#,###,##0"; cellExport2.DataFormats.Time = "H:mm"; cellExport2.FileName = "chart1.xls"; cellExport2.SheetOptions.AggregateFormat.Font.Name = "Arial"; cellExport2.SheetOptions.CustomDataFormat.Font.Name = "Arial"; cellExport2.SheetOptions.DefaultFont.Name = "Arial"; cellExport2.SheetOptions.FooterFormat.Font.Name = "Arial"; cellExport2.SheetOptions.HeaderFormat.Font.Name = "Arial"; cellExport2.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; cellExport2.SheetOptions.HyperlinkFormat.Font.Name = "Arial"; cellExport2.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single; cellExport2.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left; cellExport2.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top; cellExport2.SheetOptions.NoteFormat.Font.Bold = true; cellExport2.SheetOptions.NoteFormat.Font.Name = "Tahoma"; cellExport2.SheetOptions.NoteFormat.Font.Size = 8F; cellExport2.SheetOptions.TitlesFormat.Font.Bold = true; cellExport2.SheetOptions.TitlesFormat.Font.Name = "Arial"; workSheet3.AutoFitColWidth = true; chart1.CategoryLabels.ColX = ((System.Byte)(1)); chart1.CategoryLabels.ColY = ((System.Byte)(1)); chart1.CategoryLabels.RowX = 1; chart1.CategoryLabels.RowY = 9; chart1.CategoryLabelsColumn = "Name"; chart1.DataRangeSheet = "charts"; chart1.Position.AutoPosition.Height = 12; chart1.Position.AutoPosition.Top = 1; chart1.Position.AutoPosition.Width = 6; chartSeries1.DataColumn = "Population"; chartSeries1.DataRangeSheet = "charts"; chartSeries1.Title = "Population"; chart1.Series.Add(chartSeries1); chart1.Style = Spire.DataExport.XLS.ChartStyle.Pie3d; chart1.Title = "Chart demo"; workSheet3.Charts.Add(chart1); workSheet3.FormatsExport.CultureName = "zh-CN"; workSheet3.FormatsExport.Currency = "¥#,###,##0.00"; workSheet3.FormatsExport.DateTime = "yyyy-M-d H:mm"; workSheet3.FormatsExport.Float = "#,###,##0.00"; workSheet3.FormatsExport.Integer = "#,###,##0"; workSheet3.FormatsExport.Time = "H:mm"; stripStyle5.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle5.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle5.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle5.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen; stripStyle5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; stripStyle5.Font.Name = "Arial"; stripStyle6.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle6.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle6.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle6.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle6.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise; stripStyle6.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; stripStyle6.Font.Name = "Arial"; workSheet3.ItemStyles.Add(stripStyle5); workSheet3.ItemStyles.Add(stripStyle6); workSheet3.ItemType = Spire.DataExport.XLS.CellItemType.Col; workSheet3.Options.AggregateFormat.Font.Name = "Arial"; workSheet3.Options.CustomDataFormat.Font.Name = "Arial"; workSheet3.Options.DefaultFont.Name = "Arial"; workSheet3.Options.FooterFormat.Font.Name = "Arial"; workSheet3.Options.HeaderFormat.Font.Bold = true; workSheet3.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; workSheet3.Options.HeaderFormat.Font.Name = "Arial"; workSheet3.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; workSheet3.Options.HyperlinkFormat.Font.Name = "Arial"; workSheet3.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single; workSheet3.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left; workSheet3.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top; workSheet3.Options.NoteFormat.Font.Bold = true; workSheet3.Options.NoteFormat.Font.Name = "Tahoma"; workSheet3.Options.NoteFormat.Font.Size = 8F; workSheet3.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet3.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet3.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet3.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet3.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow; workSheet3.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; workSheet3.Options.TitlesFormat.Font.Bold = true; workSheet3.Options.TitlesFormat.Font.Name = "Arial"; workSheet3.SheetName = "charts"; workSheet3.SQLCommand = oleDbCommand2; workSheet3.StartDataCol = ((System.Byte)(0)); cellExport2.Sheets.Add(workSheet3); cellExport2.SQLCommand = oleDbCommand1; oleDbConnection1.Open(); try { cellExport2.SaveToFile(); } finally { oleDbConnection1.Close(); } }
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim oleDbConnection1 As New System.Data.OleDb.OleDbConnection() oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb" Dim oleDbCommand1 As New System.Data.OleDb.OleDbCommand() oleDbCommand1.CommandText = "select * from parts" oleDbCommand1.Connection = oleDbConnection1 Dim oleDbCommand2 As New System.Data.OleDb.OleDbCommand() oleDbCommand2.CommandText = "select * from country" oleDbCommand2.Connection = oleDbConnection1 Dim cellExport2 As New Spire.DataExport.XLS.CellExport() Dim workSheet3 As New Spire.DataExport.XLS.WorkSheet() Dim chart1 As New Spire.DataExport.XLS.Chart() Dim chartSeries1 As New Spire.DataExport.XLS.ChartSeries() Dim stripStyle5 As New Spire.DataExport.XLS.StripStyle() Dim stripStyle6 As New Spire.DataExport.XLS.StripStyle() cellExport2.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView cellExport2.AutoFitColWidth = True cellExport2.DataFormats.CultureName = "zh-CN" cellExport2.DataFormats.Currency = "¥#,###,##0.00" cellExport2.DataFormats.DateTime = "yyyy-M-d H:mm" cellExport2.DataFormats.Float = "#,###,##0.00" cellExport2.DataFormats.[Integer] = "#,###,##0" cellExport2.DataFormats.Time = "H:mm" cellExport2.FileName = "chart1.xls" cellExport2.SheetOptions.AggregateFormat.Font.Name = "Arial" cellExport2.SheetOptions.CustomDataFormat.Font.Name = "Arial" cellExport2.SheetOptions.DefaultFont.Name = "Arial" cellExport2.SheetOptions.FooterFormat.Font.Name = "Arial" cellExport2.SheetOptions.HeaderFormat.Font.Name = "Arial" cellExport2.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue cellExport2.SheetOptions.HyperlinkFormat.Font.Name = "Arial" cellExport2.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single] cellExport2.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left cellExport2.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top cellExport2.SheetOptions.NoteFormat.Font.Bold = True cellExport2.SheetOptions.NoteFormat.Font.Name = "Tahoma" cellExport2.SheetOptions.NoteFormat.Font.Size = 8.0F cellExport2.SheetOptions.TitlesFormat.Font.Bold = True cellExport2.SheetOptions.TitlesFormat.Font.Name = "Arial" workSheet3.AutoFitColWidth = True chart1.CategoryLabels.ColX = CByte(1) chart1.CategoryLabels.ColY = CByte(1) chart1.CategoryLabels.RowX = 1 chart1.CategoryLabels.RowY = 9 chart1.CategoryLabelsColumn = "Name" chart1.DataRangeSheet = "charts" chart1.Position.AutoPosition.Height = 12 chart1.Position.AutoPosition.Top = 1 chart1.Position.AutoPosition.Width = 6 chartSeries1.DataColumn = "Population" chartSeries1.DataRangeSheet = "charts" chartSeries1.Title = "Population" chart1.Series.Add(chartSeries1) chart1.Style = Spire.DataExport.XLS.ChartStyle.Pie3d chart1.Title = "Chart demo" workSheet3.Charts.Add(chart1) workSheet3.FormatsExport.CultureName = "zh-CN" workSheet3.FormatsExport.Currency = "¥#,###,##0.00" workSheet3.FormatsExport.DateTime = "yyyy-M-d H:mm" workSheet3.FormatsExport.Float = "#,###,##0.00" workSheet3.FormatsExport.[Integer] = "#,###,##0" workSheet3.FormatsExport.Time = "H:mm" stripStyle5.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium stripStyle5.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium stripStyle5.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium stripStyle5.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium stripStyle5.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen stripStyle5.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid stripStyle5.Font.Name = "Arial" stripStyle6.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium stripStyle6.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium stripStyle6.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium stripStyle6.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium stripStyle6.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise stripStyle6.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid stripStyle6.Font.Name = "Arial" workSheet3.ItemStyles.Add(stripStyle5) workSheet3.ItemStyles.Add(stripStyle6) workSheet3.ItemType = Spire.DataExport.XLS.CellItemType.Col workSheet3.Options.AggregateFormat.Font.Name = "Arial" workSheet3.Options.CustomDataFormat.Font.Name = "Arial" workSheet3.Options.DefaultFont.Name = "Arial" workSheet3.Options.FooterFormat.Font.Name = "Arial" workSheet3.Options.HeaderFormat.Font.Bold = True workSheet3.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue workSheet3.Options.HeaderFormat.Font.Name = "Arial" workSheet3.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue workSheet3.Options.HyperlinkFormat.Font.Name = "Arial" workSheet3.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.[Single] workSheet3.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left workSheet3.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top workSheet3.Options.NoteFormat.Font.Bold = True workSheet3.Options.NoteFormat.Font.Name = "Tahoma" workSheet3.Options.NoteFormat.Font.Size = 8.0F workSheet3.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium workSheet3.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium workSheet3.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium workSheet3.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium workSheet3.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow workSheet3.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid workSheet3.Options.TitlesFormat.Font.Bold = True workSheet3.Options.TitlesFormat.Font.Name = "Arial" workSheet3.SheetName = "charts" workSheet3.SQLCommand = oleDbCommand2 workSheet3.StartDataCol = CByte(0) cellExport2.Sheets.Add(workSheet3) cellExport2.SQLCommand = oleDbCommand1 oleDbConnection1.Open() Try cellExport2.SaveToFile() Finally oleDbConnection1.Close() End Try End Sub
Not needing to have Microsoft Excel installed on the machine, The Spire.DataExport can create Excel spreadsheet. This sample demonstrates how to add formulas into xls.
private void button1_Click(object sender, EventArgs e) { Spire.DataExport.XLS.CellExport cellExport5 = new Spire.DataExport.XLS.CellExport(); cellExport5.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView; cellExport5.ColumnsWidth.AddRange(new object[] { "100", "200"}); cellExport5.DataExported = false; cellExport5.DataFormats.CultureName = "zh-CN"; cellExport5.DataFormats.Currency = "¥#,###,##0.00"; cellExport5.DataFormats.DateTime = "yyyy-M-d H:mm"; cellExport5.DataFormats.Float = "#,###,##0.00"; cellExport5.DataFormats.Integer = "#,###,##0"; cellExport5.DataFormats.Time = "H:mm"; cellExport5.FileName = "formula.xls"; cellExport5.SheetOptions.AggregateFormat.Font.Name = "Arial"; cellExport5.SheetOptions.CustomDataFormat.Font.Name = "Arial"; cellExport5.SheetOptions.DefaultFont.Name = "Arial"; cellExport5.SheetOptions.FooterFormat.Font.Name = "Arial"; cellExport5.SheetOptions.HeaderFormat.Font.Name = "Arial"; cellExport5.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; cellExport5.SheetOptions.HyperlinkFormat.Font.Name = "Arial"; cellExport5.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single; cellExport5.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left; cellExport5.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top; cellExport5.SheetOptions.NoteFormat.Font.Bold = true; cellExport5.SheetOptions.NoteFormat.Font.Name = "Tahoma"; cellExport5.SheetOptions.NoteFormat.Font.Size = 8F; cellExport5.SheetOptions.TitlesFormat.Font.Bold = true; cellExport5.SheetOptions.TitlesFormat.Font.Name = "Arial"; ushort currentRow = 1; string currentFormula = string.Empty; Spire.DataExport.XLS.Cell cell = null; cellExport5.Cells.Clear(); cellExport5.SetColumnWidth(1, 32); cellExport5.SetColumnWidth(2, 16); cellExport5.SetColumnWidth(3, 16); cell = cellExport5.AddString(1, currentRow++, "Examples of formulas :"); cell.Format.Font.Bold = true; cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen; cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; cell = cellExport5.AddString(1, ++currentRow, "Test data:"); cell.Format.Font.Bold = true; cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen; cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; //test data cellExport5.AddNumeric(2, currentRow, 7.3); cellExport5.AddNumeric(3, currentRow, 5); cellExport5.AddNumeric(4, currentRow, 8.2); cellExport5.AddNumeric(5, currentRow, 4); cellExport5.AddNumeric(6, currentRow, 3); cellExport5.AddNumeric(7, currentRow++, 11.3); cell = cellExport5.AddString(1, ++currentRow, "Formulas"); cell.Format.Font.Bold = true; cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen; cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; cell = cellExport5.AddString(2, currentRow, "Results"); cell.Format.Font.Bold = true; cell.Format.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen; cell.Format.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; cell.Format.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; //str. currentFormula = "=\"hello\""; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow, "=\"hello\""); cellExport5.AddFormula(3, currentRow, "=\"" + new string(new char[] { '\u4f60', '\u597d' }) + "\""); //int. currentFormula = "=300"; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow, currentFormula); // float currentFormula = "=3389.639421"; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow, currentFormula); //bool. currentFormula = "=false"; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow, currentFormula); currentFormula = "=1+2+3+4+5-6-7+8-9"; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow, currentFormula); currentFormula = "=33*3/4-2+10"; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow, currentFormula); // sheet reference currentFormula = "=Sheet1!$B$3"; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow, currentFormula); // sheet area reference currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow, currentFormula); // Functions currentFormula = "=Count(3,5,8,10,2,34)"; cellExport5.AddString(1, ++currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=NOW()"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=SECOND(11)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=MINUTE(12)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=MONTH(9)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=DAY(10)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=TIME(4,5,7)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=DATE(6,4,2)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=RAND()"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=HOUR(12)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=TEXT(\"world\", \"$d\")"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=MOD(5,3)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=WEEKDAY(3)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=YEAR(23)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=NOT(true)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=OR(true)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=AND(TRUE)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=VALUE(30)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=LEN(\"world\")"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=MID(\"world\",4,2)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=ROUND(7,3)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=SIGN(4)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=INT(200)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=ABS(-1.21)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=LN(15)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=EXP(20)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=SQRT(40)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=PI()"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=COS(9)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=SIN(45)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=MAX(10,30)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=MIN(5,7)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=AVERAGE(12,45)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=SUM(18,29)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=IF(4,2,2)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)"; cellExport5.AddString(1, currentRow, currentFormula); cellExport5.AddFormula(2, currentRow++, currentFormula); cellExport5.SaveToFile(); }
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cellExport5 As Spire.DataExport.XLS.CellExport cellExport5 = New Spire.DataExport.XLS.CellExport cellExport5.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView cellExport5.ColumnsWidth.AddRange(New Object() {"100", "200"}) cellExport5.DataExported = False cellExport5.DataFormats.CultureName = "zh-CN" cellExport5.DataFormats.Currency = "¥#,###,##0.00" cellExport5.DataFormats.DateTime = "yyyy-M-d H:mm" cellExport5.DataFormats.Float = "#,###,##0.00" cellExport5.DataFormats.Integer = "#,###,##0" cellExport5.DataFormats.Time = "H:mm" cellExport5.FileName = "formula.xls" cellExport5.SheetOptions.AggregateFormat.Font.Name = "Arial" cellExport5.SheetOptions.CustomDataFormat.Font.Name = "Arial" cellExport5.SheetOptions.DefaultFont.Name = "Arial" cellExport5.SheetOptions.FooterFormat.Font.Name = "Arial" cellExport5.SheetOptions.HeaderFormat.Font.Name = "Arial" cellExport5.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue cellExport5.SheetOptions.HyperlinkFormat.Font.Name = "Arial" cellExport5.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single cellExport5.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left cellExport5.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top cellExport5.SheetOptions.NoteFormat.Font.Bold = True cellExport5.SheetOptions.NoteFormat.Font.Name = "Tahoma" cellExport5.SheetOptions.NoteFormat.Font.Size = 8.0! cellExport5.SheetOptions.TitlesFormat.Font.Bold = True cellExport5.SheetOptions.TitlesFormat.Font.Name = "Arial" Dim currentRow As Integer = 1 Dim firstCol As UInt16 = Convert.ToUInt16(1) Dim secondCol As UInt16 = Convert.ToUInt16(2) Dim currentFormula As String = String.Empty Dim cell As cell = Nothing cellExport5.Cells.Clear() cellExport5.SetColumnWidth(System.Convert.ToUInt16(1), System.Convert.ToUInt16(32)) cellExport5.SetColumnWidth(System.Convert.ToUInt16(2), System.Convert.ToUInt16(16)) cellExport5.SetColumnWidth(System.Convert.ToUInt16(3), System.Convert.ToUInt16(16)) cell = cellExport5.AddString(System.Convert.ToUInt16(1), System.Convert.ToUInt16(currentRow), "Examples of formulas :") currentRow += 1 cell.Format.Font.Bold = True cell.Format.FillStyle.Background = CellColor.LightGreen cell.Format.FillStyle.Pattern = Pattern.Solid cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium cell = cellExport5.AddString(System.Convert.ToUInt16(1), System.Convert.ToUInt16(currentRow), "Test data:") currentRow += 1 cell.Format.Font.Bold = True cell.Format.FillStyle.Background = CellColor.LightGreen cell.Format.FillStyle.Pattern = Pattern.Solid cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium 'test data cellExport5.AddNumeric(System.Convert.ToUInt16(2), System.Convert.ToUInt16(currentRow), 7.3) cellExport5.AddNumeric(System.Convert.ToUInt16(3), System.Convert.ToUInt16(currentRow), 5) cellExport5.AddNumeric(System.Convert.ToUInt16(4), System.Convert.ToUInt16(currentRow), 8.2) cellExport5.AddNumeric(System.Convert.ToUInt16(5), System.Convert.ToUInt16(currentRow), 4) cellExport5.AddNumeric(System.Convert.ToUInt16(6), System.Convert.ToUInt16(currentRow), 3) cellExport5.AddNumeric(System.Convert.ToUInt16(7), System.Convert.ToUInt16(currentRow), 11.3) currentRow += 1 currentRow += 1 cell = cellExport5.AddString(Convert.ToUInt16(1), Convert.ToUInt16(currentRow), "Formulas") cell.Format.Font.Bold = True cell.Format.FillStyle.Background = CellColor.LightGreen cell.Format.FillStyle.Pattern = Pattern.Solid cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium cell = cellExport5.AddString(secondCol, Convert.ToUInt16(currentRow), "Results") currentRow += 1 cell.Format.Font.Bold = True cell.Format.FillStyle.Background = CellColor.LightGreen cell.Format.FillStyle.Pattern = Pattern.Solid cell.Format.Borders.Bottom.Style = CellBorderStyle.Medium 'str. currentFormula = "=""hello""" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), "=""hello""") cellExport5.AddFormula(Convert.ToUInt16(3), Convert.ToUInt16(currentRow), "=""" & ChrW(20320) & ChrW(22909) + """") currentRow += 1 'int. currentFormula = "=300" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 ' float currentFormula = "=3389.639421" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 'bool. currentFormula = "=false" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=1+2+3+4+5-6-7+8-9" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=33*3/4-2+10" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 ' sheet reference currentFormula = "=Sheet1!$B$3" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 ' sheet area reference currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 ' Functions currentFormula = "=Count(3,5,8,10,2,34)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=NOW()" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=SECOND(11)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=MINUTE(12)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=MONTH(9)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=DAY(10)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=TIME(4,5,7)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=DATE(6,4,2)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=RAND()" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=HOUR(12)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=TEXT(""world"", ""$d"")" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=MOD(5,3)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=WEEKDAY(3)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=YEAR(23)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=NOT(true)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=OR(true)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=AND(TRUE)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=VALUE(30)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=LEN(""world"")" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=MID(""world"",4,2)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=ROUND(7,3)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=SIGN(4)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=INT(200)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=ABS(-1.21)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=LN(15)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=EXP(20)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=SQRT(40)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=PI()" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=COS(9)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=SIN(45)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=MAX(10,30)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=MIN(5,7)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=AVERAGE(12,45)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=SUM(18,29)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=IF(4,2,2)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)" cellExport5.AddString(firstCol, Convert.ToUInt16(currentRow), currentFormula) cellExport5.AddFormula(secondCol, Convert.ToUInt16(currentRow), currentFormula) currentRow += 1 cellExport5.SaveToFile() End Sub
How to export multiple data tables to different worksheets.
private void button1_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb"; System.Data.OleDb.OleDbCommand oleDbCommand1 = new System.Data.OleDb.OleDbCommand(); oleDbCommand1.CommandText = "select * from parts"; oleDbCommand1.Connection = oleDbConnection1; System.Data.OleDb.OleDbCommand oleDbCommand2 = new System.Data.OleDb.OleDbCommand(); oleDbCommand2.CommandText = "select * from country"; oleDbCommand2.Connection = oleDbConnection1; Spire.DataExport.XLS.CellExport cellExport1 = new Spire.DataExport.XLS.CellExport(); Spire.DataExport.XLS.WorkSheet workSheet1 = new Spire.DataExport.XLS.WorkSheet(); Spire.DataExport.XLS.StripStyle stripStyle1 = new Spire.DataExport.XLS.StripStyle(); Spire.DataExport.XLS.StripStyle stripStyle2 = new Spire.DataExport.XLS.StripStyle(); Spire.DataExport.XLS.WorkSheet workSheet2 = new Spire.DataExport.XLS.WorkSheet(); Spire.DataExport.XLS.StripStyle stripStyle3 = new Spire.DataExport.XLS.StripStyle(); Spire.DataExport.XLS.StripStyle stripStyle4 = new Spire.DataExport.XLS.StripStyle(); cellExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView; cellExport1.AutoFitColWidth = true; cellExport1.AutoFormula = true; cellExport1.DataFormats.CultureName = "zh-CN"; cellExport1.DataFormats.Currency = "$#,###,##0.00"; cellExport1.DataFormats.DateTime = "yyyy-M-d H:mm"; cellExport1.DataFormats.Float = "#,###,##0.00"; cellExport1.DataFormats.Integer = "#,###,##0"; cellExport1.DataFormats.Time = "H:mm"; cellExport1.FileName = "Sheets.xls"; cellExport1.SheetOptions.AggregateFormat.Font.Name = "Arial"; cellExport1.SheetOptions.CustomDataFormat.Font.Name = "Arial"; cellExport1.SheetOptions.DefaultFont.Name = "Arial"; cellExport1.SheetOptions.FooterFormat.Font.Name = "Arial"; cellExport1.SheetOptions.HeaderFormat.Font.Name = "Arial"; cellExport1.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; cellExport1.SheetOptions.HyperlinkFormat.Font.Name = "Arial"; cellExport1.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single; cellExport1.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left; cellExport1.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top; cellExport1.SheetOptions.NoteFormat.Font.Bold = true; cellExport1.SheetOptions.NoteFormat.Font.Name = "Tahoma"; cellExport1.SheetOptions.NoteFormat.Font.Size = 8F; cellExport1.SheetOptions.TitlesFormat.Font.Bold = true; cellExport1.SheetOptions.TitlesFormat.Font.Name = "Arial"; workSheet1.AutoFitColWidth = true; workSheet1.FormatsExport.CultureName = "zh-CN"; workSheet1.FormatsExport.Currency = "¥#,###,##0.00"; workSheet1.FormatsExport.DateTime = "yyyy-M-d H:mm"; workSheet1.FormatsExport.Float = "#,###,##0.00"; workSheet1.FormatsExport.Integer = "#,###,##0"; workSheet1.FormatsExport.Time = "H:mm"; stripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen; stripStyle1.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; stripStyle1.Font.Name = "Arial"; stripStyle2.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle2.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle2.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle2.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle2.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise; stripStyle2.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; stripStyle2.Font.Name = "Arial"; workSheet1.ItemStyles.Add(stripStyle1); workSheet1.ItemStyles.Add(stripStyle2); workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Col; workSheet1.Options.AggregateFormat.Font.Name = "Arial"; workSheet1.Options.CustomDataFormat.Font.Name = "Arial"; workSheet1.Options.DefaultFont.Name = "Arial"; workSheet1.Options.FooterFormat.Font.Name = "Arial"; workSheet1.Options.HeaderFormat.Font.Bold = true; workSheet1.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; workSheet1.Options.HeaderFormat.Font.Name = "Arial"; workSheet1.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; workSheet1.Options.HyperlinkFormat.Font.Name = "Arial"; workSheet1.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single; workSheet1.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left; workSheet1.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top; workSheet1.Options.NoteFormat.Font.Bold = true; workSheet1.Options.NoteFormat.Font.Name = "Tahoma"; workSheet1.Options.NoteFormat.Font.Size = 8F; workSheet1.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet1.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet1.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet1.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet1.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow; workSheet1.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; workSheet1.Options.TitlesFormat.Font.Bold = true; workSheet1.Options.TitlesFormat.Font.Name = "Arial"; workSheet1.SheetName = "parts"; workSheet1.SQLCommand = oleDbCommand1; workSheet1.StartDataCol = ((System.Byte)(0)); workSheet2.AutoFitColWidth = true; workSheet2.FormatsExport.CultureName = "zh-CN"; workSheet2.FormatsExport.Currency = "¥#,###,##0.00"; workSheet2.FormatsExport.DateTime = "yyyy-M-d H:mm"; workSheet2.FormatsExport.Float = "#,###,##0.00"; workSheet2.FormatsExport.Integer = "#,###,##0"; workSheet2.FormatsExport.Time = "H:mm"; stripStyle3.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle3.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle3.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle3.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle3.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen; stripStyle3.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; stripStyle3.Font.Name = "Arial"; stripStyle4.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle4.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle4.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle4.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; stripStyle4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise; stripStyle4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.White; stripStyle4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; stripStyle4.Font.Name = "Arial"; workSheet2.ItemStyles.Add(stripStyle3); workSheet2.ItemStyles.Add(stripStyle4); workSheet2.ItemType = Spire.DataExport.XLS.CellItemType.Col; workSheet2.Options.AggregateFormat.Font.Name = "Arial"; workSheet2.Options.CustomDataFormat.Font.Name = "Arial"; workSheet2.Options.DefaultFont.Name = "Arial"; workSheet2.Options.FooterFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; workSheet2.Options.FooterFormat.Font.Name = "Arial"; workSheet2.Options.HeaderFormat.Font.Bold = true; workSheet2.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; workSheet2.Options.HeaderFormat.Font.Name = "Arial"; workSheet2.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue; workSheet2.Options.HyperlinkFormat.Font.Name = "Arial"; workSheet2.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single; workSheet2.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left; workSheet2.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top; workSheet2.Options.NoteFormat.Font.Bold = true; workSheet2.Options.NoteFormat.Font.Name = "Tahoma"; workSheet2.Options.NoteFormat.Font.Size = 8F; workSheet2.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet2.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet2.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet2.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium; workSheet2.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow; workSheet2.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid; workSheet2.Options.TitlesFormat.Font.Bold = true; workSheet2.Options.TitlesFormat.Font.Name = "Arial"; workSheet2.SheetName = "country"; workSheet2.SQLCommand = oleDbCommand2; workSheet2.StartDataCol = ((System.Byte)(0)); cellExport1.Sheets.Add(workSheet1); cellExport1.Sheets.Add(workSheet2); cellExport1.SQLCommand = oleDbCommand1; cellExport1.GetDataParams += new Spire.DataExport.Delegates.DataParamsEventHandler(cellExport1_GetDataParams); oleDbConnection1.Open(); try { cellExport1.SaveToFile(); } finally { oleDbConnection1.Close(); } } private void cellExport1_GetDataParams(object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e) { if ((e.Sheet == 0) && (e.Col == 6)) { e.FormatText = (sender as Spire.DataExport.XLS.WorkSheet).ExportCell.DataFormats.Currency; } }
Private WithEvents cellExport1 As Spire.DataExport.XLS.CellExport Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim oleDbConnection1 As System.Data.OleDb.OleDbConnection oleDbConnection1 = New System.Data.OleDb.OleDbConnection oleDbConnection1.ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb" Dim oleDbCommand1 As System.Data.OleDb.OleDbCommand oleDbCommand1 = New System.Data.OleDb.OleDbCommand oleDbCommand1.CommandText = "select * from parts" oleDbCommand1.Connection = oleDbConnection1 Dim oleDbCommand2 As System.Data.OleDb.OleDbCommand oleDbCommand2 = New System.Data.OleDb.OleDbCommand oleDbCommand2.CommandText = "select * from country" oleDbCommand2.Connection = oleDbConnection1 Dim WorkSheet1 As Spire.DataExport.XLS.WorkSheet = New Spire.DataExport.XLS.WorkSheet Dim StripStyle1 As Spire.DataExport.XLS.StripStyle = New Spire.DataExport.XLS.StripStyle Dim StripStyle2 As Spire.DataExport.XLS.StripStyle = New Spire.DataExport.XLS.StripStyle Dim WorkSheet2 As Spire.DataExport.XLS.WorkSheet = New Spire.DataExport.XLS.WorkSheet Dim StripStyle3 As Spire.DataExport.XLS.StripStyle = New Spire.DataExport.XLS.StripStyle Dim StripStyle4 As Spire.DataExport.XLS.StripStyle = New Spire.DataExport.XLS.StripStyle cellExport1 = New Spire.DataExport.XLS.CellExport cellExport1.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView cellExport1.AutoFitColWidth = True cellExport1.AutoFormula = True cellExport1.DataFormats.CultureName = "zh-CN" cellExport1.DataFormats.Currency = "$#,###,##0.00" cellExport1.DataFormats.DateTime = "yyyy-M-d H:mm" cellExport1.DataFormats.Float = "#,###,##0.00" cellExport1.DataFormats.Integer = "#,###,##0" cellExport1.DataFormats.Time = "H:mm" cellExport1.FileName = "Sheets.xls" cellExport1.SheetOptions.AggregateFormat.Font.Name = "Arial" cellExport1.SheetOptions.CustomDataFormat.Font.Name = "Arial" cellExport1.SheetOptions.DefaultFont.Name = "Arial" cellExport1.SheetOptions.FooterFormat.Font.Name = "Arial" cellExport1.SheetOptions.HeaderFormat.Font.Name = "Arial" cellExport1.SheetOptions.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue cellExport1.SheetOptions.HyperlinkFormat.Font.Name = "Arial" cellExport1.SheetOptions.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single cellExport1.SheetOptions.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left cellExport1.SheetOptions.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top cellExport1.SheetOptions.NoteFormat.Font.Bold = True cellExport1.SheetOptions.NoteFormat.Font.Name = "Tahoma" cellExport1.SheetOptions.NoteFormat.Font.Size = 8.0! cellExport1.SheetOptions.TitlesFormat.Font.Bold = True cellExport1.SheetOptions.TitlesFormat.Font.Name = "Arial" WorkSheet1.AutoFitColWidth = True WorkSheet1.FormatsExport.CultureName = "zh-CN" WorkSheet1.FormatsExport.Currency = "¥#,###,##0.00" WorkSheet1.FormatsExport.DateTime = "yyyy-M-d H:mm" WorkSheet1.FormatsExport.Float = "#,###,##0.00" WorkSheet1.FormatsExport.Integer = "#,###,##0" WorkSheet1.FormatsExport.Time = "H:mm" StripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen StripStyle1.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid StripStyle1.Font.Name = "Arial" StripStyle2.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle2.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle2.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle2.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle2.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise StripStyle2.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid StripStyle2.Font.Name = "Arial" WorkSheet1.ItemStyles.Add(StripStyle1) WorkSheet1.ItemStyles.Add(StripStyle2) WorkSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Col WorkSheet1.Options.AggregateFormat.Font.Name = "Arial" WorkSheet1.Options.CustomDataFormat.Font.Name = "Arial" WorkSheet1.Options.DefaultFont.Name = "Arial" WorkSheet1.Options.FooterFormat.Font.Name = "Arial" WorkSheet1.Options.HeaderFormat.Font.Bold = True WorkSheet1.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue WorkSheet1.Options.HeaderFormat.Font.Name = "Arial" WorkSheet1.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue WorkSheet1.Options.HyperlinkFormat.Font.Name = "Arial" WorkSheet1.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single WorkSheet1.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left WorkSheet1.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top WorkSheet1.Options.NoteFormat.Font.Bold = True WorkSheet1.Options.NoteFormat.Font.Name = "Tahoma" WorkSheet1.Options.NoteFormat.Font.Size = 8.0! WorkSheet1.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium WorkSheet1.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium WorkSheet1.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium WorkSheet1.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium WorkSheet1.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow WorkSheet1.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid WorkSheet1.Options.TitlesFormat.Font.Bold = True WorkSheet1.Options.TitlesFormat.Font.Name = "Arial" WorkSheet1.SheetName = "parts" WorkSheet1.SQLCommand = oleDbCommand1 WorkSheet1.StartDataCol = CType(0, Byte) WorkSheet2.AutoFitColWidth = True WorkSheet2.FormatsExport.CultureName = "zh-CN" WorkSheet2.FormatsExport.Currency = "¥#,###,##0.00" WorkSheet2.FormatsExport.DateTime = "yyyy-M-d H:mm" WorkSheet2.FormatsExport.Float = "#,###,##0.00" WorkSheet2.FormatsExport.Integer = "#,###,##0" WorkSheet2.FormatsExport.Time = "H:mm" StripStyle3.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle3.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle3.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle3.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle3.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen StripStyle3.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid StripStyle3.Font.Name = "Arial" StripStyle4.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle4.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle4.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle4.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium StripStyle4.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightTurquoise StripStyle4.FillStyle.Foreground = Spire.DataExport.XLS.CellColor.White StripStyle4.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid StripStyle4.Font.Name = "Arial" WorkSheet2.ItemStyles.Add(StripStyle3) WorkSheet2.ItemStyles.Add(StripStyle4) WorkSheet2.ItemType = Spire.DataExport.XLS.CellItemType.Col WorkSheet2.Options.AggregateFormat.Font.Name = "Arial" WorkSheet2.Options.CustomDataFormat.Font.Name = "Arial" WorkSheet2.Options.DefaultFont.Name = "Arial" WorkSheet2.Options.FooterFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue WorkSheet2.Options.FooterFormat.Font.Name = "Arial" WorkSheet2.Options.HeaderFormat.Font.Bold = True WorkSheet2.Options.HeaderFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue WorkSheet2.Options.HeaderFormat.Font.Name = "Arial" WorkSheet2.Options.HyperlinkFormat.Font.Color = Spire.DataExport.XLS.CellColor.Blue WorkSheet2.Options.HyperlinkFormat.Font.Name = "Arial" WorkSheet2.Options.HyperlinkFormat.Font.Underline = Spire.DataExport.XLS.XlsFontUnderline.Single WorkSheet2.Options.NoteFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Left WorkSheet2.Options.NoteFormat.Alignment.Vertical = Spire.DataExport.XLS.VerticalAlignment.Top WorkSheet2.Options.NoteFormat.Font.Bold = True WorkSheet2.Options.NoteFormat.Font.Name = "Tahoma" WorkSheet2.Options.NoteFormat.Font.Size = 8.0! WorkSheet2.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Medium WorkSheet2.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Medium WorkSheet2.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Medium WorkSheet2.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Medium WorkSheet2.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow WorkSheet2.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid WorkSheet2.Options.TitlesFormat.Font.Bold = True WorkSheet2.Options.TitlesFormat.Font.Name = "Arial" WorkSheet2.SheetName = "country" WorkSheet2.SQLCommand = oleDbCommand2 WorkSheet2.StartDataCol = CType(0, Byte) cellExport1.Sheets.Add(WorkSheet1) cellExport1.Sheets.Add(WorkSheet2) cellExport1.SQLCommand = oleDbCommand1 oleDbConnection1.Open() Try cellExport1.SaveToFile() Finally oleDbConnection1.Close() End Try End Sub Private Sub cellExport1_GetDataParams(ByVal sender As Object, ByVal e As Spire.DataExport.EventArgs.DataParamsEventArgs) Handles cellExport1.GetDataParams If (e.Sheet = 0) AndAlso (e.Col = 6) Then e.FormatText = cellExport1.DataFormats.Currency End If End Sub
Published in
Cell
Tagged under