/// <summary> /// 直接从dataReader中读取第i列的日期值,如果值为空,则返回DateTime.MinValue /// </summary> /// <param name="dataReader">要从中读取数据的dataReader</param> /// <param name="i">dataReader中的列索引</param> /// <returns></returns> public static Byte GetImage(System.Data.IDataReader dataReader, int i) { if (dataReader.IsDBNull(i)) { return(Byte.MinValue); } return(dataReader.GetByte(i)); }
public static byte Byte(System.Data.IDataReader reader, MapPoint mapPoint, byte defaultValue = 0) { try { return(mapPoint.IsInResultSet() && !reader.IsDBNull(mapPoint.Index) ? reader.GetByte(mapPoint.Index) : defaultValue); } catch (Exception) { throw; } }
public void GenerateXlsxReport(System.Data.Common.DbConnection conn) { SetDbParams(conn); using (ExcelPackage package = (templateFile != null ? new ExcelPackage(newFile, templateFile) : new ExcelPackage(newFile))) { foreach (var tab in def.tabs) { var ws = GetWs(package, tab); var riadok = 1; var stlpec = 1; var start = 1; // set start position for Label - that would be Global or Local if (templateFile != null) { ExcelNamedRange label = null; try { label = ws.Names["Label"]; } catch { //Console.WriteLine("{0} Exception caught.", e); try { label = package.Workbook.Names["Label"]; } catch { SimpleLog.WriteLog("Label field not found in this workbook/template"); } } if (label != null) { riadok = label.Start.Row; stlpec = label.Start.Column; } } var nadpis = ""; if (!(tab.title == null || tab.title == "")) { if (tab.title.Trim().ToUpper().Substring(0, 6) == "SELECT") { nadpis = conn.QuerySingle <string>(tab.title, QuerryArguments(tab.title)); } else { nadpis = tab.title; } } // Main Select // https://github.com/ericmend/oracleClientCore-2.0/blob/master/test/dotNetCore.Data.OracleClient.test/OracleClientCore.cs System.Data.IDataReader reader = conn.ExecuteReader(tab.query, QuerryArguments(tab.query)); List <TableFields> rowConfig = GetDeclaration(reader, tab.fields); outDef.tabs.Add(new Tab { name = tab.name, title = tab.title, query = tab.query, fields = rowConfig }); int r = 0; int activeColCount = 0; while (reader.Read()) { r++; //Initial section for sheet if (r == 1) { if (nadpis != null && nadpis != "") { ws.Cells[riadok, stlpec].Value = nadpis; if (templateFile == null) { using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, stlpec - 1 + rowConfig.Where(o => o.order != 0).Count()]) { rr.Merge = true; //rr.Style.Font.SetFromFont(new Font("Britannic Bold", 12, FontStyle.Italic)); rr.Style.Font.Size = 12; rr.Style.Font.Bold = true; rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63)); rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; rr.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93)); rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242)); } } riadok++; } // set Data position if (templateFile != null) { ExcelNamedRange label = null; try { label = ws.Names["Data"]; } catch { try { label = package.Workbook.Names["Data"]; } catch { SimpleLog.WriteLog("Data field not found in this workbook/template"); } } if (label != null) { riadok = label.Start.Row - 1; // Header je nad riadkom (above row) stlpec = label.Start.Column; } } //Add the headers for (int i = 0; i < rowConfig.Count; i++) { if (rowConfig[i].order != 0) { activeColCount++; if (templateFile == null || tab.printHeader) { ws.Cells[riadok, activeColCount + stlpec - 1].Value = rowConfig[i].title; } ws.Names.Add(rowConfig[i].name, ws.Cells[riadok, activeColCount + stlpec - 1]); } } //Ok now format the values; //ws.Cells[1, 1, 1, tab.fields.Count].Style.Font.Bold = true; //Font should be bold //ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.BackgroundColor.SetColor(Color.Aqua); if (templateFile == null || tab.printHeader) { using (var range = ws.Cells[riadok, stlpec, riadok, activeColCount + stlpec - 1]) { range.Style.Font.Color.SetColor(Color.White); range.Style.Fill.PatternType = ExcelFillStyle.Solid; //range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228)); range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); range.Style.Font.Bold = true; range.Style.WrapText = true; //Only need to grab the first cell of the merged range //ws.Cells[$"A{row}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //ws.Cells[$"A{row}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } } riadok++; // set line for data start = riadok; } //data section, FETCH RECORDS for (int i = 0; i < rowConfig.Count(); i++) { var colId = rowConfig[i].colId; if (rowConfig[i].order != 0 && !reader.IsDBNull(colId)) { var a = reader.GetValue(colId); switch (rowConfig[i].type) { case "String": var pom = reader.GetString(colId); ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, pom.Substring(0, pom.Length /*- correctStringChars */)); break; case "Integer": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetInt32(colId)); break; case "DateTime": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId)); break; case "Date": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId)); break; case "Decimal": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDecimal(colId)); break; case "Byte[]": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetByte(colId)); break; default: ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetValue(colId).ToString()); break; } ; } } riadok++; } // no rows if (r == 0) { if (nadpis != null) { ws.Cells[riadok, stlpec].Value = nadpis; using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, 8 + stlpec]) { rr.Merge = true; rr.Style.Font.Size = 12; rr.Style.Font.Bold = true; rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63)); rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; rr.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242)); } riadok++; } ws.Cells[riadok, stlpec].Value = "No rows in querry result"; SimpleLog.WriteLog("No rows in Query from " + tab.name); } else { foreach (var row in rowConfig.Where(o => o.format != null && o.format != "auto")) { ws.Cells[start, row.order + stlpec - 1, riadok, row.order + stlpec - 1].Style.Numberformat.Format = row.format; } //ws.Cells[1, 1, Rows, 1].Style.Numberformat.Format = "#,##0"; //ws.Cells[1, 3, Rows, 3].Style.Numberformat.Format = "YYYY-MM-DD"; //ws.Cells[1, 4, Rows, 5].Style.Numberformat.Format = "#,##0.00"; // add comp fields // worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress); // Autofit if (templateFile == null) { ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFitColumns(); foreach (var row in rowConfig.Where(o => o.order != 0)) { if (ws.Column(row.order).Width < row.minsize) { ws.Column(row.order).Width = row.minsize; } } } //Create an autofilter(global settings) for the range if (def.autofilter) { ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFilter = true; } } if (enabledTimestamp && def.timestamp) { ws.Cells[riadok + 2, stlpec].Value = "Created : " + DateTime.Now.ToString("dd.MM.yyyy H:mm:ss"); } } package.Workbook.Calculate(); // Set document properties package.Workbook.Properties.Comments = "Created with EpSqlGen Copyright © 2018 Miroslav Dubovsky"; package.Workbook.Properties.Created = DateTime.Now; package.Workbook.Properties.Title = outFileName; var pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Author"); if (pomProp != null) { package.Workbook.Properties.Author = pomProp; } pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Company"); if (pomProp != null) { package.Workbook.Properties.Company = pomProp; } if (def.version != null) { package.Workbook.Properties.Subject = "Template " + defFileName + ", version:" + def.version; } else { package.Workbook.Properties.Subject = "Template " + defFileName; } package.Save(); } if (!enabledConsoleLog) { Console.Write(JsonConvert.SerializeObject(new { output_file = newFile.FullName }, Formatting.Indented)); } else { SimpleLog.WriteLog("Generated file: " + newFile.FullName); } }
public void SetFromReader <T>(T obj, System.Data.IDataReader reader, Action <T, string, object> setMethod) where T : class { if (!reader.IsDBNull(this.Index)) { switch (this.SqlType) { case System.Data.SqlDbType.BigInt: setMethod(obj, FieldName, reader.GetInt64(this.Index)); break; case System.Data.SqlDbType.Image: case System.Data.SqlDbType.VarBinary: case System.Data.SqlDbType.Binary: long size = reader.GetBytes(this.Index, 0, null, 0, 0); byte[] values = new byte[size]; int bufferSize = 1024; long bytesRead = 0; int curPos = 0; while (bytesRead < size) { bytesRead += reader.GetBytes(this.Index, curPos, values, curPos, bufferSize); curPos += bufferSize; } setMethod(obj, FieldName, values); break; case System.Data.SqlDbType.Bit: setMethod(obj, FieldName, reader.GetBoolean(this.Index)); break; case System.Data.SqlDbType.Char: setMethod(obj, FieldName, reader.GetChar(this.Index)); break; case System.Data.SqlDbType.SmallDateTime: case System.Data.SqlDbType.Date: case System.Data.SqlDbType.DateTime: case System.Data.SqlDbType.DateTime2: setMethod(obj, FieldName, reader.GetDateTime(this.Index)); break; case System.Data.SqlDbType.SmallMoney: case System.Data.SqlDbType.Money: case System.Data.SqlDbType.Decimal: setMethod(obj, FieldName, reader.GetDecimal(this.Index)); break; case System.Data.SqlDbType.Float: setMethod(obj, FieldName, reader.GetDouble(this.Index)); break; case System.Data.SqlDbType.Int: setMethod(obj, FieldName, reader.GetInt32(this.Index)); break; case System.Data.SqlDbType.Text: case System.Data.SqlDbType.NVarChar: case System.Data.SqlDbType.NText: case System.Data.SqlDbType.VarChar: case System.Data.SqlDbType.NChar: setMethod(obj, FieldName, reader.GetString(this.Index)); break; case System.Data.SqlDbType.Real: setMethod(obj, FieldName, reader.GetFloat(this.Index)); break; case System.Data.SqlDbType.SmallInt: setMethod(obj, FieldName, reader.GetInt16(this.Index)); break; case System.Data.SqlDbType.TinyInt: setMethod(obj, FieldName, reader.GetByte(this.Index)); break; case System.Data.SqlDbType.UniqueIdentifier: setMethod(obj, FieldName, reader.GetGuid(this.Index)); break; default: break; } } else { setMethod(obj, FieldName, null); } }
byte System.Data.IDataRecord.GetByte(int i) { return(_dataReader.GetByte(i)); }