Import CSV Files to Excel, PDF, DataGridView in C#

Files with the .csv extension are very similar to plain text files. CSV files are commonly used to transfer data from one database or spreadsheet format to another. This post gives examples of how to import data from CSV files into Excel worksheet, PDF form and DataGridView, by using .NET Excel component with C#.

CVS to Excel


//create a Workbook object
Workbook wb = new Workbook();

//load sample csv file
wb.LoadFromFile("SampleCSVFile.csv",",",1,1);

//save as excel file
wb.SaveToFile("toExcel.xlsx", ExcelVersion.Version2013);

2017-11-15_113237

CVS to PDF


//create a Workbook object
Workbook wb = new Workbook();

//load sample csv file
wb.LoadFromFile("SampleCSVFile.csv",",",1,1);

//render content of a worksheet into a single PDF page
wb.ConverterSetting.SheetFitToPage = true;

//autofit a column if the characters in the column exceed column width
Worksheet sheet = wb.Worksheets[0];
for (int i = 1; i < sheet.Columns.Length; i++)
{
    sheet.AutoFitColumn(i);
}

//save to PDF file
sheet.SaveToPdf("toPDF.pdf");

2017-11-15_113308

CVS to DataGridView


private void btnImport_Click(object sender, EventArgs e)
{
    //create a OpenFileDialog instance
    OpenFileDialog dialog = new OpenFileDialog();
    
    //set filter
    dialog.Filter = "CSV document(*.csv)|*.csv";

    //show dialog
    DialogResult result = dialog.ShowDialog();
    if (result == DialogResult.OK)
    {
        //get cvs file name
        string csvFile = dialog.FileName;

        //load csv to Wordbook instance
        Workbook wb = new Workbook();
        wb.LoadFromFile(csvFile,",");

        //export to datatable
        System.Data.DataTable datatable = wb.Worksheets[0].ExportDataTable();

        //display data in datagridview
        this.dataGridView1.DataSource = datatable;
    }
}

2017-11-15_113120

Advertisements

Insert Image and Formatted Text in Excel Header or Footer in C#

Excel header or footer, which presents information like page numbers, the creation date, the logo of your company, and even customized text, can be of use in printed worksheets. In this article, you’ll learn how to insert images and formatted text in Excel header or footer in C#.

This solution relies on Spire.XLS, an Excel processing library, which provides a PageSetup class to deal with all page setup settings. Specifically, it contains LeftHeader, CenterHeader, LeftHeaderImage, OddHeaderStringand similar properties that represent header/footer text, image, odd header/footer and even header/footer in Excel. Besides, this component provides some special script commands, which are used to get the dynamic value like page number or format the header or footer text.

Script D
&P The current page number
&N The total number of pages
&D The current data
&T The current time
&G A picture
&A The worksheet name
&F The file name
&B Make text bold
&I Italicize text
&U Underline text
&”font name” Represents a font name, for example, &”Aril”
&font size Represents font size, for example, &12
&K Represents font color, for example, &KFF0000

Insert Image to Header

  //create a word document
Workbook wb = new Workbook();

//get the first worksheet
Worksheet sheet = wb.Worksheets[0];

//load a pic to Image object
Image image = Image.FromFile("company-logo.jpg");

//scale the image
Bitmap bitmap = new Bitmap(image, new Size(image.Width / 4, image.Height / 4));

//insert image to left header
sheet.PageSetup.LeftHeaderImage = bitmap;
sheet.PageSetup.LeftHeader = "&G";

//save the file
wb.SaveToFile("Insert_Text_Footer.xlsx", ExcelVersion.Version2013);

2017-06-26_152908

Insert Formatted Text to Footer

 //create an object of Workbook class   
Workbook wb = new Workbook();

//get the first sheet  
Worksheet sheet = wb.Worksheets[0];

//format a string with script commands and assign it to CenterFooter   
sheet.PageSetup.CenterFooter = "&\"Arial\"&B&12&KFF0000Copyright © 2017 JMD. All Rights Reserved.";

//save the file
wb.SaveToFile("Insert_Text_Footer.xlsx", ExcelVersion.Version2013);

2017-06-26_153656

How to Create Sparkline Charts in Excel with C#

Sometimes you may want to analyze and view trends in your data without creating an entire chart. Sparklines are miniature charts that fit into a single cell. In this post, I am going to introduce how to programmatically create a sparkline chart in Excel from scratch.

