ImportCSV.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' CSV Import and Export: the workbook code is in App_Code\ImportCsvDemo.vb.
''' </summary>
Partial Class ImportCSVPage
Inherits Page
' AutoEventWireup="true" in the page directive connects Page_Load to the Load event
Protected Sub Page_Load(sender As Object, e As EventArgs)
End Sub
Protected Sub buttonCreate_Click(sender As Object, e As EventArgs)
Dim format As ExcelWorkbookFormat = If(radioXlsx.Checked, ExcelWorkbookFormat.Xlsx_2007, ExcelWorkbookFormat.Xls_2003)
Dim workbookBytes As Byte()
Try
' create the workbook in memory; the input files are in the folders of the web site
Using stream As New MemoryStream()
ImportCsvDemo.CreateWorkbook(format, Server.MapPath("~"), stream)
workbookBytes = stream.ToArray()
End Using
Catch ex As Exception
labelError.Text = "The workbook could not be created: " & Server.HtmlEncode(ex.Message)
Return
End Try
WorkbookDownload.Send(Response, workbookBytes, ImportCsvDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/ImportCSVDemo.vb
Imports System.Drawing
Imports System.Globalization
Imports System.IO
Imports System.Text
Imports System.Threading
Imports ExpertXls.ExcelLib
''' <summary>
''' Imports a CSV file into a new workbook, formats the imported table, and exports a worksheet back to CSV.
''' </summary>
Public Module ImportCsvDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "ImportFromCsv"
''' <summary>
''' Creates the workbook in the given format from Data\awemployees.csv and saves it to the output stream.
''' </summary>
''' <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
''' <param name="inputFolder">The folder that contains the Data and Images folders of the sample.</param>
''' <param name="output">The stream that receives the workbook.</param>
Public Sub CreateWorkbook(format As ExcelWorkbookFormat, inputFolder As String, output As Stream)
Dim workbook As ExcelWorkbook = LoadEmployees(format, inputFolder)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Import data from CSV"
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET"
Dim logo As Image = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"))
Try
Dim titleStyle As ExcelCellStyle = DemoLayout.AddTitleStyle(workbook)
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)
worksheet.Name = "Imported from CSV"
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(worksheet, "A2:N3", "Data Imported from a CSV File", titleStyle)
' the CSV header row was imported in row 5; the used range ends with the last imported row and column
Dim lastRow As Integer = worksheet.UsedRange.BottomRowIndex
Dim lastColumn As Integer = worksheet.UsedRange.RightColumnIndex
Dim headerRange As ExcelRange = worksheet(5, 1, 5, lastColumn)
headerRange.RowHeightInPoints = 20
headerRange.Style.Font.Bold = True
headerRange.Style.Fill.FillType = ExcelCellFillType.SolidFill
headerRange.Style.Fill.SolidFillOptions.BackColor = Color.LightBlue
headerRange.Style.Borders.LineStyle = ExcelCellLineStyle.Thin
' the numbers and dates of the CSV file were imported as numbers and dates: show the dates of the
' BirthDate, HireDate and ModifiedDate columns in the ISO format
worksheet(6, 7, lastRow, 7).Style.Number.NumberFormatString = "yyyy-mm-dd"
worksheet(6, 10, lastRow, 10).Style.Number.NumberFormatString = "yyyy-mm-dd"
worksheet(6, 14, lastRow, 14).Style.Number.NumberFormatString = "yyyy-mm-dd"
' keep the header visible when scrolling and fit the columns to the data
worksheet("A6").FreezePanes()
worksheet.AutofitColumns()
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
''' <summary>
''' Saves the first worksheet of a workbook created by this demo to a CSV stream, with ';' as the separator.
''' </summary>
''' <param name="workbookFile">A workbook saved by CreateWorkbook.</param>
''' <param name="csvOutput">The stream that receives the CSV text.</param>
Public Sub ExportToCsv(workbookFile As String, csvOutput As Stream)
Dim workbook As New ExcelWorkbook(workbookFile)
Try
' the worksheet index is zero based; the text is saved as UTF-8
workbook.SaveToCsv(0, csvOutput, ";", Encoding.UTF8)
Finally
workbook.Close()
End Try
End Sub
Private Function LoadEmployees(format As ExcelWorkbookFormat, inputFolder As String) As ExcelWorkbook
' the CSV fields that are numbers, dates or booleans become number, date and boolean cells (set
' ParseCSVStrings to False to import all the fields as text); fields of digits with leading zeros,
' like 00123, stay text
Dim settings As New ExcelWorkbookDefaultSettings()
settings.ParseCSVStrings = True
' the fields are parsed with the number and date formats of the current culture, and this CSV file
' uses the US formats (5/15/1972)
Dim threadCulture As CultureInfo = Thread.CurrentThread.CurrentCulture
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US")
Try
Using csvStream As New FileStream(Path.Combine(inputFolder, "Data", "awemployees.csv"), FileMode.Open, FileAccess.Read)
' the CSV text encoding, the field separator, and the row and column where the data starts;
' the first row of the CSV file (the column names) goes to row 5, column 1
Return ExcelWorkbook.LoadFromCsv(csvStream, Encoding.GetEncoding(1252), ",", 5, 1, format, settings)
End Using
Finally
Thread.CurrentThread.CurrentCulture = threadCulture
End Try
End Function
End Module