public void CloseDuringCopy()
{
// TODO: Check no broken connections were returned to the pool
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginBinaryImport("COPY data (field_text, field_int4) FROM STDIN BINARY");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginBinaryExport("COPY data (field_text, field_int2) TO STDIN BINARY");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) FROM STDIN BINARY");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) TO STDIN BINARY");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginTextImport("COPY data (field_text, field_int4) FROM STDIN");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginTextExport("COPY data (field_text, field_int4) TO STDIN");
}
}
/// <summary>
/// create an xlsx representation of a select
/// </summary>
/// <param name="conn"></param>
/// <param name="query"></param>
/// <param name="reprocess_header">if not null allow you to transform header</param>
/// <param name="convert_data">if not null allow you to custom parse to a type (int,double,datetime, etc ). for not processed header just return the functor argument string</param>
/// <param name="header_width">if not null allow you to customize column width</param>
/// <param name="auto_header_width">if not null allow you to autoset header width</param>
/// <param name="autofilter">if sets to true allow you to enable the column autofilter</param>
/// <param name="block_header">if sets to true allow you to block the header</param>
/// <param name="bold_header">if sets to true enables columns header to be bold</param>
public static XLWorkbook ToXlsx(this NpgsqlConnection conn,
string query,
Func <string, string> reprocess_header = null,
Func <string, Func <string, object> > convert_data = null,
Func <string, double?> header_width = null,
Func <string, bool> auto_header_width = null,
bool autofilter = true,
bool block_header = true,
bool bold_header = true)
{
var wb = new XLWorkbook();
IXLWorksheet ws = null;
string[] header = null;
using (var tr = conn.BeginTextExport($"COPY ({query}) TO STDOUT WITH DELIMITER ',' CSV HEADER"))
{
using (var csv = new CsvParser(tr, new CsvHelper.Configuration.CsvConfiguration()
{
Delimiter = ","
}))
{
header = csv.Read();
if (header == null)
{
return(null);
}
var rowdata = csv.Read();
if (rowdata == null)
{
return(null);
}
ws = wb.Worksheets.Add("data");
IXLCell cell = null;
var i_convert_data = new List <Func <string, object> >();
var row = 1;
var col = 1;
for (int i = 0; i < header.Length; ++i)
{
if (convert_data != null)
{
i_convert_data.Add(convert_data(header[i]));
}
else
{
i_convert_data.Add((s) => s);
}
cell = ws.Cell(row, col);
if (reprocess_header != null)
{
cell.Value = reprocess_header(header[i]);
}
else
{
cell.Value = header[i];
}
++col;
}
++row;
do
{
col = 1;
for (int i = 0; i < header.Length; ++i)
{
cell = ws.Cell(row, col);
cell.Value = i_convert_data[i](rowdata[i]);
++col;
}
++row;
rowdata = csv.Read();
} while (rowdata != null);
}
}
for (int i = 0; i < header.Length; ++i)
{
var col = i + 1;
if (header_width != null)
{
var q = header_width(header[i]);
if (q != null)
{
ws.Column(col).Width = q.Value;
}
}
if (auto_header_width != null)
{
if (auto_header_width(header[i]))
{
ws.Column(col).AdjustToContents();
}
}
if (bold_header)
{
ws.Cell(1, col).Style.Font.Bold = true;
}
}
if (block_header)
{
ws.SheetView.Freeze(1, 0);
}
if (autofilter)
{
ws.RangeUsed().SetAutoFilter();
}
return(wb);
}
