/// <summary>
/// Sets the font color of ExcelColumn from a Color object
/// </summary>
/// <param name="column"></param>
/// <param name="fontColor"></param>
/// <returns></returns>
public static ExcelColumn SetFontColor(this ExcelColumn column, Color fontColor)
{
column.Style.SetFontColor(fontColor);
return(column);
}
/// <summary>
/// Sets the horizontal alignment of ExcelColumn
/// </summary>
/// <param name="column"></param>
/// <param name="horizontalAlignment"></param>
/// <returns></returns>
public static ExcelColumn SetHorizontalAlignment(this ExcelColumn column, ExcelHorizontalAlignment horizontalAlignment)
{
column.Style.SetHorizontalAlignment(horizontalAlignment);
return(column);
}
public ColumnBuilder(ExcelColumn column)
{
Column = column;
}
public static void LoadExcelData()
{
ExcelBusy = true;
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
string path;
using (var dialog = new OpenFileDialog())
{
dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
dialog.Filter = @"Excel files (*.xlsx)|*.xlsx";
dialog.FilterIndex = 1;
if (dialog.ShowDialog() != DialogResult.OK)
{
return;
}
path = dialog.FileName;
}
var workbook = ExcelFile.Load(path);
var worksheet = workbook.Worksheets[0];
ExcelColumn nameColumn = null, phoneNumberColumn = null, birthDateColumn = null;
foreach (var cell in worksheet.GetUsedCellRange(true))
{
if (cell.ValueType == CellValueType.Null)
{
continue;
}
if (cell.StringValue.Contains("Participant Name"))
{
nameColumn = cell.Column;
}
else if (cell.StringValue.Contains("Participants' Birth Date"))
{
birthDateColumn = cell.Column;
}
else if (cell.StringValue.Contains("Phones"))
{
phoneNumberColumn = cell.Column;
}
if (nameColumn != null && phoneNumberColumn != null && birthDateColumn != null)
{
break;
}
}
if (nameColumn == null || phoneNumberColumn == null || birthDateColumn == null)
{
throw new NoNullAllowedException();
}
var nameStrings = (from nameCell in nameColumn.Cells
where nameCell.ValueType != CellValueType.Null &&
!nameCell.StringValue.Contains("Participant Name")
select nameCell.StringValue).ToList();
var phoneStrings = (from phoneCell in phoneNumberColumn.Cells
where phoneCell.ValueType != CellValueType.Null &&
!phoneCell.StringValue.Contains("Phones")
select phoneCell.StringValue).ToList();
var birthStrings = (from birthCell in birthDateColumn.Cells
where birthCell.ValueType != CellValueType.Null &&
!birthCell.StringValue.Contains("Participants' Birth Date")
select birthCell.StringValue).ToList();
using (var db = new BirthdayEntriesContext())
{
db.Database.ExecuteSqlCommand("TRUNCATE TABLE [BirthdayEntries]");
for (int i = 0; i < nameStrings.Count; i++)
{
if (!string.IsNullOrWhiteSpace(nameStrings[i]) && !string.IsNullOrWhiteSpace(phoneStrings[i]) &&
!string.IsNullOrWhiteSpace(birthStrings[i]))
{
db.BirthdayEntries.Add(new BirthdayEntry
{
Name = nameStrings[i],
Number = ProcessPhone(phoneStrings[i]),
Birthday = DateTime.Parse(ProcessDates(birthStrings[i])),
SmsDate = DateTime.Parse(SqlDateTime.MinValue.ToString())
});
}
}
db.SaveChanges();
}
ExcelBusy = false;
}
internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns)
{
ValidateColumn(columnFrom, columns);
lock (ws)
{
//Set previous column Max to Row before if it spans the deleted column range.
ExcelColumn col = ws.GetValueInner(0, columnFrom) as ExcelColumn;
if (col == null)
{
var r = 0;
var c = columnFrom;
if (ws._values.PrevCell(ref r, ref c))
{
col = ws.GetValueInner(0, c) as ExcelColumn;
if (col._columnMax >= columnFrom)
{
col.ColumnMax = columnFrom - 1;
}
}
}
var delRange = new ExcelAddressBase(1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1);
WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange);
DeleteCellStores(ws, 0, columnFrom, 0, columns);
foreach (var wsToUpdate in ws.Workbook.Worksheets)
{
FixFormulasDeleteColumn(wsToUpdate, columnFrom, columns, ws.Name);
}
WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true);
foreach (var tbl in ws.Tables)
{
if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column)
{
var node = tbl.Columns[0].TopNode.ParentNode;
var ix = columnFrom - tbl.Address.Start.Column;
for (int i = 0; i < columns; i++)
{
if (node.ChildNodes.Count > ix)
{
node.RemoveChild(node.ChildNodes[ix]);
}
}
tbl._cols = new ExcelTableColumnCollection(tbl);
}
tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns);
foreach (var ptbl in ws.PivotTables)
{
if (ptbl.Address.Start.Column > columnFrom + columns)
{
ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns);
}
if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns)
{
ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address;
}
}
}
//Adjust DataValidation
foreach (ExcelDataValidation dv in ws.DataValidations)
{
var addr = dv.Address;
if (addr.Start.Column > columnFrom + columns)
{
var newAddr = addr.DeleteColumn(columnFrom, columns).Address;
if (addr.Address != newAddr)
{
dv.SetAddress(newAddr);
}
}
}
var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1];
var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Left);
DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Left);
DeleteSparkLinesAddress(range, eShiftTypeDelete.Left, effectedAddress);
//Adjust drawing positions.
WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns);
}
}
public void RemoveColumn(ExcelColumn <T> column)
{
RemoveColumn(Columns.IndexOf(column));
}
public static void GenerovatPrehledXLS(this STAG_Classes.STAG_Database data, string path)
{
// Kramář W.I.P.
FileInfo file = new FileInfo(path);
if (file.Exists)
{
file.Delete();
}
using (var excel = new ExcelPackage(file))
{
ExcelWorksheet sheet;
for (int j = 0; j < 3; j++)
{
if (j == 0)
{
sheet = excel.Workbook.Worksheets.Add("ZS");
}
else
if (j == 1)
{
sheet = excel.Workbook.Worksheets.Add("LS");
}
else
{
sheet = excel.Workbook.Worksheets.Add("ZS+LS");
}
sheet.Cells[1, 1].Value = "Osobni cislo";
sheet.Cells[1, 2].Value = "Rocnik";
sheet.Cells[1, 3].Value = "St. program";
sheet.Cells[1, 4].Value = "Forma";
var katedry = data.HiearchiePracovist[data.Fakulta];
for (int i = 0; i < katedry.Count; i++)
{
sheet.Cells[1, i + 5].Value = katedry.Keys.ElementAt(i);
}
sheet.Cells[1, katedry.Count + 5].Value = "jiná";
// st.program
// forma
// foreach (katedry)
// debug - vsechny predmety studenta
int row = 2;
Dictionary <string, double> podiKatedry;
foreach (var student in data.Students)
{
sheet.Cells[row, 1].Value = student.OsCislo;
sheet.Cells[row, 2].Value = student.Rocnik;
sheet.Cells[row, 3].Value = student.KodSp;
sheet.Cells[row, 4].Value = student.FormaSp;
if (j == 0)
{
podiKatedry = student.PodilKatedryZS;
}
else
if (j == 1)
{
podiKatedry = student.PodilKatedryLS;
}
else
{
podiKatedry = student.PodilKatedry;
}
for (int i = 0; i < katedry.Count; i++)
{
if (podiKatedry.ContainsKey(katedry.Keys.ElementAt(i)))
{
sheet.Cells[row, i + 5].Value = Math.Round(podiKatedry[katedry.Keys.ElementAt(i)], 2);
}
else
{
sheet.Cells[row, i + 5].Value = 0;
}
}
sheet.Cells[row, katedry.Count + 5].Value = (from x in podiKatedry where !katedry.Keys.Contains(x.Key) select x.Value).Sum();
row++;
}
for (int i = 1; i <= 4; i++)
{
ExcelColumn col = sheet.Column(i);
col.AutoFit();
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
}
}
excel.Save();
}
}
internal OpenXmlExcelColumn(ExcelColumn obj)
{
_obj = obj;
}
/// <summary>
/// 读取Excel文档结构
/// </summary>
private void InitExcelStructure()
{
try
{
_excelStructure = new ExcelStructure()
{
Name = _fileName,
};
var sheets = _excelStructure.Sheets;
var excelTablesName = GetExcelTablesName(); //获取excel表头
if (excelTablesName == null || !excelTablesName.Any()) //1.0、无工作表
{
_checkReport.ExcelStructureError("无工作表", "没有找到工作表，请检查Excel是否有效!");
}
else
{
var sheetIndex = -1;
foreach (var sheetName in excelTablesName)
{
sheetIndex++;
if (string.IsNullOrWhiteSpace(sheetName))
_checkReport.ExcelStructureError("工作表名称无效", $"----第{sheetIndex + 1}个工作表名称无效!");
else
{
var aSheet = new ExcelSheet()
{
SheetIndex = sheetIndex,
SheetName = sheetName
};
var columns = aSheet.Columns;
var sheet = _workbook.GetSheet(sheetName);
var headerrow = sheet.GetRow(0);
if (headerrow == null)
{
_checkReport.ExcelStructureError("存在空工作表", $"----第{sheetIndex + 1}个工作表,为空工作表!");
}
else
{
var lastCellNum = headerrow.LastCellNum;
var continueNullHeader = 0;
for (int colIndex = 0; colIndex < lastCellNum; colIndex++)
{
try
{
var cell = headerrow.GetCell(colIndex);
if (cell == null || cell.ToString().Trim() == "") //1.3 存在为空列名称
{
//aCol.ColumnName = NullHeader + colIndex;
//cols.Add(aCol); 空列头不加入到表结构中
continueNullHeader++;
if (continueNullHeader > 2) break;
}
else
{
continueNullHeader = 0;
var value = cell.CellType == CellType.String ?
cell.StringCellValue.Trim() : cell.ToString().Trim();
if (columns.Any(f => f.ColumnName == Convert.ToString(value))) //1.4存在重复列名
{
_checkReport.ExcelStructureError("列名称重复", $"----[{sheetName}]工作表,存在多个名称为[{value}]的列!");
}
else
{
var aCol = new ExcelColumn()
{
ColumnIndex = colIndex,
ColumnName = value.Replace(" ", "").Replace("\n", "").Replace("\r", "")
};
columns.Add(aCol);
}
}
}
catch (Exception)
{
_checkReport.ExcelStructureError("列名称读取失败", $"----[{sheetName}]工作表,第{colIndex + 1},列名称读取失败!");
}
}
}
sheets.Add(aSheet);
}
}
}
}
catch (Exception ex)
{
_checkReport.ExcelStructureError("读取表结构出现错误", $"----[错误信息]{ex.Message}");
}
}
/// <summary>
/// Vygenerovani prehledu zateze ziskane analyzou SRA.
/// Vystupem je xlsx soubor.
/// </summary>
/// <param name="data"></param>
/// <param name="name">Nazev souboru bez pripony!</param>
public static void GenerovatSRAPrehledXLS(this STAG_Classes.STAG_Database data, string name)
{
FileInfo file;
int number = 2;
if ((file = new FileInfo(name + ".xlsx")).Exists)
{
while ((file = new FileInfo(name + number + ".xlsx")).Exists)
{
number++;
}
}
using (var excel = new ExcelPackage(file))
{
ExcelWorksheet sheet;
var deprts = data.HiearchiePracovist[data.Fakulta];
double per;
int row = 2;
int col = 5;
sheet = excel.Workbook.Worksheets.Add("sheet");
sheet.Cells[1, 1].Value = "Osobni cislo";
sheet.Cells[1, 2].Value = "Rocnik";
sheet.Cells[1, 3].Value = "St. program";
sheet.Cells[1, 4].Value = "Forma";
foreach (var dep in deprts)
{
sheet.Cells[1, col++].Value = dep.Key;
}
sheet.Cells[1, col].Value = "jiná";
foreach (var student in data.zatezNaStudenta.GetAll())
{
sheet.Cells[row, 1].Value = student.Item1.OsCislo;
sheet.Cells[row, 2].Value = student.Item1.Rocnik;
sheet.Cells[row, 3].Value = student.Item1.KodSp;
sheet.Cells[row, 4].Value = student.Item1.FormaSp;
col = 5;
foreach (var dep in deprts)
{
if (student.Item2.TryGetValue(dep.Key, out per))
{
sheet.Cells[row, col++].Value = per;
}
else
{
sheet.Cells[row, col++].Value = 0;
}
}
sheet.Cells[row, col].Value = (from cizi in student.Item2 where !deprts.Keys.Contains(cizi.Key) select cizi.Value).Sum();
row++;
}
for (int i = 1; i <= 4; i++)
{
ExcelColumn coll = sheet.Column(i);
coll.AutoFit();
coll.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
}
excel.Save();
}
}
public ExcelCell(int colIndex, int rowIndex, string value)
{
_column = new ExcelColumn(colIndex);
_row = new ExcelRow(rowIndex);
_value = value;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
Int32 ColumnaDetalle = 1;
Int32 Contador = 0;
DataTable Data = AtencionPeluqueriaBuss.BuscarCanil(0, Request.QueryString["txtfechaIni"], Request.QueryString["txtFechaFinal"], Request.QueryString["cboRecurso"], Request.QueryString["cboEstado"]);
DataTable Data2 = AtencionPeluqueriaBuss.BuscarSector(0, Request.QueryString["txtfechaIni"], Request.QueryString["txtFechaFinal"], Request.QueryString["cboRecurso"], Request.QueryString["cboEstado"]);
string Name = Convert.ToString(DateTime.Now.Ticks);
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet WorkSheet = package.Workbook.Worksheets.Add("BASE");
Int32 Fila = 2;
if (Data.Rows.Count > 0 && (Request.QueryString["cboRecurso"] == "0" || Request.QueryString["cboRecurso"] == "" || Request.QueryString["cboRecurso"] == "1"))
{
ExcelRange range = WorkSheet.Cells[2, 1, 2, 6];
range.Merge = true;
WorkSheet.Cells[Fila, 1].Style.Font.SetFromFont(new Font("Calibri", 16, FontStyle.Bold));
WorkSheet.Cells[Fila, 1].Style.Font.Color.SetColor(Color.Black);
WorkSheet.Cells[Fila, 1].Value = "Listado de Canil";
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
Fila = Fila + 2;
WorkSheet.Cells[Fila, 1].Value = "CODIGO";
WorkSheet.Cells[Fila, 2].Value = "NOMBRE";
WorkSheet.Cells[Fila, 3].Value = "TAMAÑO";
WorkSheet.Cells[Fila, 4].Value = "ESTADO";
WorkSheet.Cells[Fila, 5].Value = "ESPECIE";
WorkSheet.Cells[Fila, 6].Value = "OBSERVACIONES";
ExcelRange RangoDeCeldasNameT2 = WorkSheet.Cells[Fila, 1, Fila, 6];
RangoDeCeldasNameT2.Style.Border.Top.Style = ExcelBorderStyle.Thin;
RangoDeCeldasNameT2.Style.Border.Left.Style = ExcelBorderStyle.Thin;
RangoDeCeldasNameT2.Style.Border.Right.Style = ExcelBorderStyle.Thin;
RangoDeCeldasNameT2.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
RangoDeCeldasNameT2.Style.Font.Color.SetColor(Color.Black);
RangoDeCeldasNameT2.Style.Fill.PatternType = ExcelFillStyle.Solid;
RangoDeCeldasNameT2.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#FACA2C"));
RangoDeCeldasNameT2.Style.Font.Bold = true;
RangoDeCeldasNameT2.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
RangoDeCeldasNameT2.Style.WrapText = true;
ColumnaDetalle = 1;
Contador = 0;
foreach (DataRow dr2 in Data.Rows)
{
Fila = Fila + 1;
ColumnaDetalle = 1;
foreach (DataColumn dr in Data.Columns)
{
if (ColumnaDetalle <= 6)
{
WorkSheet.Cells[Fila, ColumnaDetalle].Value = dr2[dr.ColumnName];
}
ColumnaDetalle = ColumnaDetalle + 1;
Contador = Contador + 1;
}
ExcelRange rangeCab6 = WorkSheet.Cells[Fila, 1, Fila, 6];
rangeCab6.Style.Border.Top.Style = ExcelBorderStyle.Thin;
rangeCab6.Style.Border.Left.Style = ExcelBorderStyle.Thin;
rangeCab6.Style.Border.Right.Style = ExcelBorderStyle.Thin;
rangeCab6.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
rangeCab6.Style.Border.BorderAround(ExcelBorderStyle.Thin);
rangeCab6.Style.Font.Color.SetColor(Color.Black);
rangeCab6.Style.Font.Bold = false;
rangeCab6.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
rangeCab6.Style.WrapText = true;
}
Fila = Fila + 2;
}
if (Data2.Rows.Count > 0 && (Request.QueryString["cboRecurso"] == "0" || Request.QueryString["cboRecurso"] == "" || Request.QueryString["cboRecurso"] == "2"))
{
ExcelRange range5 = WorkSheet.Cells[Fila, 1, Fila, 6];
range5.Merge = true;
WorkSheet.Cells[Fila, 1].Style.Font.SetFromFont(new Font("Calibri", 16, FontStyle.Bold));
WorkSheet.Cells[Fila, 1].Style.Font.Color.SetColor(Color.Black);
WorkSheet.Cells[Fila, 1].Value = "Listado de Sectores";
range5.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
Fila = Fila + 2;
WorkSheet.Cells[Fila, 1].Value = "CODIGO";
WorkSheet.Cells[Fila, 2].Value = "NOMBRE";
WorkSheet.Cells[Fila, 3].Value = "SERVICIO";
WorkSheet.Cells[Fila, 4].Value = "ESTADO";
WorkSheet.Cells[Fila, 5].Value = "OBSERVACIONES";
ExcelRange RangoDeCeldasName5 = WorkSheet.Cells[Fila, 5, Fila, 6];
RangoDeCeldasName5.Merge = true;
ExcelRange RangoDeCeldasNameT4 = WorkSheet.Cells[Fila, 1, Fila, 6];
RangoDeCeldasNameT4.Style.Border.Top.Style = ExcelBorderStyle.Thin;
RangoDeCeldasNameT4.Style.Border.Left.Style = ExcelBorderStyle.Thin;
RangoDeCeldasNameT4.Style.Border.Right.Style = ExcelBorderStyle.Thin;
RangoDeCeldasNameT4.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
RangoDeCeldasNameT4.Style.Font.Color.SetColor(Color.Black);
RangoDeCeldasNameT4.Style.Fill.PatternType = ExcelFillStyle.Solid;
RangoDeCeldasNameT4.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#FACA2C"));
RangoDeCeldasNameT4.Style.Font.Bold = true;
RangoDeCeldasNameT4.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
RangoDeCeldasNameT4.Style.WrapText = true;
ColumnaDetalle = 1;
Contador = 0;
foreach (DataRow dr3 in Data2.Rows)
{
Fila = Fila + 1;
ColumnaDetalle = 1;
foreach (DataColumn dr4 in Data2.Columns)
{
if (ColumnaDetalle <= 5)
{
WorkSheet.Cells[Fila, ColumnaDetalle].Value = dr3[dr4.ColumnName];
if (ColumnaDetalle == 5)
{
ExcelRange RangoDeCeldasNamecol5 = WorkSheet.Cells[Fila, 5, Fila, 6];
RangoDeCeldasNamecol5.Merge = true;
}
}
ColumnaDetalle = ColumnaDetalle + 1;
Contador = Contador + 1;
}
ExcelRange RangeCab8 = WorkSheet.Cells[Fila, 1, Fila, 6];
RangeCab8.Style.Border.Top.Style = ExcelBorderStyle.Thin;
RangeCab8.Style.Border.Left.Style = ExcelBorderStyle.Thin;
RangeCab8.Style.Border.Right.Style = ExcelBorderStyle.Thin;
RangeCab8.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
RangeCab8.Style.Border.BorderAround(ExcelBorderStyle.Thin);
RangeCab8.Style.Font.Color.SetColor(Color.Black);
RangeCab8.Style.Font.Bold = false;
RangeCab8.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
RangeCab8.Style.WrapText = true;
}
}
ExcelColumn Col1 = WorkSheet.Column(1);
ExcelColumn Col2 = WorkSheet.Column(2);
ExcelColumn Col3 = WorkSheet.Column(3);
ExcelColumn Col4 = WorkSheet.Column(4);
ExcelColumn Col5 = WorkSheet.Column(5);
ExcelColumn Col6 = WorkSheet.Column(6);
Col1.Width = 20;
Col2.Width = 20;
Col3.Width = 20;
Col4.Width = 20;
Col5.Width = 20;
Col6.Width = 40;
Response.Clear();
Response.ContentType = "application/xlsx";
Response.AddHeader("content-disposition", "attachment; filename=Reporte_Canil_Sectores.xlsx");
Response.BinaryWrite(package.GetAsByteArray());
Response.End();
}
}
catch (Exception ex)
{
lblmsg.Text = "Error interno en el sistema contacte a su administrador " + " Detalle:" + ex.Message;
msgError.Clear();
msgError.AppendLine("Fecha:" + DateTime.Now.ToString());
msgError.AppendLine("Descripción:" + ex.Message);
msgError.AppendLine("Detalle:" + ex.StackTrace);
log.Error(msgError.ToString());
}
}
}
/// <summary>
/// 读取hdr表说明 转换成list 导入数据库 与数据库中的列 进行比较 判断excel与表结构是否一致
/// </summary>
/// <param name="cells"></param>
/// <returns></returns>
public IList <ExcelColumn> Process(Cells cells)
{
List <ExcelColumn> list = new List <ExcelColumn>();
string TableName = string.Empty; //表名 （organization（机构信息））
string Schema = string.Empty; //业务域（mdm（主数据））
string TableComment = string.Empty; //表说明 （机构信息，如：医疗机构、科研机构等）
for (int row = 0; row < cells.Rows.Count; row++)
{
Row current_row = cells.CheckRow(row);
if (current_row == null || current_row.FirstDataCell == null)
{
continue;
}
//表名
Cell cel = current_row[CellsHelper.ColumnNameToIndex("A")];
if (cel.IsMerged)
{ //业务域 及 表说明
var range = cel.GetMergedRange();
if (range.CellCount == 9)
{//表的第一行
TableName = cel.GetStringValue(CellValueFormatStrategy.None);
}
if (range.CellCount == 2)
{//第二行
Schema = current_row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None);
TableComment = current_row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None);
}
continue;
}
else if (cel.GetStringValue(CellValueFormatStrategy.None) == "序号") //列头
{
continue;
}
else //表列
{
ExcelColumn ec = new ExcelColumn();
var tableNamearray = TableName.Split(new string[4] {
"（", "）", "(", ")"
}, StringSplitOptions.None);
ec.TableName = tableNamearray[0];//表名 （organization（机构信息））
ec.TableNameCh = string.Join("", tableNamearray.Where(p => p != tableNamearray[0]).ToArray());
var schemaarray = Schema.Split(new string[4] {
"（", "）", "(", ")"
}, StringSplitOptions.None);
ec.Schema = schemaarray[0]; //业务域（mdm（主数据））
ec.SchemaCh = string.Join("", schemaarray.Where(p => p != schemaarray[0]).ToArray()); //业务域（mdm（主数据））
ec.TableComment = TableComment; //表说明 （机构信息，如：医疗机构、科研机构等）
ec.SerialNumber = cel.GetStringValue(CellValueFormatStrategy.None); //序号
ec.ColumnName = current_row[CellsHelper.ColumnNameToIndex("B")].GetStringValue(CellValueFormatStrategy.None); //列名
ec.ColumnComment = current_row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None); //中文名
ec.DataType = current_row[CellsHelper.ColumnNameToIndex("D")].GetStringValue(CellValueFormatStrategy.None); //字段类型
ec.IsNull = current_row[CellsHelper.ColumnNameToIndex("E")].GetStringValue(CellValueFormatStrategy.None); //允许空
ec.ForeignKey = current_row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None); //外键（关联字段）
ec.CodeSystem = current_row[CellsHelper.ColumnNameToIndex("G")].GetStringValue(CellValueFormatStrategy.None); //字典系统（值域）
ec.IsStandard = current_row[CellsHelper.ColumnNameToIndex("H")].GetStringValue(CellValueFormatStrategy.None); //字典表标准化
ec.Description = current_row[CellsHelper.ColumnNameToIndex("I")].GetStringValue(CellValueFormatStrategy.None); //说明
list.Add(ec);
continue;
}
}
return(list);
}
public ExcelCell(int colIndex, int rowIndex, string value)
{
_column = new ExcelColumn(colIndex);
_row = new ExcelRow(rowIndex);
_value = value;
}
public Column(ExcelColumn column)
{
_column = column;
}
private void SetStyleAddress(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw new Exception("error address");
}
//Columns
if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
bool isNew;
//Get the startcolumn
object o = null;
if (!ws.ExistsValueInner(0, address.Start.Column, ref o))
{
column = ws.Column(address.Start.Column);
isNew = true;
}
else
{
//column = (ExcelColumn)ws.GetValueInner(0, address.Start.Column);
column = (ExcelColumn)o;
isNew = false;
}
var prevColumnMax = column.ColumnMax;
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMin > prevColumnMax + 1)
{
ExcelColumn newColumn = ws.Column(prevColumnMax + 1);
newColumn.ColumnMax = column.ColumnMin - 1;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
if (column.ColumnMax > address.End.Column)
{
ExcelColumn newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws.GetStyleInner(0, column.ColumnMin);
AddNewStyleColumn(sender, e, ws, styleCashe, column, s);
//index++;
prevColumnMax = column.ColumnMax;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
if (column._columnMax == address.End.Column)
{
break;
}
if (isNew)
{
column._columnMax = address.End.Column;
}
else
{
ExcelColumn newColumn = ws.Column(column._columnMax + 1);
newColumn.ColumnMax = address.End.Column;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
column = newColumn;
}
break;
}
column = ws.GetValueInner(0, col) as ExcelColumn;
}
if (column._columnMax < address.End.Column)
{
ExcelColumn newCol = ws.Column(column._columnMax + 1);
newCol._columnMax = address.End.Column;
var s = ws.GetStyleInner(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column && cse.Column <= address.End.Column && cse.Value._styleId != 0)
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, cse.Column, newId);
}
}
}
if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
{
//Update cells with styled columns
cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, 0, address._toRow, 0);
while (cse.Next())
{
if (cse.Value._styleId == 0)
{
continue;
}
for (var c = address._fromCol; c <= address._toCol; c++)
{
if (!ws.ExistsStyleInner(cse.Row, c))
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, c, newId);
}
}
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (var rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
var s = ws.GetStyleInner(rowNum, 0);
if (s == 0)
{
//iterate all columns and set the row to the style of the last column
var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns);
while (cse.Next())
{
s = cse.Value._styleId;
if (s == 0)
{
continue;
}
var c = ws.GetValueInner(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (var col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws.ExistsStyleInner(rowNum, col))
{
ws.SetStyleInner(rowNum, col, s);
}
}
}
}
ws.SetStyleInner(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(rowNum, 0, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(rowNum, 0, newId);
}
}
//Update individual cells
var cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse2.Next())
{
var s = cse2.Value._styleId;
if (s == 0)
{
continue;
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(cse2.Row, cse2.Column, newId);
}
}
//Update cells with styled rows
cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, address._toCol);
while (cse2.Next())
{
if (cse2.Value._styleId == 0)
{
continue;
}
for (var r = address._fromRow; r <= address._toRow; r++)
{
if (!ws.ExistsStyleInner(r, cse2.Column))
{
var s = cse2.Value._styleId;
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(r, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(r, cse2.Column, newId);
}
}
}
}
}
else //Cell range
{
var tmpCache = styleCashe;
var rowCache = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1);
var colCache = new Dictionary <int, ExcelCoreValue>(address.End.Column - address.Start.Column + 1);
ws._values.SetRangeValueSpecial(address.Start.Row, address.Start.Column, address.End.Row, address.End.Column,
(list, index, row, column, args) =>
{
// Optimized GetStyleID
var s = list[index]._styleId;
if (s == 0 && !ws.ExistsStyleInner(row, 0, ref s))
{
// get row styleId with cache
if (!rowCache.ContainsKey(row))
{
rowCache.Add(row, ws._values.GetValue(row, 0)._styleId);
}
s = rowCache[row];
if (s == 0)
{
// get column styleId with cache
if (!colCache.ContainsKey(column))
{
colCache.Add(column, ws._values.GetValue(0, column));
}
s = colCache[column]._styleId;
if (s == 0)
{
int r = 0, c = column;
if (ws._values.PrevCell(ref r, ref c))
{
//var val = ws._values.GetValue(0, c);
if (!colCache.ContainsKey(c))
{
colCache.Add(c, ws._values.GetValue(0, c));
}
ExcelCoreValue val = colCache[c];
var colObj = (ExcelColumn)val._value;
if (colObj != null && colObj.ColumnMax >= column) //Fixes issue 15174
{
s = val._styleId;
}
}
}
}
}
if (tmpCache.ContainsKey(s))
{
//ws.SetStyleInner(row, column, tmpCache[s]);
list[index] = new ExcelCoreValue {
_value = list[index]._value, _styleId = tmpCache[s]
};
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
tmpCache.Add(s, newId);
//ws.SetStyleInner(row, column, newId);
list[index] = new ExcelCoreValue {
_value = list[index]._value, _styleId = newId
};
}
}, e);
}
}
/// <summary>
///
/// </summary>
/// <param name="column"></param>
/// <param name="tempColumn"></param>
public static void CopyFromTemplate(this ExcelColumn column, ExcelColumn tempColumn)
{
column.Width = tempColumn.Width;
}
public string Apply(ExcelColumn <object> column, string value)
{
var formulaChars = new char[] { '=', '+', '-', '@', '|' };
return(value.OrEmpty().TrimStart(formulaChars));
}
public override int GetHashCode()
{
return(ExcelColumn.GetHashCode() + DbColumn.GetHashCode());
}
public List <ExcelSheet> Parse(ExcelUpload uploadModel)
{
List <ExcelSheet> returnSheets = new List <ExcelSheet>();
using (MemoryStream ms = new MemoryStream(uploadModel.ExcelFile))
{
SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(ms, false);
WorkbookPart wbPart = spreadsheet.WorkbookPart;
var sheets = wbPart.Workbook.Descendants <Sheet>();
foreach (Sheet sheet in sheets)
{
ExcelSheet newReturnSheet = new ExcelSheet();
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
Worksheet worksheet = wsPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
newReturnSheet.SheetName = sheet.Name;
var firstRowCells = worksheet.Descendants <Cell>().Where(y => GetCellRow(y.CellReference.Value) == 1);
foreach (var firstRowCell in firstRowCells)
{
ExcelColumn newReturnColumn = new ExcelColumn()
{
Letter = GetCellLetter(firstRowCell.CellReference.Value),
Name = GetCellLetter(firstRowCell.CellReference.Value)
};
if (uploadModel.FirstRowIsHeader)
{
newReturnColumn.Name = GetCellValue(firstRowCell, wbPart);
}
newReturnSheet.Columns.Add(newReturnColumn);
}
var rows = sheetData.Descendants <Row>();
foreach (var row in rows)
{
ExcelRow newReturnRow = new ExcelRow()
{
RowNumber = (int)row.RowIndex.Value
};
if (uploadModel.FirstRowIsHeader && newReturnRow.RowNumber == 1)
{
continue;
}
foreach (ExcelColumn column in newReturnSheet.Columns)
{
var cellReference = string.Format("{0}{1}", column.Letter, newReturnRow.RowNumber);
Cell cell = worksheet.Descendants <Cell>().SingleOrDefault(y => y.CellReference.Value == cellReference);
ExcelCell newReturnCell = new ExcelCell()
{
Letter = column.Letter,
Value = GetCellValue(cell, wbPart)
};
newReturnRow.Cells.Add(newReturnCell);
}
newReturnSheet.Rows.Add(newReturnRow);
}
returnSheets.Add(newReturnSheet);
}
}
return(returnSheets);
}
public void SetExcelColumnWidth(ExcelColumn column, double width)
{
excel.SetColumnWidth(column, width);
}
private void SetHeaderColumn(ExcelColumn column, object columnData)
{
column.Style.Numberformat.Format = GetFormattingByColumnName(columnData);
column.Width = GetColumnWidthByColumnName(columnData);
}
/// <summary>
/// 设置表格列伸缩适应单元格文字长度
/// </summary>
/// <param name="column"></param>
public static void SetFit(this ExcelColumn column)
{
column.AutoFit();
}
/// <summary>
/// Process file stream to html string
/// </summary>
/// <param name="stream">Excel file stream</param>
/// <returns>Html string</returns>
public string Process(Stream stream)
{
ExcelPackage p = new ExcelPackage(stream);
// Get the first sheet
var sheet = p.Workbook.Worksheets.FirstOrDefault();
if (sheet == null)
{
throw new Exception("No worksheets found");
}
var numberColumns = sheet.Dimension.End.Column;
var numberRows = sheet.Dimension.End.Row;
StringBuilder s = new StringBuilder();
string[] tableStyles = new string[]
{
"width: 100%",
"table-layout: fixed",
"border-collapse: collapse"
};
s.Append($"<table style='{string.Join("; ", tableStyles)}'>");
// For each row
for (int i = 1; i <= numberRows; i++)
{
ExcelRow row = sheet.Row(i);
s.Append($"<tr style='height: {Utils.FormatNumber(row.Height)}pt'>");
// For each column
for (int j = 1; j <= numberColumns; j++)
{
ExcelColumn column = sheet.Column(j);
ExcelRange cell = sheet.Cells[i, j];
int colspan = 1;
int rowspan = 1;
if (!cell.Merge || (cell.Merge && IsFirstMergeRange(sheet, cell.Address, ref colspan, ref rowspan)))
{
StylesParser styles = new StylesParser(cell);
//styles.Add($"width: {Utils.FormatNumber(sheet.Column(j).Width)}pt;");
if (options.Debug)
{
styles.Add("border: 1px solid black;");
}
s.Append($"<td rowspan='{rowspan}' colspan='{colspan}' style='{styles.Parse()}'>");
if (cell.Value != null)
{
s.Append(cell.Value.ToString());
}
s.Append("</td>");
}
}
s.Append("</tr>");
}
s.Append("</table>");
return(FormatHtml(s.ToString()));
}
/// <summary>
/// 设置表格列伸缩适应单元格文字长度
/// </summary>
/// <param name="column"></param>
/// <param name="width"></param>
public static void SetFit(this ExcelColumn column, double width)
{
column.AutoFit(width);
}
/// <summary>
/// Sets the font of ExcelColumn from a Font object
/// </summary>
/// <param name="column"></param>
/// <param name="font"></param>
/// <returns></returns>
public static ExcelColumn SetFont(this ExcelColumn column, Font font)
{
column.Style.SetFont(font);
return(column);
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="col"></param>
/// <param name="width"></param>
public static void SetColumnWidth(this ExcelColumn col, int width = 15)
{
col.Width = width;
}
/// <summary>
/// Sets the background color of ExcelColumn from a Color object
/// </summary>
/// <param name="column"></param>
/// <param name="backgroundColor"></param>
/// <param name="fillStyle"></param>
/// <returns></returns>
public static ExcelColumn SetBackgroundColor(this ExcelColumn column, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid)
{
column.Style.SetBackgroundColor(backgroundColor, fillStyle);
return(column);
}
private void AddNewStyleColumn(StyleBase sender, StyleChangeEventArgs e, ExcelWorksheet ws, Dictionary <int, int> styleCashe, ExcelColumn column, int s)
{
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(0, column.ColumnMin, newId);
}
}
/// <summary>
/// Sets the vertical alignment of ExcelColumn
/// </summary>
/// <param name="column"></param>
/// <param name="verticalAlignment"></param>
/// <returns></returns>
public static ExcelColumn SetVerticalAlignment(this ExcelColumn column, ExcelVerticalAlignment verticalAlignment)
{
column.Style.SetVerticalAlignment(verticalAlignment);
return(column);
}
public EpExcelColumn(ExcelWorksheet sheet, int columnNum)
{
_epWorkSheet = sheet;
_columnNum = columnNum;
_epColumn = sheet.Column(columnNum);
}
