Por algún motivo no me puedo instalar el MS Office así que tuve que buscar alguna alternativa para poder seguir desarrollando con manejo de Excel sin interop, y di con esta magnifica librería, NPOI:
http://npoi.codeplex.com/Tomé los ejemplos oficiales en C# y escribí los siguientes ejemplos en VB.NET
Crear un workbook:
#Region " Create a WorkBook "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
' Create a sheet.
Dim sheet As ISheet = workbook.CreateSheet("Sheet A1")
' Create a cell.
Dim cell As ICell = sheet.CreateRow(0).CreateCell(0)
' Set cell value.
cell.SetCellValue("This is a test")
' Set the width of column A1.
sheet.SetColumnWidth(0, 50 * 256)
' Set the height of row A1.
sheet.CreateRow(0).Height = 200
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Create a Workbook Example.xlsx") workbook.Write(sw)
End Using
#End Region
Deinifir la cabecera y el pie de página:
#Region " Set Header and Footer "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
Dim sheet As ISheet = workbook.CreateSheet("Sheet1") ' Create a sheet.
With sheet
' Create a cell and add a value.
.CreateRow(0).CreateCell(1).SetCellValue("test")
' Set header text.
.Header.Left = HSSFHeader.Page
' Page is a static property of HSSFHeader and HSSFFooter.
.Header.Center = "This is a test sheet"
' Set footer text.
.Footer.Left = "Copyright NPOI Team"
.Footer.Right = "created by Tony Qu（瞿杰）"
End With
Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Header-Footer Example.xlsx") workbook.Write(sw)
End Using
#End Region
Añadir comentarios a una celda:
#Region " Add Comments "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
Dim sheet As ISheet = workbook.CreateSheet("some comments") ' Create the first sheet.
' Create the drawing patriarch. This is the top level container for all shapes including cell comments.
Dim patr As IDrawing = sheet.CreateDrawingPatriarch()
' Create a cell in row 3.
Dim cell1 As ICell = sheet.CreateRow(3).CreateCell(1)
cell1.SetCellValue(New XSSFRichTextString("Hello, World"))
' Create a richtext to use it in the comment.
Dim strComment As New XSSFRichTextString("This is saying you hello")
' Create the richtext font style.
Dim font As IFont = workbook.CreateFont()
With font
.FontName = "Arial"
.FontHeightInPoints = 10
.Boldweight = CShort(FontBoldWeight.Bold)
.Color = HSSFColor.Red.Index
End With
' Apply font style to the text in the comment.
strComment.ApplyFont(font)
' Create a comment, Anchor defines size and position of the comment in worksheet.
Dim comment1 As IComment = patr.CreateCellComment(New XSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5))
With comment1
' Set comment text.
.[String] = strComment
' Set comment author.
.Author = "Elektro"
' By default comments are hidden. This one is always visible.
.Visible = True
End With
'* The first way to assign comment to a cell is via CellComment method:
cell1.CellComment = comment1
'* The second way to assign comment to a cell is to implicitly specify its row and column.
'* Note: It is possible to set row and column of a non-existing cell.
comment1.Row = 3
comment1.Column = 1
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Comment Example.xlsx") workbook.Write(sw)
End Using
#End Region
Definir propiedades personalizadas:
#Region " Set Custom Properties "
' Create the excel workbook.
Dim workbook As XSSFWorkbook = New XSSFWorkbook()
Dim sheet As ISheet = workbook.CreateSheet("Sheet1") ' Create the first sheet.
' Get the properties.
Dim props As POIXMLProperties = workbook.GetProperties()
With props ' Set some default properties.
.CoreProperties.Title = "Properties Example"
.CoreProperties.Creator = "Elektro"
.CoreProperties.Created = DateTime.Now
End With
' Set a custom property.
If Not props.CustomProperties.Contains("My Property Name") Then
props.CustomProperties.AddProperty("My Property Name", "Hello World!")
End If
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Properties Example.xlsx") workbook.Write(sw)
End Using
#End Region
Rellenar el color de fondo de una celda:
#Region " Fill Cell Background "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
' Create a sheet.
Dim sheet As ISheet = workbook.CreateSheet("Sheet1")
' Create a cell.
Dim cell1 As ICell = sheet.CreateRow(0).CreateCell(0)
' Set the cell text.
cell1.SetCellValue("Hello")
' Set the Background Style.
Dim style As ICellStyle = workbook.CreateCellStyle()
With style
.FillForegroundColor = IndexedColors.Blue.Index
.FillPattern = FillPattern.BigSpots
.FillBackgroundColor = IndexedColors.Pink.Index
End With
' Fill the cell background.
cell1.CellStyle = style
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Fill background Example.xlsx") workbook.Write(sw)
End Using
#End Region
Añadir un hyperlink:
#Region " Add HyperLinks "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
Dim cell As ICell = Nothing
Dim sheet As ISheet = workbook.CreateSheet("Hyperlinks") ' Create the first sheet.
' Set the Hyperlink style.
Dim HyperLinkStyle As ICellStyle = workbook.CreateCellStyle()
Dim HyperLinkFont As IFont = workbook.CreateFont()
HyperLinkFont.Underline = FontUnderlineType.[Single]
HyperLinkFont.Color = HSSFColor.Blue.Index
HyperLinkStyle.SetFont(HyperLinkFont)
' Link to an URL.
Dim LinkURL As New XSSFHyperlink(HyperlinkType.Url) With {.Address = "http://poi.apache.org/"}
cell = sheet.CreateRow(0).CreateCell(0)
With cell
.SetCellValue("URL Link")
.Hyperlink = LinkURL
.CellStyle = HyperLinkStyle
End With
' Link to a file.
Dim LinkFile
As New XSSFHyperlink
(HyperlinkType.
File) With {.
Address = "link1.xls"} cell = sheet.CreateRow(1).CreateCell(0)
With cell
.SetCellValue("File Link")
.Hyperlink = LinkFile
.CellStyle = HyperLinkStyle
End With
' Link to an e-amil.
Dim LinkMail As New XSSFHyperlink(HyperlinkType.Email) With {.Address = "mailto:poi@apache.org?subject=Hyperlinks"}
With cell
cell = sheet.CreateRow(2).CreateCell(0)
.SetCellValue("Email Link")
.Hyperlink = LinkMail
.CellStyle = HyperLinkStyle
End With
' Link to a place in the workbook.
Dim LinkSheet As New XSSFHyperlink(HyperlinkType.Document) With {.Address = "'Target ISheet'!A1"}
Dim sheet2 As ISheet = workbook.CreateSheet("Target ISheet") ' Create a target sheet.
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell") ' Create a target cell.
With cell
cell = sheet.CreateRow(3).CreateCell(0)
.SetCellValue("Worksheet Link")
.Hyperlink = LinkSheet
.CellStyle = HyperLinkStyle
End With
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\HyperLink Example.xlsx") workbook.Write(sw)
End Using
#End Region
Establecer el estilo de fuente:
#Region " Set Font style "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
Dim sheet1 As ISheet = workbook.CreateSheet("Sheet1") ' Create the first sheet.
' Create a cell style.
Dim style1 As ICellStyle = workbook.CreateCellStyle()
' Create a font style.
Dim font1 As IFont = workbook.CreateFont()
With font1 ' underlined, italic, red color, fontsize=20
.Color = IndexedColors.Red.Index
.IsItalic = True
.Underline = FontUnderlineType.[Double]
.FontHeightInPoints = 20
End With
' bind font1 with style1
style1.SetFont(font1)
' Create a cell, add text, and apply the font.
Dim cell1 As ICell = sheet1.CreateRow(1).CreateCell(1)
With cell1
.SetCellValue("Hello World!")
.CellStyle = style1
End With
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Font-Style Example.xlsx") workbook.Write(sw)
End Using
#End Region
Establecer el tipo de fuente para texto con formato (rich text):
#Region " Set Font style RichText "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
Dim sheet1 As ISheet = workbook.CreateSheet("Sheet1") ' Create the first sheet.
' Create a cell with rich text.
Dim cell1 As ICell = sheet1.CreateRow(0).CreateCell(0)
' Create a richtext.
Dim richtext As New XSSFRichTextString("Microsoft OfficeTM")
' Create a font style.
Dim font1 As IFont = workbook.CreateFont()
With font1
.FontHeightInPoints = 12
End With
richtext.ApplyFont(0, 16, font1) ' apply font to "Microsoft Office".
' Create a font style.
Dim font2 As IFont = workbook.CreateFont()
With font2
.TypeOffset = FontSuperScript.Super
.IsItalic = True
.Color = IndexedColors.Blue.Index
.FontHeightInPoints = 8
End With
richtext.ApplyFont(16, 18, font2) ' apply font to "TM"
' Add the richtext into the cell.
cell1.SetCellValue(richtext)
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Font-Style RichText Example.xlsx") workbook.Write(sw)
End Using
#End Region
Añadir una tabla:
#Region " Add a Table "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
Dim sheet1 As XSSFSheet = DirectCast(workbook.CreateSheet("Sheet1"), XSSFSheet) ' Create the first sheet.
' Create a cell with text.
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample")
' Create a table.
Dim x As Integer = 1
For i As Integer = 1 To 15
Dim row As IRow = sheet1.CreateRow(i)
For j As Integer = 0 To 14
row.CreateCell(j).SetCellValue(System.Math.Max(System.Threading.Interlocked.Increment(x), x - 1))
Next j
Next i
Dim table As XSSFTable = sheet1.CreateTable()
table.Name = "Tabella1"
table.DisplayName = "Tabella1"
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Table Example.xlsx") workbook.Write(sw)
End Using
#End Region
Formatear el valor de una celda:
#Region " Format Cell Data "
Private Sub Test() Handles MyBase.Shown
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
' Create a sheet.
Dim sheet As ISheet = workbook.CreateSheet("Sheet1")
' Create the format instance.
Dim format As IDataFormat = workbook.CreateDataFormat()
' Increase the width of Column A.
sheet.SetColumnWidth(0, 5000)
' Create a row and put some cells in it. Rows are 0 based.
Dim cell1 As ICell = sheet.CreateRow(0).CreateCell(0)
Dim cell2 As ICell = sheet.CreateRow(1).CreateCell(0)
Dim cell3 As ICell = sheet.CreateRow(2).CreateCell(0)
Dim cell4 As ICell = sheet.CreateRow(3).CreateCell(0)
Dim cell5 As ICell = sheet.CreateRow(4).CreateCell(0)
Dim cell6 As ICell = sheet.CreateRow(5).CreateCell(0)
Dim cell7 As ICell = sheet.CreateRow(6).CreateCell(0)
' Format the cell values.
' [Cell1]
' Number format with 2 digits after the decimal point. eg: "1.20"
SetValueAndFormat(workbook, cell1, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00"))
' [Cell2]
' RMB currency format with comma. eg: "¥20,000"
SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0"))
' [Cell3]
' Scentific number format. eg: "3.15E+00"
SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00"))
' [Cell4]
' Percent format, 2 digits after the decimal point. eg: "99.33%"
SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%"))
' [Cell5]
' Phone number format. eg: "021-65881234"
SetValueAndFormat(workbook, cell5, 2165881234UI, format.GetFormat("000-00000000"))
' [Cell6]:
' Formula value with datetime style.
cell6.CellFormula = "DateValue(""2005-11-11"")+TIMEVALUE(""11:11:11"")"
Dim cellStyle6 As ICellStyle = workbook.CreateCellStyle()
cellStyle6.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm")
cell6.CellStyle = cellStyle6
' [Cell7]:
' Display current time in AM/PM format.
SetDate(workbook, cell7, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@"))
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Formula Example.xlsx") workbook.Write(sw)
End Using
End Sub
Private Shared Sub SetValueAndFormat(ByVal workbook As IWorkbook,
ByVal cell As ICell,
ByVal value As Double,
ByVal formatId As Short)
cell.SetCellValue(value)
Dim cellStyle As ICellStyle = workbook.CreateCellStyle()
cellStyle.DataFormat = formatId
cell.CellStyle = cellStyle
End Sub
Private Shared Sub SetDate(ByVal workbook As IWorkbook,
ByVal cell As ICell,
ByVal value As DateTime,
ByVal formatId As Short)
'set value for the cell
If Not value = Nothing Then
cell.SetCellValue(value)
End If
Dim cellStyle As ICellStyle = workbook.CreateCellStyle()
cellStyle.DataFormat = formatId
cell.CellStyle = cellStyle
End Sub
#End Region
Ocultar una fila o una columna:
#Region " Hide row or column "
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
' Create a sheet.
Dim sheet As ISheet = workbook.CreateSheet("Sheet1")
' Create some rows.
Dim r1 As IRow = sheet.CreateRow(0)
Dim r2 As IRow = sheet.CreateRow(1)
Dim r3 As IRow = sheet.CreateRow(2)
Dim r4 As IRow = sheet.CreateRow(3)
Dim r5 As IRow = sheet.CreateRow(4)
' Hide IRow 2.
r2.ZeroHeight = True
' Hide column C.
sheet.SetColumnHidden(2, True)
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Hide Row or Column Example.xlsx") workbook.Write(sw)
End Using
#End Region
Añadir una imagen:
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
' Create a sheet.
Dim sheet As ISheet = workbook.CreateSheet("PictureSheet")
' Create the drawing patriarch. This is the top level container for all shapes including cell comments.
Dim patriarch As IDrawing = sheet.CreateDrawingPatriarch()
' Create the anchor.
Dim anchor As New XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7)
anchor.AnchorType = 2
' Load the picture and get the picture index in the workbook.
Dim imageId As Integer = LoadImage("C:\Users\Administrador\Desktop\4t0n.png", workbook)
Dim picture As XSSFPicture = DirectCast(patriarch.CreatePicture(anchor, imageId), XSSFPicture)
' Reset the image to the original size.
' Note: Resize will reset client anchor you set.
'picture.Resize();
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Add Picture Example.xlsx") workbook.Write(sw)
End Using
Public Shared Function LoadImage(path As String, wb As IWorkbook) As Integer
Dim file As New FileStream
(path, FileMode.
Open, FileAccess.
Read) Dim buffer
As Byte() = New Byte(file.
Length - 1) {} Return wb.AddPicture(buffer, PictureType.JPEG)
End Function
Unir celdas:
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
' Create a sheet.
Dim sheet As ISheet = workbook.CreateSheet("Sheet1")
' Create a cell.
Dim cell As ICell = sheet.CreateRow(1).CreateCell(1)
cell.SetCellValue(New XSSFRichTextString("This is a test of merging"))
' Merge B2 cell with C2 cell.
sheet.AddMergedRegion(New CellRangeAddress(1, 1, 1, 2))
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Merge Cells Example.xlsx") workbook.Write(sw)
End Using
Proteger con contraseña:
' Create the excel workbook.
Dim workbook As IWorkbook = New XSSFWorkbook()
' Create a sheet.
Dim sheet As XSSFSheet = DirectCast(workbook.CreateSheet("Sheet A1"), XSSFSheet)
With sheet ' Lock accessing excel operations.
.LockFormatRows()
.LockFormatCells()
.LockFormatColumns()
.LockDeleteColumns()
.LockDeleteRows()
.LockInsertHyperlinks()
.LockInsertColumns()
.LockInsertRows()
End With
' Set the password to unprotect:
Dim password As String = "Your Password"
sheet.ProtectSheet(password)
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(".\Protect Cells Example.xlsx") workbook.Write(sw)
End Using
EDITO:Como leer un workbook:
' The existing workbook filepath.
Dim WorkBookFile As String = "C:\MyWorkBook.xlsx"
' Create the excel workbook instance.
Dim workbook As IWorkbook = Nothing
' Load the workbook.
Using
file As New IO.
FileStream(WorkBookFile, IO.
FileMode.
Open, IO.
FileAccess.
Read) workbook
= New XSSFWorkbook
(file) End Using
' Get the first sheet.
Dim sheet As ISheet = workbook.GetSheetAt(0)
' Get the first row.
Dim row As IRow = sheet.GetRow(0)
' Create a cell.
Dim cell As ICell = row.CreateCell(1)
' Get the cell value.
If String.IsNullOrEmpty(cell.StringCellValue) Then ' If value is emty then...
' Set cell value.
cell.SetCellValue("This is a test")
End If
' Save changes.
Using sw
As IO.
FileStream = IO.
File.
Create(WorkBookFile
) workbook.Write(sw)
End Using
Mostrar Mensajes
¡ Por lo demás me parece un aporte genial !