public static async Task DataTableToExcel(ConnectionModel cnn, string tableName, string fileName, string sheetName = "sheet1")
{
await Task.Yield();
OnStart?.Invoke();
var dbObj = DBObjectHelper.GetDBObject(cnn);
var reader = dbObj.GetDataReader(cnn.Database, $"select * from {tableName}");
var filedCount = reader.FieldCount;
if (string.IsNullOrEmpty(sheetName) || filedCount < 1)
{
return;
}
var workbook = new HSSFWorkbook();//创建Workbook对象
var styleTitle = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;//加粗
styleTitle.SetFont(font);
//创建工作表
ISheet sheet = workbook.CreateSheet(sheetName);
//在工作表中添加一行
IRow firstRow = sheet.CreateRow(0);
for (int i = 0; i < filedCount; i++)
{
ICell cell = firstRow.CreateCell(i);
cell.CellStyle = styleTitle;
cell.SetCellValue(reader.GetName(i));
}
var rowCount = 0;
while (reader.Read())
{
rowCount++;
IRow row = sheet.CreateRow(rowCount); //在工作表1中添加一行
for (int j = 0; j < filedCount; j++)
{
var rowValue = reader[j] == null ? "" : reader[j].ToString();
row.CreateCell(j).SetCellValue(rowValue);
}
OnRunning?.Invoke(rowCount, filedCount * rowCount);
}
;
using (var fs = File.Open(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
{
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Position = 0;
var buffer = ms.ToArray();
await fs.WriteAsync(buffer, 0, buffer.Length);
}
}
OnStop?.Invoke();
}
/// <summary>
/// 大数据转换
/// </summary>
/// <param name="cnn"></param>
/// <param name="tableName"></param>
/// <param name="fileName"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task CSV(ConnectionModel cnn, string tableName, string fileName, CancellationToken cancellationToken = default(CancellationToken))
{
OnStart?.Invoke();
var dbObj = DBObjectHelper.GetDBObject(cnn);
var reader = dbObj.GetDataReader(cnn.Database, $"select * from {tableName}");
var header = new List <string>();
var filedCount = reader.FieldCount;
if (filedCount > 0)
{
for (int i = 0; i < filedCount; i++)
{
header.Add(reader.GetName(i));
}
}
using (StringWriter sw = new StringWriter())
{
StringBuilder sb = new StringBuilder();
char c = ',';
for (int i = 0; i < filedCount; i++)
{
sb.Append(header[i] + c);
}
sb.Append("\r\n");
var rowCount = 1;
while (reader.Read())
{
rowCount++;
for (int j = 0; j < filedCount; j++)
{
try
{
sb.Append(ConvertToSaveCell(reader[j]));
OnRunning?.Invoke(filedCount * j + rowCount, filedCount * rowCount);
}
catch
{
sb.Append(c);
}
}
sb.Append("\r\n");
}
sw.Write(Encoding.UTF8.GetString(new byte[] { (byte)0xEF, (byte)0xBB, (byte)0xBF }));
sw.Write(sb);
using (var fs = File.Open(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
{
using (StreamWriter ssw = new StreamWriter(fs))
{
await ssw.WriteAsync(sb.ToString());
}
}
}
OnStop?.Invoke();
}
