using System;
using System.Data;
using System.Windows.Forms;

using Spire.Xls;

namespace ExcelSheetToDataTable
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();
	             
	            workbook.LoadFromFile(@"..\..\parts.xls",ExcelVersion.Version97to2003);
	            //Initialize worksheet
	            Worksheet sheet = workbook.Worksheets[0];

                DataTable dataTable = sheet.ExportDataTable();
                this.dataGridView1.DataSource = dataTable;
        }
    }
}

Imports System.Data
Imports System.Windows.Forms

Imports Spire.Xls

Namespace ExcelSheetToDataTable
	Public Partial Class Form1
		Inherits Form
		Public Sub New()
			InitializeComponent()
		End Sub

		Private Sub button1_Click(sender As Object, e As EventArgs)
			Dim workbook As New Workbook()

			workbook.LoadFromFile("..\..\parts.xls", ExcelVersion.Version97to2003)
			'Initialize worksheet
			Dim sheet As Worksheet = workbook.Worksheets(0)

			Dim dataTable As DataTable = sheet.ExportDataTable()
			Me.dataGridView1.DataSource = dataTable
		End Sub
	End Class
End Namespace

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			//Initialize worksheet
			Worksheet sheet = workbook.Worksheets[0];

			sheet.InsertDataTable((DataTable)this.dataGrid1.DataSource,true,2,1,-1,-1);

			//Sets body style
			CellStyle oddStyle = workbook.Styles.Add("oddStyle");
			oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
			oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
			oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
			oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
			oddStyle.KnownColor = ExcelColors.LightGreen1;

			CellStyle evenStyle = workbook.Styles.Add("evenStyle");
			evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
			evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
			evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
			evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
			evenStyle.KnownColor = ExcelColors.LightTurquoise;

			foreach( CellRange range in  sheet.AllocatedRange.Rows)
			{
				if (range.Row % 2 == 0)
					range.CellStyleName = evenStyle.Name;
			    else
					range.CellStyleName = oddStyle.Name;
			}

			//Sets header style
			CellStyle styleHeader = sheet.Rows[0].Style;
			styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
			styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
			styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
			styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
			styleHeader.VerticalAlignment = VerticalAlignType.Center;
			styleHeader.KnownColor = ExcelColors.Green;
			styleHeader.Font.KnownColor = ExcelColors.White;
			styleHeader.Font.IsBold = true;

			sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = "\"$\"#,##0";
			sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat =  "\"$\"#,##0";

			sheet.AllocatedRange.AutoFitColumns();
			sheet.AllocatedRange.AutoFitRows();

			sheet.Rows[0].RowHeight = 20;

			workbook.SaveToFile("sample.xls");
			ExcelDocViewer( workbook.FileName );
		}



		private void Form1_Load(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			workbook.LoadFromFile(@"..\..\..\..\..\..\Data\DataTableSample.xls");
			//Initialize worksheet
			Worksheet sheet = workbook.Worksheets[0];

			this.dataGrid1.DataSource =  sheet.ExportDataTable();
		}


		private void ExcelDocViewer( string fileName )
		{
			try
			{
				System.Diagnostics.Process.Start(fileName);
			}
			catch{}
		}

		Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
			Dim workbook As Workbook = New Workbook()

			'Initialize worksheet
			Dim sheet As Worksheet = workbook.Worksheets(0)

			sheet.InsertDataTable(CType(Me.dataGrid1.DataSource, DataTable),True,2,1,-1,-1)

			'Sets body style
			Dim oddStyle As CellStyle = workbook.Styles.Add("oddStyle")
			oddStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			oddStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			oddStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			oddStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			oddStyle.KnownColor = ExcelColors.LightGreen1

			Dim evenStyle As CellStyle = workbook.Styles.Add("evenStyle")
			evenStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			evenStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			evenStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			evenStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			evenStyle.KnownColor = ExcelColors.LightTurquoise

			For Each range As CellRange In sheet.AllocatedRange.Rows
				If range.Row Mod 2 = 0 Then
					range.CellStyleName = evenStyle.Name
				Else
					range.CellStyleName = oddStyle.Name
				End If
			Next range

			'Sets header style
			Dim styleHeader As CellStyle = sheet.Rows(0).Style
			styleHeader.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
			styleHeader.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
			styleHeader.VerticalAlignment = VerticalAlignType.Center
			styleHeader.KnownColor = ExcelColors.Green
			styleHeader.Font.KnownColor = ExcelColors.White
			styleHeader.Font.IsBold = True

			sheet.Columns(sheet.AllocatedRange.LastColumn - 1).Style.NumberFormat = """$""#,##0"
			sheet.Columns(sheet.AllocatedRange.LastColumn - 2).Style.NumberFormat = """$""#,##0"

			sheet.AllocatedRange.AutoFitColumns()
			sheet.AllocatedRange.AutoFitRows()

			sheet.Rows(0).RowHeight = 20

			workbook.SaveToFile("sample.xls")
			ExcelDocViewer(workbook.FileName)
		End Sub


		Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
			Dim workbook As Workbook = New Workbook()

			workbook.LoadFromFile("..\..\..\..\..\..\Data\DataTableSample.xls")
			'Initailize worksheet
			Dim sheet As Worksheet = workbook.Worksheets(0)

			Me.dataGrid1.DataSource = sheet.ExportDataTable()
		End Sub


		Private Sub ExcelDocViewer(ByVal fileName As String)
			Try
				System.Diagnostics.Process.Start(fileName)
			Catch
			End Try
		End Sub

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
  			
			//Initialize worksheet
			workbook.LoadFromFile("../../../../../../Data/EditChartSample.xls",true);
			Worksheet sheet = workbook.Worksheets[0];

			//Writes chart data
			CreateChartData(sheet);

			workbook.SaveToFile("Sample.xls");
			ExcelDocViewer(workbook.FileName);
		}

		private void CreateChartData(Worksheet sheet)
		{
			//Jun
			sheet.Range["F6"].NumberValue = 6000;
			sheet.Range["F7"].NumberValue = 8000;
			sheet.Range["F8"].NumberValue = 9000;
			sheet.Range["F9"].NumberValue = 8500;

			//Aug
			sheet.Range["G6"].NumberValue = 4000;
			sheet.Range["G7"].NumberValue = 7000;
			sheet.Range["G8"].NumberValue = 2000;
			sheet.Range["G9"].NumberValue = 5000;

			sheet.Range["F6:F9"].Style.NumberFormat = "\"$\"#,##0";
			sheet.Range["G6:G9"].Style.NumberFormat = "\"$\"#,##0";
		}

		private void ExcelDocViewer( string fileName )
		{
			try
			{
				System.Diagnostics.Process.Start(fileName);
			}
			catch{}
		}

		Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
			Dim workbook As Workbook = New Workbook()

			'Initialize worksheet
			workbook.LoadFromFile("../../../../../../Data/EditChartSample.xls",True)
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'Writes chart data
			CreateChartData(sheet)

			workbook.SaveToFile("Sample.xls")
			ExcelDocViewer(workbook.FileName)
		End Sub

		Private Sub CreateChartData(ByVal sheet As Worksheet)
			'Jun
			sheet.Range("F6").NumberValue = 6000
			sheet.Range("F7").NumberValue = 8000
			sheet.Range("F8").NumberValue = 9000
			sheet.Range("F9").NumberValue = 8500

			'Aug
			sheet.Range("G6").NumberValue = 4000
			sheet.Range("G7").NumberValue = 7000
			sheet.Range("G8").NumberValue = 2000
			sheet.Range("G9").NumberValue = 5000

			sheet.Range("F6:F9").Style.NumberFormat = """$""#,##0"
			sheet.Range("G6:G9").Style.NumberFormat = """$""#,##0"
		End Sub

		Private Sub ExcelDocViewer(ByVal fileName As String)
			Try
				System.Diagnostics.Process.Start(fileName)
			Catch
			End Try
		End Sub