Skip to content

Latest commit

 

History

History
267 lines (239 loc) · 8.76 KB

README.md

File metadata and controls

267 lines (239 loc) · 8.76 KB
Raw

Hsu.Db.Export.Spreadsheet

dev preview main Nuke Build FreeSql OpenXml

A component that regularly exports database tables to spreadsheets every day, can export Excel and Csv files, and supports Excel templates.

Package Version

  • Hsu.Db.Export.Spreadsheet : library
  • Hsu.Db.Export.Spreadsheet.Hosting : template
Name Source Stable Preview
Hsu.Db.Export.Spreadsheet Nuget NuGet NuGet
Hsu.Db.Export.Spreadsheet MyGet MyGet MyGet
Hsu.Db.Export.Spreadsheet.Hosting Nuget NuGet NuGet
Hsu.Db.Export.Spreadsheet.Hosting MyGet MyGet MyGet

Getting Started

Install Packages

  • Hsu.Db.Export.Spreadsheet
  • FreeSql

Configure DependencyInjection

static void ConfigureServices(IServiceCollection services, IConfiguration configuration)
{
    services.AddDailySyncSpreadsheet();
    ConfigureFreeSql(services, configuration);
}

static void ConfigureFreeSql(IServiceCollection services, IConfiguration configuration)
{
    var freeSql = new FreeSqlBuilder()
        .UseConnectionString(DataType.MySql, configuration.GetConnectionString("Default"))
        .Build();

    freeSql.Aop.CommandAfter += (_, e) =>
    {
        if (e.Exception != null)
        {
            Log.Logger.Error("Message:{Message}\r\nStackTrace:{StackTrace}", e.Exception.Message, e.Exception.StackTrace);
        }

        Log.Logger.Debug("FreeSql>A>{Sql}", e.Command.CommandText);
    };
    
    freeSql.Aop.CommandBefore += (_, e) =>
    {
        Log.Logger.Debug("FreeSql>B>{Sql}", e.Command.CommandText);
    };

    services.AddSingleton(freeSql);
}

Add Export Options

  • Update ConnectionString
  • Update Export Options

Template only support Excel export

{
  "ConnectionStrings": {
    "Default": "Data Source=mysql.sqlpub.com;Port=3306;User ID=public;Initial Catalog=db_hsu_des;Charset=utf8;SslMode=none;Min pool size=1"
  },
  "Export": {
    "Spreadsheet": {
      "Trigger": "00:00:00",
      "Launch": true,
      "Interval": "00:00:30",
      "Timeout": "00:01:30",
      "Path": null,
      "Tables": [
        {
          "Name": "Employees",
          "Code": "employees",
          "Filter": "create_at",
          "Chunk": 5000,
          "AscOrder": true,
          "Output": "Csv",
          "Fields": [
            {
              "Name": "EmployeeNo",
              "Column": "emp_no",
              "Type": "Int32"
            },
            {
              "Name": "Birthdate",
              "Column": "birth_date",
              "Type": "DateTime",
              "Format": "yyyy-MM-dd"
            },
            {
              "Name": "First Name",
              "Column": "first_name",
              "Type": "String"
            },
            {
              "Name": "Last Name",
              "Column": "last_name",
              "Type": "String"
            },
            {
              "Name": "Gender",
              "Column": "gender",
              "Type": "String",
              "Format": "yyyy-MM-dd"
            },
            {
              "Name": "Hire Date",
              "Column": "hire_date",
              "Type": "DateTime",
              "Format": "yyyy-MM-dd"
            },
            {
              "Name": "Create At",
              "Column": "create_at",
              "Type": "DateTime"
            }
          ]
        },
        {
          "Name": "Titles",
          "Code": "titles",
          "Filter": "create_at",
          "Chunk": 5000,
          "AscOrder": true,
          "Output": "Xlsx",
          "Fields": [
            {
              "Name": "EmployeeNo",
              "Column": "emp_no",
              "Type": "Int32"
            },
            {
              "Name": "Title",
              "Column": "title",
              "Type": "String"
            },
            {
              "Name": "From Date",
              "Column": "from_date",
              "Type": "DateTime",
              "Format": "yyyy-MM-dd"
            },
            {
              "Name": "To Date",
              "Column": "to_date",
              "Type": "DateTime",
              "Format": "yyyy-MM-dd"
            },
            {
              "Name": "Create At",
              "Column": "create_at",
              "Type": "DateTime"
            }
          ]
        },
        {
          "Name": "Salaries",
          "Code": "salaries",
          "Filter": "create_at",
          "Chunk": 5000,
          "AscOrder": false,
          "Template": "ExportTemplate.xlsx",
          "Output": "Xlsx",
          "Fields": [
            {
              "Name": "EmployeeNo",
              "Column": "emp_no",
              "Property": "EmployeeNo",
              "Type": "Int32"
            },
            {
              "Name": "Salary",
              "Column": "salary",
              "Property": "Salary",
              "Type": "Int32",
              "Format": "0000.00"
            },
            {
              "Name": "From Date",
              "Column": "from_date",
              "Property": "FromDate",
              "Type": "DateTime",
              "Format": "yyyy-MM-dd"
            },
            {
              "Name": "To Date",
              "Column": "to_date",
              "Property": "ToDate",
              "Type": "DateTime",
              "Format": "yyyy-MM-dd"
            },
            {
              "Name": "Create At",
              "Column": "create_at",
              "Type": "DateTime"
            }
          ]
        }
      ]
    }
  }
}
  • Worker
  • Trigger : The time to sync tables to local
  • Launch : if true will execute once at startup
  • Interval : The time to wait for export mutil days
  • Timeout : The time to wait for table read operations
  • Path : The path that export file storage
  • Tables
  • Name : The name of the column, to display in header
  • Code : The column of the table
  • Filter : The date column to filter
  • Chunk : The size of the chunk per read from the database
  • AscOrder : Is ascending or descending
  • Output : Only Csv and Xlsx
  • Fields
  • Property : The name of the property for object to export,if null use Column
  • Type : The type of field, default is String
  • Nullable : The field is nullable
  • Template : The template excel file,only Xlsx output
  • Format : The format for IFormattable
  • Escape : if true escape the value, default is false

Template Format

The template format is Field.

Fields Type Format

  • Boolean
  • Byte
  • Char
  • Int16
  • Int32
  • Int64
  • SByte
  • UInt16
  • UInt32
  • UInt64
  • Single
  • Double
  • Decimal
  • String
  • DateTime
  • Enum

License

MIT