internal static Thread getThreadFromReader(System.Data.IDataReader rdr, Dictionary <string, bool> columnTable)
{
Thread thread = new Thread();
if (columnTable["THREAD_ID"])
{
thread.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("THREAD_ID")));
}
if (columnTable["SUBJECT"])
{
thread.Subject = rdr.GetString(rdr.GetOrdinal("SUBJECT"));
}
if (columnTable["TRIAGE_GROUP_ID"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("TRIAGE_GROUP_ID")))
{
thread.MailGroup = new TriageGroup()
{
Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("TRIAGE_GROUP_ID")))
};
}
}
if (columnTable["MTOPLOCK"])
{
thread.Oplock = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("MTOPLOCK")));
}
if (columnTable["CATEGORY_TYPE"])
{
thread.MessageCategoryType = (enums.MessageCategoryTypeEnum)Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("CATEGORY_TYPE")));
}
return(thread);
}
internal static Folder getFolderFromReader(System.Data.IDataReader rdr, Dictionary <string, bool> columnTable)
{
Folder folder = new Folder();
if (columnTable["FOLDER_ID"])
{
int idIndex = rdr.GetOrdinal("FOLDER_ID");
if (!rdr.IsDBNull(idIndex))
{
folder.Id = Convert.ToInt32(rdr.GetDecimal(idIndex));
}
}
if (columnTable["USER_ID"])
{
int userIdIndex = rdr.GetOrdinal("USER_ID");
if (!rdr.IsDBNull(userIdIndex))
{
folder.Owner = new User()
{
Id = Convert.ToInt32(rdr.GetDecimal(userIdIndex))
};
}
}
if (columnTable["FOLDER_NAME"])
{
int nameIndex = rdr.GetOrdinal("FOLDER_NAME");
if (!rdr.IsDBNull(nameIndex))
{
folder.Name = rdr.GetString(nameIndex);
}
}
if (columnTable["FOLDOPLOCK"])
{
int oplockIndex = rdr.GetOrdinal("FOLDOPLOCK");
if (!rdr.IsDBNull(oplockIndex))
{
folder.Oplock = Convert.ToInt32(rdr.GetDecimal(oplockIndex));
}
}
// set up other folder properties
if (folder.Id <= 0)
{
folder.SystemFolder = true;
folder.Name = Enum.GetName(typeof(domain.sm.enums.SystemFolderEnum), folder.Id);
}
else if (folder.Id > 0) // all system folders have ID < 0
{
if (columnTable["FOLDACTIVE"])
{
folder.Active = rdr.GetDecimal(rdr.GetOrdinal("FOLDACTIVE")) == 1;
}
}
return(folder);
}
/// <summary>
/// 直接从dataReader中读取第i列的值,如果值为空，则返回Decimal.MinValue
/// </summary>
/// <param name="dataReader">要读取数据的dataReader</param>
/// <param name="i">dataReader中的列索引</param>
/// <returns></returns>
public static decimal GetDecimal(System.Data.IDataReader dataReader, int i)
{
if (dataReader.IsDBNull(i))
{
return(Decimal.MinValue);
}
return(dataReader.GetDecimal(i));
}
/// <summary>
/// 直接从dataReader中读取第i列的值,如果值为空，则返回Decimal.MinValue
/// </summary>
/// <param name="dataReader">要读取数据的dataReader</param>
/// <param name="i">dataReader中的列索引</param>
/// <returns></returns>
public static decimal?GetDecimalNullable(System.Data.IDataReader dataReader, int i)
{
if (dataReader.IsDBNull(i))
{
return(null);
}
return(dataReader.GetDecimal(i));
}
private static T_Entity DataReader2Entity(System.Data.IDataReader dataReader)
{
T_Entity obj = new T_Entity
{
FLong = dataReader.GetInt64(0),
FString = dataReader.GetString(1),
FDecimal = dataReader.GetDecimal(2),
FNullDecimal = dataReader.IsDBNull(3) ? default(decimal?) : dataReader.GetDecimal(3),
FBool = dataReader.GetBoolean(4),
FNullBool = dataReader.IsDBNull(5) ? default(bool?) : dataReader.GetBoolean(5),
Status = (EntityStatus)Enum.ToObject(_entityStatusType, dataReader.GetInt16(7)),
NullStatus = dataReader.IsDBNull(8) ? default(EntityStatus?) : (EntityStatus)Enum.ToObject(_entityStatusType, dataReader.GetInt16(8)),
CreationTime = dataReader.GetDateTime(9),
LastUpdateTime = dataReader.IsDBNull(10) ? default(DateTime?) : dataReader.GetDateTime(10),
};
return(obj);
}
public static decimal GetDecimal(System.Data.IDataReader rdr, string Column, decimal NullValue)
{
if (rdr[Column] != DBNull.Value)
{
return(rdr.GetDecimal(rdr.GetOrdinal(Column)));
}
else
{
return(NullValue);
}
}
public static decimal GetDecimal(System.Data.IDataReader rdr, int column, decimal NullValue)
{
if (rdr[column] != DBNull.Value)
{
return(rdr.GetDecimal(column));
}
else
{
return(NullValue);
}
}
private static T_Entity DataReader2Entity_GetValue(System.Data.IDataReader dataReader)
{
var FNullDecimal = dataReader.GetValue(3);
var FNullBool = dataReader.GetValue(5);
var NullStatus = dataReader.GetValue(8);
var LastUpdateTime = dataReader.GetValue(10);
T_Entity obj = new T_Entity
{
FLong = dataReader.GetInt64(0),
FString = dataReader.GetString(1),
FDecimal = dataReader.GetDecimal(2),
FNullDecimal = FNullDecimal == DBNull.Value ? default(decimal?) : (decimal)FNullDecimal,
FBool = dataReader.GetBoolean(4),
FNullBool = FNullBool == DBNull.Value ? default(bool?) : (bool)FNullBool,
Status = (EntityStatus)Enum.ToObject(_entityStatusType, dataReader.GetInt16(7)),
NullStatus = NullStatus == DBNull.Value ? default(EntityStatus?) : (EntityStatus)Enum.ToObject(_entityStatusType, NullStatus),
CreationTime = dataReader.GetDateTime(9),
LastUpdateTime = LastUpdateTime == DBNull.Value ? default(DateTime?) : (DateTime)LastUpdateTime,
};
return(obj);
}
internal static Message getMessageFromReader(System.Data.IDataReader rdr, Dictionary <string, bool> columnTable)
{
Message msg = new Message();
if (columnTable["SECURE_MESSAGE_ID"])
{
msg.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("SECURE_MESSAGE_ID")));
}
if (columnTable["CLINICIAN_STATUS"])
{
msg.Status = (domain.sm.enums.ClinicianStatusEnum)Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("CLINICIAN_STATUS")));
}
if (columnTable["COMPLETED_DATE"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("COMPLETED_DATE")))
{
msg.CompletedDate = rdr.GetDateTime(rdr.GetOrdinal("COMPLETED_DATE"));
}
}
if (columnTable["ASSIGNED_TO"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ASSIGNED_TO")))
{
if (msg.AssignedTo == null)
{
msg.AssignedTo = new Clinician();
}
msg.AssignedTo.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("ASSIGNED_TO")));
}
}
if (columnTable["CHECKSUM"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("CHECKSUM")))
{
msg.Checksum = rdr.GetString(rdr.GetOrdinal("CHECKSUM"));
}
}
if (columnTable["THREAD_ID"])
{
if (msg.MessageThread == null)
{
msg.MessageThread = new Thread();
}
msg.MessageThread.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("THREAD_ID")));
}
if (columnTable["STATUS_SET_BY"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("STATUS_SET_BY")))
{
if (msg.StatusSetBy == null)
{
msg.StatusSetBy = new Clinician();
}
msg.StatusSetBy.Id = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("STATUS_SET_BY")));
}
}
if (columnTable["SMOPLOCK"])
{
msg.Oplock = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("SMOPLOCK")));
}
if (columnTable["ESCALATED"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ESCALATED")))
{
msg.EscalatedDate = rdr.GetDateTime(rdr.GetOrdinal("ESCALATED"));
}
}
if (columnTable["BODY"])
{
msg.Body = rdr.GetString(rdr.GetOrdinal("BODY"));
}
if (columnTable["SENT_DATE"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("SENT_DATE")))
{
msg.SentDate = rdr.GetDateTime(rdr.GetOrdinal("SENT_DATE"));
}
}
if (columnTable["SENDER_TYPE"])
{
msg.SenderType = (domain.sm.enums.ParticipantTypeEnum)Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("SENDER_TYPE")));
}
if (columnTable["SENDER_ID"])
{
msg.SenderId = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("SENDER_ID")));
}
if (columnTable["SENDER_NAME"])
{
msg.SenderName = rdr.GetString(rdr.GetOrdinal("SENDER_NAME"));
}
if (columnTable["RECIPIENT_TYPE"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("RECIPIENT_TYPE")))
{
msg.RecipientType = (domain.sm.enums.ParticipantTypeEnum)Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("RECIPIENT_TYPE")));
}
}
if (columnTable["RECIPIENT_ID"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("RECIPIENT_ID")))
{
msg.RecipientId = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("RECIPIENT_ID")));
}
}
if (columnTable["RECIPIENT_NAME"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("RECIPIENT_NAME")))
{
msg.RecipientName = rdr.GetString(rdr.GetOrdinal("RECIPIENT_NAME"));
}
}
if (columnTable["SENT_DATE_LOCAL"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("SENT_DATE_LOCAL")))
{
msg.SentDateLocal = rdr.GetDateTime(rdr.GetOrdinal("SENT_DATE_LOCAL"));
}
}
if (columnTable["ESCALATION_NOTIFICATION_DATE"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ESCALATION_NOTIFICATION_DATE")))
{
msg.EscalationNotificationDate = rdr.GetDateTime(rdr.GetOrdinal("ESCALATION_NOTIFICATION_DATE"));
}
}
if (columnTable["ESCALATION_NOTIFICATION_TRIES"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ESCALATION_NOTIFICATION_TRIES")))
{
msg.EscalationNotificationTries = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("ESCALATION_NOTIFICATION_TRIES")));
}
}
if (columnTable["READ_RECEIPT"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("READ_RECEIPT")))
{
msg.ReadReceipt = rdr.GetString(rdr.GetOrdinal("READ_RECEIPT"));
}
}
if (columnTable["HAS_ATTACHMENT"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("HAS_ATTACHMENT")))
{
msg.Attachment = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("HAS_ATTACHMENT"))) == 1;
}
}
if (columnTable["ATTACHMENT_ID"])
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ATTACHMENT_ID")))
{
msg.AttachmentId = Convert.ToInt32(rdr.GetDecimal(rdr.GetOrdinal("ATTACHMENT_ID")));
}
}
return(msg);
}
internal static MessageAttachment getAttachmentFromReader(System.Data.IDataReader rdr, Dictionary <string, bool> columnTable)
{
MessageAttachment attachment = new MessageAttachment();
if (columnTable["ATTACHMENT_ID"])
{
int idIndex = rdr.GetOrdinal("ATTACHMENT_ID");
if (!rdr.IsDBNull(idIndex))
{
attachment.Id = Convert.ToInt32(rdr.GetDecimal(idIndex));
}
}
if (columnTable["ATTACHMENT_NAME"])
{
int nameIndex = rdr.GetOrdinal("ATTACHMENT_NAME");
if (!rdr.IsDBNull(nameIndex))
{
attachment.AttachmentName = rdr.GetString(nameIndex);
}
}
if (columnTable["ATTACHMENT"])
{
int attIndex = rdr.GetOrdinal("ATTACHMENT");
if (!rdr.IsDBNull(attIndex))
{
// not crazy about this implementation as it appears to invoke the reader twice but the commented out code
// block directly below throws an exception when calling GetOracleBlob for some reason... The good thing about
// this solution is it should work for all IDataReader implementations and doesn't need to be cast to an OracleDataReader
byte[] blob = new byte[rdr.GetBytes(attIndex, 0, null, 0, Int32.MaxValue)];
rdr.GetBytes(attIndex, 0, blob, 0, blob.Length);
attachment.SmFile = blob;
//if (rdr is Oracle.DataAccess.Client.OracleDataReader)
//{
// System.Console.WriteLine(rdr[attIndex].GetType().ToString());
// Oracle.DataAccess.Types.OracleBlob blob = ((Oracle.DataAccess.Client.OracleDataReader)rdr).GetOracleBlob(attIndex);
// byte[] buf = new byte[blob.Length];
// blob.Read(buf, 0, Convert.ToInt32(blob.Length));
// attachment.SmFile = buf;
//}
}
}
if (columnTable["MIME_TYPE"])
{
int mimeTypeIndex = rdr.GetOrdinal("MIME_TYPE");
if (!rdr.IsDBNull(mimeTypeIndex))
{
attachment.MimeType = rdr.GetString(mimeTypeIndex);
}
}
if (columnTable["ATTOPLOCK"])
{
int oplockIndex = rdr.GetOrdinal("ATTOPLOCK");
if (!rdr.IsDBNull(oplockIndex))
{
attachment.Oplock = Convert.ToInt32(rdr.GetDecimal(oplockIndex));
}
}
return(attachment);
}
internal static Addressee getAddresseeFromReader(System.Data.IDataReader rdr, Dictionary <string, bool> columnTable)
{
Addressee addr = new Addressee();
if (columnTable["ADDRESSEE_ID"])
{
int idIndex = rdr.GetOrdinal("ADDRESSEE_ID");
if (!rdr.IsDBNull(idIndex))
{
addr.Id = Convert.ToInt32(rdr.GetDecimal(idIndex));
}
}
if (columnTable["ADDRESSEE_ROLE"])
{
int roleIndex = rdr.GetOrdinal("ADDRESSEE_ROLE");
if (!rdr.IsDBNull(roleIndex))
{
addr.Role = (AddresseeRoleEnum)Convert.ToInt32(rdr.GetDecimal(roleIndex));
}
}
if (columnTable["SECURE_MESSAGE_ID"])
{
int smIdIndex = rdr.GetOrdinal("SECURE_MESSAGE_ID");
if (!rdr.IsDBNull(smIdIndex))
{
addr.Message = new Message()
{
Id = Convert.ToInt32(rdr.GetDecimal(smIdIndex))
};
}
}
if (columnTable["USER_ID"])
{
int userIdIndex = rdr.GetOrdinal("USER_ID");
if (!rdr.IsDBNull(userIdIndex))
{
addr.Owner = new User()
{
Id = Convert.ToInt32(rdr.GetDecimal(userIdIndex))
};
}
}
if (columnTable["ADDROPLOCK"])
{
int oplockIndex = rdr.GetOrdinal("ADDROPLOCK");
if (!rdr.IsDBNull(oplockIndex))
{
addr.Oplock = Convert.ToInt32(rdr.GetDecimal(oplockIndex));
}
}
if (columnTable["FOLDER_ID"])
{
int folderIdIndex = rdr.GetOrdinal("FOLDER_ID");
if (!rdr.IsDBNull(folderIdIndex))
{
addr.FolderId = Convert.ToInt32(rdr.GetDecimal(folderIdIndex));
}
addr.Folder = Folder.getFolderFromReader(rdr);
}
if (columnTable["READ_DATE"])
{
int readDateIndex = rdr.GetOrdinal("READ_DATE");
if (!rdr.IsDBNull(readDateIndex))
{
addr.ReadDate = rdr.GetDateTime(readDateIndex);
}
}
if (columnTable["REMINDER_DATE"])
{
int reminderDateIndex = rdr.GetOrdinal("REMINDER_DATE");
if (!rdr.IsDBNull(reminderDateIndex))
{
addr.ReminderDate = rdr.GetDateTime(reminderDateIndex);
}
}
return(addr);
}
public static decimal Decimal(System.Data.IDataReader reader, MapPoint mapPoint, decimal defaultValue = 0)
{
try
{
return(mapPoint.IsInResultSet() && !reader.IsDBNull(mapPoint.Index) ? reader.GetDecimal(mapPoint.Index) : defaultValue);
}
catch (Exception)
{
throw;
}
}
public static decimal?GetDecimal(System.Data.IDataReader rdr, string Column)
{
return(rdr [Column] != DBNull.Value ? (decimal?)rdr.GetDecimal(rdr.GetOrdinal(Column)) : null);
}
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);
}
}
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);
}
}
decimal System.Data.IDataRecord.GetDecimal(int i)
{
return(_dataReader.GetDecimal(i));
}
