使用GemBox.Spreadsheet控件操作Excel文件

最近在给cc帮忙做一个客户账单处理的小工具,需要打开xls编辑处理后再另存自定义样式的新xls文件,无意间发现了GemBox的这个控件,简直不能好用更多啊,就一个dll导入后不管目标机器上是否安装Excel或者WPS都可以直接在自己写的程序里面进行对xls或者xlsx文件的访问和处理.而且这货更凶残的地方在于可以直接一条命令将DataTable对象导入到xls文件的工作簿对象中,各种简单粗暴有效的操作,记录一下用到的操作:

主程序:

Imports System.Data.OleDb
Imports System.IO

Public Class Form1
    Private fileName As String  '加载的文件完整路径
    Private savePath As String  '已加载文件路径的目录路径
    Private conn As OleDbConnection
    ''' <summary>
    ''' 使用OleDb结合SQL语句加载文件并在Datagridview中显示,客户端需要下载安装AccessDatabaseEngine(07就够用了)
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            fileName = Me.OpenFileDialog1.FileName
            savePath = Path.GetDirectoryName(fileName)
            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & fileName & "; Extended Properties=""Excel 12.0 Xml; HDR=Yes;IMEX=1""")

            Try
                conn.Open()
                Dim sql As String = "select * from ['发布汇总表(系统导出)$']"    '中文工作簿名称需要在中文末尾加$符号并且用单引号括起来,再用[]括起来这才是完整的表名的写法
                Dim cmd As New OleDbCommand(sql, conn)
                Dim adp As New OleDbDataAdapter(cmd)
                Dim dt As New DataTable
                adp.Fill(dt)
                Me.DataGridView1.DataSource = dt
                conn.Close()
            Catch ex As Exception
                MsgBox("请将程序导出的原始文件使用Excel或WPS打开后另存为xls格式文件再用本程序打开另存的那个新文件.")
            End Try
        End If
    End Sub

    ''' <summary>
    ''' 处理并导出新文件
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Dim dts As List(Of DataTable) = getClients()
        Directory.CreateDirectory(savePath + "\整理导出")
        For Each dt As DataTable In dts
            com.dt2excel(dt, savePath + "\整理导出\" + dt.TableName + ".xls")
        Next
        MsgBox("导出完毕,点击确定打开导出的文件夹.", MsgBoxStyle.OkCancel, "提示")

        Process.Start("explorer.exe", savePath + "\整理导出\")
    End Sub


    ''' <summary>
    ''' 获取客户名称List
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function getClientNames() As List(Of String)
        Dim names As New List(Of String)
        Dim sql As String = "select distinct 客户名称 from ['发布汇总表(系统导出)$'] where 客户名称<>'' order by 客户名称"
        conn.Open()
        Dim cmd As New OleDbCommand(sql, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader
        While reader.Read
            names.Add(reader.Item(0))
        End While
        conn.Close()
        Return names
    End Function

    ''' <summary>
    ''' 获取所有客户原始数据Datatable数组
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function getClients() As List(Of DataTable)
        Dim names As List(Of String) = getClientNames()
        Dim dts As New List(Of DataTable)
        conn.Open()
        For Each n As String In names
            Dim sql As String = "select * from ['发布汇总表(系统导出)$'] where 客户名称=@cName"
            Dim cmd As New OleDbCommand(sql, conn)
            cmd.Parameters.AddWithValue("cName", n)
            Dim dt As New DataTable(n)
            Dim adp As New OleDbDataAdapter(cmd)
            adp.Fill(dt)
            corectData(dt)  '矫正处理费
            dts.Add(dt)
        Next
        conn.Close()
        Return dts
    End Function
    ''' <summary>
    ''' 矫正处理费
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <remarks></remarks>
    Private Sub corectData(ByVal dt As DataTable)
        Dim zje, zzl, lf, lm, yh, clf As Decimal
        For Each r As DataRow In dt.Rows
            'r("处理费") = (Double.Parse(r("总金额")) / Integer.Parse(r("总质量"))) - Double.Parse(r("拉幅单价")) - Double.Parse(r("拉毛单价")) - Double.Parse(r("印花单价"))
            zje = Decimal.Parse(r("总金额"))
            zzl = Integer.Parse(r("总质量"))
            lf = Decimal.Parse(r("拉幅单价"))
            lm = Decimal.Parse(r("拉毛单价"))
            yh = Decimal.Parse(r("印花单价"))
            clf = zje / zzl - lf - lm - yh
            r("处理费") = clf
        Next
        '删除不需要的列
        With dt.Columns
            .Remove("客户名称")
            .Remove("发布人")
            .Remove("承运人")
            .Remove("发布时间")
        End With
    End Sub
End Class

模块com.vb

Imports GemBox.Spreadsheet
Module com
    ''' <summary>
    ''' datatable导出到xls文件
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="filePath"></param>
    ''' <remarks></remarks>
    Public Sub dt2excel(ByVal dt As DataTable, ByVal filePath As String)
        '获取dt对象中所有记录的起止月份
        Dim SEdate As List(Of String)
        SEdate = getStartEndTime(dt)

        Dim exFile As New ExcelFile '定义GemBox中的Excel文件对象
        exFile.Worksheets.Add("sheet1") '在xls文件中添加工作簿(至少需要有一个工作簿才能算一个完整的xls嘛)
        exFile.Worksheets("sheet1").InsertDataTable(dt, New InsertDataTableOptions("A1") With {.ColumnHeaders = True})  '直接将datatable对象插入到工作簿中,这个方法简直碉堡了
        '自动调整列宽,这里尝试使用for each对所有列都自动调整发现并没有卵用,反正也不多,就一条一条写了
        exFile.Worksheets("sheet1").Columns(0).AutoFit()
        exFile.Worksheets("sheet1").Columns(1).AutoFit()
        exFile.Worksheets("sheet1").Columns(2).AutoFit()
        exFile.Worksheets("sheet1").Columns(3).AutoFit()
        exFile.Worksheets("sheet1").Columns(4).AutoFit()
        exFile.Worksheets("sheet1").Columns(5).AutoFit()
        With exFile.Worksheets("sheet1")
            .Rows.InsertEmpty(0, 2)     '在0行(首行)位置处插入3个空白行
            .Cells.GetSubrange("A1", "M1").Merged = True    '对A1:M1区域进行合并单元格操作,这是标题行
            '.Cells.GetSubrangeAbsolute(0,1,12,0).Merged=True    '这是绝对位置的写法,(开始单元格行号,开始单元格列号,结束单元格行号,结束单元格列号)
            .Cells(0, 0).Value = dt.TableName + " - 往来账单月汇总表" '设置合并后的单元格内容
            With .Cells(0, 0).Style
                .Font.Size = 24 * 20    '系统中的24号字体,需要x20
                .Font.Weight = 700  '700是bold加粗的值
                .HorizontalAlignment = HorizontalAlignmentStyle.Center
                .VerticalAlignment = VerticalAlignmentStyle.Center
            End With

            .Cells.GetSubrangeAbsolute(1, 0, 1, 12).Merged = True   '这是第二行显示日期
            .Cells(1, 0).Value = SEdate(0) & "-" & SEdate(1)
            With .Cells(1, 0).Style
                .Font.Size = 11 * 20
                .Font.Weight = 700
                .HorizontalAlignment = HorizontalAlignmentStyle.Right
                .VerticalAlignment = VerticalAlignmentStyle.Center
            End With

        End With
        exFile.Save(filePath)
    End Sub

    ''' <summary>
    ''' 从Datatable中获取当前账单的起止日期
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function getStartEndTime(ByVal dt As DataTable) As List(Of String)
        Dim startM, endM, startTime, endTime As String
        Dim ret As New List(Of String)
        startM = dt.Rows(0)("日期").ToString.Split("-")(0)    '第一条记录的月份
        endM = dt.Rows(dt.Rows.Count - 1)("日期").ToString.Split("-")(0)  '最后一条记录的月份
        startTime = Now.Year & "年" & startM & "月" & "1日"
        endTime = Now.Year & "年" & endM & "月" & getLastDayofMonth(endM) & "日"
        ret.Add(startTime)
        ret.Add(endTime)
        Return ret
    End Function

    ''' <summary>
    ''' 获取月份的最后一天
    ''' </summary>
    ''' <param name="mont"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function getLastDayofMonth(ByVal mont As String) As String
        If mont = "1" Or mont = "3" Or mont = "5" Or mont = "7" Or mont = "8" Or mont = "10" Or mont = "12" Then
            Return "31"
        ElseIf mont = "2" Then
            Return "28"
        Else
            Return "30"
        End If
    End Function
End Module

主程序界面:

待导入数据:

导出的数据: