用C#将Excel文档导出到PostgreSQL数据库

83qze16e  于 2022-09-21  发布在  C#
关注(0)|答案(1)|浏览(201)

我正在开发一种功能,负责读取Excel文档,并将其数据导出到PostgreSQL。到目前为止,我有以下流程。在使用C#的ASP.NET MVC应用程序中,我生成了一个页面,请求它输入文件。

该文件被读取并保存在流对象中。然后,使用NPOI库,我将该文件转换为实体列表,该列表是根据Read中的Excel文档中的数据创建的。最后,完成列表后,通过Entity Framework将其保存到数据库。以下是目前有效的代码。

public async Task<ActionResult> Index([FromForm] IFormFile archivoExcel)
{
    try
    {
        if (archivoExcel.Length > 0)
        {
            Stream excelStream = archivoExcel.OpenReadStream();
            IWorkbook miExcel = null;
            if (Path.GetExtension(archivoExcel.FileName) == ".xlsx")
            {
                miExcel = new XSSFWorkbook(excelStream);
            }
            else
            {
                miExcel = new HSSFWorkbook(excelStream);
            }

            List<ManifiestoExcel> lstManifiestoExcel = new List<ManifiestoExcel>();
            var sheet = miExcel.GetSheetAt(0);
            for (int i = 1; i < sheet.PhysicalNumberOfRows; i++)
            {
                var sheetRow = sheet.GetRow(i);
                ManifiestoDetalle md = new ManifiestoDetalle();
                md.CodigoEntrega        = sheetRow.Cells[0].ToString();
                md.Pais                 = sheetRow.Cells[1].ToString();
                md.NombreCompleto       = sheetRow.Cells[2].ToString();
                md.CodArea              = Convert.ToInt32(sheetRow.Cells[3].ToString());
                md.Telefono             = Convert.ToInt32(sheetRow.Cells[4].ToString());
                md.Direccion1           = sheetRow.Cells[5].ToString();
                md.Direccion2           = sheetRow.Cells[6].ToString();
                md.Direccion3           = sheetRow.Cells[7].ToString();
                md.Region               = sheetRow.Cells[8].ToString();
                md.Comuna               = sheetRow.Cells[9].ToString();
                md.CodigoPostal         = Convert.ToInt32(sheetRow.Cells[10].ToString());
                md.RutDni               = sheetRow.Cells[11].ToString();
                md.DescripcionEnvio     = sheetRow.Cells[12].ToString();
                md.Precio               = Convert.ToInt32(sheetRow.Cells[13].ToString());
                lstManifiestoExcel.Add(md);
            }
            await _context.SaveChangesAsync();
        }
        else
        {
            ViewBag.Message = "Empty File Upload Failed";
        }
    }
    catch (Exception ex)
    {
        ViewBag.Message = "File Upload Failed";
    }
    return View(await _context.Manifiestos.ToListAsync());
}

此代码在记录很少的情况下运行良好。这个问题是产生的,当已经在生产时,有很多记录,因为它被卡住了…

在Python中,我做了测试,在Heroku中记录了30,000条这种类型的记录,加载时间不到10秒。我尝试从.NET Core应用程序运行Python脚本,但效果不是很好,因为使用这种方法不能使用NumPyPandas这样的库。

有没有办法从.NET Core在PostgreSQL中进行批量插入?我找过一些例子,但它们只出现在SQL Server上。

hc2pp10m

hc2pp10m1#

无论如何,如果您有很多记录,并且速度是您最关心的问题,我发现没有比使用导出/复制更快的方法来执行此操作,这意味着使用本机Excel的功能(与迭代行相比快得惊人)和Postgres的copy to命令(与逐行插入相比非常快)将文件导出到CSV。我甚至更进一步,在将文件发送到服务器之前对其进行压缩,以将网络影响降至最低。

这是Interop..。很少与速度联系在一起,但我要告诉你,Excel可以比任何第三方包更快地将工作表转换为CSV。

下面的代码是我所做的一个简化版本,你可以声明任何范围吗?这很有帮助,因为如果您的工作表包含您想要上载的数据之外的数据,那么您就不能使用本地导出到CSV。代码将该范围复制到空白工作表(并在稍后将其删除)以启用该功能。

将文件保存到CSV并压缩:

Excel.Range range = excel.Selection;
Excel.Workbook wb = excel.Workbooks.Add();
Excel.Worksheet ws = wb.Worksheets[1];

range.Copy();
ws.get_Range("A1").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats);
excel.DisplayAlerts = false;
wb.SaveAs(Path.Combine(_Outputdir, string.Format("{0}.csv", TableName)),
    Excel.XlFileFormat.xlCSV);
wb.Close();
excel.DisplayAlerts = true;

// Pick your favorite compress method -- this is optional
string newFile = Commons.Compress(_Outputdir, string.Format("{0}.csv", TableName));

将压缩的CSV文件发送到PG服务器并运行副本:

// Send this to the server however you normally would
Commons.FtpPut(newFile, _Outputdir);

NpgsqlTransaction trans = PgConnection.BeginTransaction(IsolationLevel.RepeatableRead);

if (TruncateTable)
{
    cmd = new NpgsqlCommand(string.Format("truncate table {0}", TableName),
        PgConnection, trans);
    cmd.ExecuteNonQuery();
}

try
{
    cmd.CommandText = string.Format(
        "copy {0} from program 'gzip -dc /apps/external_data/inbound/{0}.csv.gz' " +
        "with null as '' csv header encoding 'WIN1250'", TableName);

    cmd.ExecuteNonQuery();
    trans.Commit();
}
catch (Exception ex)
{
    // If the copy fails, roll back the truncate
    trans.Rollback();
}

PgConnection.Close();

// Clean up after yourself
Commons.FtpDelete(newFile, _Outputdir);

前提是您有能力访问服务器并运行复制,这是一个超级用户功能。如果您不能做到这一点,那么您可以用本地副本(在Npgsql上得到很好的支持)来替换它,但是这种方法会有很大的不同。

这是我的压缩方法,如果您想使用它的话:

public static string Compress(String Directory, String FileName)
{
    string newFileName = string.Format("{0}.gz", FileName);

    using (FileStream originalFileStream = File.Open(Path.Combine(Directory, FileName), FileMode.Open))
        using (FileStream compressedFileStream = File.Create(Path.Combine(Directory, newFileName)))
            using (GZipStream compressionStream = new GZipStream(compressedFileStream, CompressionMode.Compress))
                originalFileStream.CopyTo(compressionStream);

    return newFileName;
}

相关问题