This solution is also depending on Spire.XLS component. Do remember to add the Spire.Xls.dll as reference in your project to get following snippets executed.

using Spire.Xls;

namespace SparklineChart
{
    class Program
    {
        static void Main(string[] args)
        {
            //create an object of Workbook
            Workbook wb = new Workbook();
            wb.Version = ExcelVersion.Version2010;
            Worksheet ws = wb.Worksheets[0];

            //insert data
            ws.Range["A1"].Text = "Value01";
            ws.Range["B1"].Text = "Value02";
            ws.Range["C1"].Text = "Value03";
            ws.Range["D1"].Text = "Value04";
            ws.Range["E1"].Text = "Value05";
            ws.Range["A2"].NumberValue = 542.65;
            ws.Range["B2"].NumberValue = 682.65;
            ws.Range["C2"].NumberValue = 254.26;
            ws.Range["D2"].NumberValue = 953.25;
            ws.Range["E2"].NumberValue = 654.21;
            ws.Range["A3"].NumberValue = 782.45;
            ws.Range["B3"].NumberValue = 354.94;
            ws.Range["C3"].NumberValue = 987.24;
            ws.Range["D3"].NumberValue = 214.96;
            ws.Range["E3"].NumberValue = 825.14;
            ws.Range["A4"].NumberValue = 547.47;
            ws.Range["B4"].NumberValue = 244.94;
            ws.Range["C4"].NumberValue = 687.24;
            ws.Range["D4"].NumberValue = 614.96;
            ws.Range["E4"].NumberValue = 325.14;
            CellStyle cs = wb.Styles.Add("style");
            cs.Font.Size = 9;
            cs.Font.FontName = "Aril";
            cs.HorizontalAlignment = HorizontalAlignType.Justify;
            ws.Range[1, 1, 4, 5].Style = cs;

            //create sparkline chart based the data
            SparklineGroup sparklineGroup = ws.SparklineGroups.AddGroup(SparklineType.Column);
            sparklineGroup.SparklineColor = System.Drawing.Color.Blue;
            SparklineCollection sparklines = sparklineGroup.Add();
            sparklines.Add(ws["A2:E2"], ws["F2"]);
            sparklines.Add(ws["A3:E3"], ws["F3"]);
            sparklines.Add(ws["A4:E4"], ws["F4"]);
            ws.SetColumnWidth(6, 15f);

            //save to file
            wb.SaveToFile("SparklineChart-1.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("SparklineChart-1.xlsx");
        }
    }
}

Output

2016-05-04_171651

C# – Copy Rows and Columns in Excel

When we process the data within Excel sheets, it’s very common that we need to move or copy all data containing formulas, comments, cell formats from one cell rang to another. This article is focusing on how to achieve this task programmatically using Spire.XLS in C#.

In the following section, I built a console application in C# to explain the details. Before we start to code please add the Spire.Xls.dll as reference to your VS project.

A. Copy Rows and Columns

Step 1: Create a new instance of Workbook class and the sample document.

Workbook book = new Workbook();

book.LoadFromFile(“sample.xlsx”, ExcelVersion.Version2010);

Step 2: Get the sheet that contains data.

Worksheet sheet = book.Worksheets[0];

Step 3: Copy the data with formatting from one range to another by calling the method Worksheet.Copy(sourceRange, destRange, bool copyStyle).

sheet.Copy(sheet.Range[“A1:C1”], sheet.Range[“A9:C9”],true);

sheet.Copy(sheet.Range[“B4:B7”], sheet.Range[“D4:D7”], true);

Step 4: Save the file.

book.SaveToFile(“result.xlsx”, ExcelVersion.Version2010);

Output

CopyRow

B. Move the Data from One Range to Another

If you want to move the data from one row or column to another without maintaining the data in the previous row or column, just follow the steps in section A to copy and insert data to destination cells, then use following code to remove the data in previous cells.

sheet.Range[“A1:C1”].Clear(ExcelClearOptions.ClearAll);

sheet.Range[“B4:B7”].Clear(ExcelClearOptions.ClearAll);

Output

CutData

Entire Code

Part A:

Workbook book = new Workbook();

book.LoadFromFile(“test.xlsx”, ExcelVersion.Version2010);

Worksheet sheet = book.Worksheets[0];

 

sheet.Copy(sheet.Range[“A1:C1”], sheet.Range[“A9:C9”],true);

sheet.Copy(sheet.Range[“B4:B7”], sheet.Range[“D4:D7”], true);

 

book.SaveToFile(“result.xlsx”, ExcelVersion.Version2010);

System.Diagnostics.Process.Start(“result.xlsx”);

Part B:

Workbook book = new Workbook();

book.LoadFromFile(“test.xlsx”, ExcelVersion.Version2010);

Worksheet sheet = book.Worksheets[0];

 

sheet.Copy(sheet.Range[“A1:C1”], sheet.Range[“A9:C9”],true);

sheet.Copy(sheet.Range[“B4:B7”], sheet.Range[“D4:D7”], true);

 

sheet.Range[“A1:C1”].Clear(ExcelClearOptions.ClearAll);

sheet.Range[“B4:B7”].Clear(ExcelClearOptions.ClearAll);

 

book.SaveToFile(“result.xlsx”, ExcelVersion.Version2010);

System.Diagnostics.Process.Start(“result.xlsx”);

Create Combination Chart in C#

A combination chart in Excel is a chart that consists of bar and line chart in a single chart. As the value may vary from one category to another category, you can choose which category to be displayed as bar, which category to be displayed as line so that you can compare the values in different categories easily.

In the following section, I made a simply example to present how to create a combination chart via .NET Excel component in C#.

