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(); }