postgresql - F# SQLProvider 列顺序与表中的顺序不匹配

Question

我从 postgresql View \表中选择并将值导出到 excel 文件中。

excel文件的列顺序需要和表格一致，但是sqlProvider选择的是abc顺序...

我的代码是:

module ViewToExcel

open System
open System.IO
//open Microsoft.Office.Interop.Excel
open System.Drawing
open Npgsql
open FSharp.Data.Sql
open OfficeOpenXml
open Casaubon
open NpgsqlTypes

let [<Literal>] connectionString = @"Server=localhost;Database=db;User Id=postgres;Password=;"
let [<Literal>] npgPath = @"..\packages\Npgsql.3.1.7\lib\net451"

type sqlConnection = SqlDataProvider<ConnectionString = connectionString,
                                        DatabaseVendor = Common.DatabaseProviderTypes.POSTGRESQL,
                                        ResolutionPath = npgPath,
                                        IndividualsAmount = 1000,
                                        UseOptionTypes = true>
let functionParseViewToExcel (excelPath:string, serverName:string, dbName:string) =


        /////////////////////////////////Get Data Connection///////////////////////
        printf "connect to db\n"

        let connectionUserString = @"Server="+serverName+";Database="+dbName+";User Id=postgres;Password=;"

        let ctx = sqlConnection.GetDataContext(connectionUserString)

        let weekCalcView = ctx.Public.CcVibeWeeklyCalculations

       // weekCalcView|> Seq.toList

        let weekCalcViewSeq = ctx.Public.CcVibeWeeklyCalculations|> Seq.toArray

        ////////////////////////////////// Start Excel//////////////////////////////

        let newExcelFile = FileInfo(excelPath  + "cc_vibe_treatment_period_"+ DateTime.Today.ToString("yyyy_dd_MM")+".xlsx");

        if (newExcelFile.Exists) then
           newExcelFile.Delete();

        let pck = new ExcelPackage(newExcelFile);

        //Add the 'xxx' sheet
        let ws = pck.Workbook.Worksheets.Add("xxx");

        //printf "success to start the excel file\n"

        let mutable columNames = "blabla"
        for col in weekCalcViewSeq.[0].ColumnValues do
            let columnName = match col with |(a, _) -> a
            //printf "a %A\n" columnName
            let columnNamewithPsic = "," + columnName
            columNames <-  columNames + columnNamewithPsic
        ws.Cells.[1, 1].LoadFromText(columNames.Replace("blabla,",""))|> ignore

        ws.Row(1).Style.Fill.PatternType <- Style.ExcelFillStyle.Solid
        ws.Row(1).Style.Fill.BackgroundColor.SetColor(Color.FromArgb(170, 170, 170))  
        ws.Row(1).Style.Font.Bold <- true; 
        ws.Row(1).Style.Font.UnderLine <- true;

        let mutable subject = weekCalcViewSeq.[0].StudySubjectLabel.Value // in order to color the rows according to subjects
        let mutable color = 0
        for row in 1.. weekCalcViewSeq.Length do
            let mutable columValues = "blabla"
            for col in weekCalcViewSeq.[row-1].ColumnValues do
                let columnValue = match col with |(_, a) -> a
                //printf "a %A\n" columnValue
                match columnValue with
                | null -> columValues <-  columValues + "," + ""
                | _ -> columValues <-  columValues + "," + columnValue.ToString()            
            ws.Cells.[row + 1, 1].LoadFromText(columValues.Replace("blabla,",""))|> ignore

            /////////////////////Color the row according to subject///////////////


            if (weekCalcViewSeq.[row - 1].StudySubjectLabel.Value = subject) then
                 if (color = 0) then
                  ws.Row(row + 1).Style.Fill.PatternType <- Style.ExcelFillStyle.Solid
                  ws.Row(row + 1).Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255,255,204)) 
                 else
                  ws.Row(row + 1).Style.Fill.PatternType <- Style.ExcelFillStyle.Solid
                  ws.Row(row + 1).Style.Fill.BackgroundColor.SetColor(Color.White) 
            else
                 subject <- weekCalcViewSeq.[row - 1].StudySubjectLabel.Value
                 if (color = 0) then
                  color <- 1
                  ws.Row(row + 1).Style.Fill.PatternType <- Style.ExcelFillStyle.Solid
                  ws.Row(row + 1).Style.Fill.BackgroundColor.SetColor(Color.White)                 
                 else
                  color <- 0
                  ws.Row(row + 1).Style.Fill.PatternType <- Style.ExcelFillStyle.Solid
                  ws.Row(row + 1).Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255,255,204))

 pck.Save()

Excel 输出字段是:bloating_avg,caps_fail,caps_success,date_of_baseline_visit,discomfort_avg 等...

但是表格中的顺序不一样。

有人可以帮我吗？

谢谢!

Answer 1

您可以编写一个小的辅助函数来通过 npgqsl 提取字段(列)名称.之后，您可以使用这个列名列表来创建您的 Excel 表格。 getColNames 函数从 DataReader 获取它。显然，您可以进一步重构它，以获取表名作为参数等。

#r @"..\packages\SQLProvider.1.0.33\lib\FSharp.Data.SqlProvider.dll"
#r @"..\packages\Npgsql.3.1.7\lib\net451\Npgsql.dll"

open System
open FSharp.Data.Sql
open Npgsql
open NpgsqlTypes

let conn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=root;Database=postgres;Pooling=false")
conn.Open()
let cmd  = new NpgsqlCommand()
cmd.Connection <- conn

cmd.CommandText <- """ SELECT * FROM public."TestTable1" """ 
let recs = cmd.ExecuteReader()

let getColNames (recs:NpgsqlDataReader) =  
    let columns = recs.GetColumnSchema() |> Seq.toList 
    columns |> List.map (fun x -> x.BaseColumnName)

let colnames = getColNames recs
//val colnames : string list = ["ID"; "DT"; "ADAY"]

rec.Dispose()
conn.Dispose()

您可以看到列名不是按字母顺序排列的。您可以使用此列名称列表以正确的顺序获取记录。或者直接使用 Reader 对象而不使用类型提供程序。

编辑:使用记录映射表

也可以使用类型提供程序以所需的格式提取数据，方法是连接类型，然后使用 .MapTo<T> :

type DataRec = {
    DT:DateTime 
    ADAY:String 
    ID:System.Int64
    }

type sql = SqlDataProvider<dbVendor,connString2,"",resPath,indivAmount,useOptTypes>
let ctx = sql.GetDataContext()
let table1 = ctx.Public.TestTable1
let qry = query { for row in table1 do
                  select row} |> Seq.map (fun x -> x.MapTo<DataRec>())
qry |> Seq.toList

val it : DataRec list = [{DT = 2016/09/27 00:00:00; ADAY = "Tuesday"; ID = 8L;}; {DT = 2016/09/26 00:00:00; ADAY = "Monday"; ID = 9L;}; {DT = 2016/09/25 00:00:00; ADAY = "Sunday";