  1. Download the free Excel component, install it on your computer.

  2. Create a project in your VS studio, add the Spire.Xls.dll to the .NET assemblies.

  3. Using following code snippet to insert sample into Excel, and create bar and line chart based on the input data.

using Spire.Xls;

using Spire.Xls.Charts;

using System.Drawing;

namespace CreateCombinationChart

{

    class Program

    {

        static void Main(string[] args)

        {

            Workbook workbook = new Workbook();          

            Worksheet sheet=workbook.Worksheets[0];

            //insert sample data with formatting into the sheet

            sheet.Range[“A1”].Value = “Month”;

            sheet.Range[“A2”].Value = “Jan”;

            sheet.Range[“A3”].Value = “Feb”;

            sheet.Range[“A4”].Value = “Mar”;

            sheet.Range[“B1”].Value = “Visits”;

            sheet.Range[“B2”].Value = “548”;

            sheet.Range[“B3”].Value = “256”;

            sheet.Range[“B4”].Value = “471”;

            sheet.Range[“C1”].Value = “Revenue”;

            sheet.Range[“C2”].Value = “530”;

            sheet.Range[“C3”].Value = “340”;

            sheet.Range[“C4”].Value = “673”;

            sheet.Range[“C2:C4”].NumberFormat = “$#,##0.00”;

            sheet.Range[“D1”].Value = “Conversion
Rate”
;

            sheet.Range[“D2”].NumberValue = 0.027;

            sheet.Range[“D3”].NumberValue = 0.034;

            sheet.Range[“D4”].NumberValue = 0.092;

            sheet.Range[“D2:D4”].NumberFormat = “0.00%”;

            //add a chart based the data from A1 to D4

            Chart chart = sheet.Charts.Add();

            chart.DataRange = sheet.Range[“A1:D4”];

            chart.ChartTitle = “Combination Chart”;

            chart.SeriesDataFromRange = false;

            //set position of chart

            chart.LeftColumn = 1;

            chart.TopRow = 7;

            chart.RightColumn = 9;

            chart.BottomRow = 25;

            //apply different chart type to different series

            var cs1 = (ChartSerie)chart.Series[0];

            cs1.SerieType = ExcelChartType.ColumnClustered;

            var cs2 = (ChartSerie)chart.Series[1];

            cs2.SerieType = ExcelChartType.ColumnClustered;

            var cs3 = (ChartSerie)chart.Series[2];

            cs3.SerieType = ExcelChartType.LineMarkers;

 

            //plot series 3 to Y-axis
chart.SecondaryCategoryAxis.IsMaxCross = true;

            cs3.UsePrimaryAxis = false;

            //save and launch the file

            workbook.SaveToFile(“result.xlsx”,ExcelVersion.Version2010);

            System.Diagnostics.Process.Start(“result.xlsx”);

        }

    }

}

Output

2015-10-08