/// <summary>
/// Copy a range of cells from source worksheet to destination worksheet
/// </summary>
/// <param name="srcWs"></param>
/// <param name="destWs"></param>
/// <param name="srcRange"></param>
/// <param name="destRange"></param>
private void CopyFrom(ExcelRange srcRange, ExcelRange destRange)
{
//bug:out of range
srcRange.Copy(destRange);
}
private void CreateSheet(ExcelPackage ep, Schema schema)
{
string connectionString = ConfigurationManager.ConnectionStrings["DefaultDatabase"].ConnectionString;
string dbName = ConfigurationManager.AppSettings["DatabaseName"];
ep.Workbook.Worksheets.Add(schema.Name);
var ws = ep.Workbook.Worksheets.Where(s => s.Name.Equals(schema.Name)).FirstOrDefault();
var horizontalAligment = ExcelHorizontalAlignment.Center;
var verticalAligment = ExcelVerticalAlignment.Center;
var rowSchemaAndTableOne = 1;
var rowInformationOne = 2;
if (ws != null)
{
ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
//ExcelRange schemaCell = null;
ExcelRange tableCell = null;
ExcelRange columnValueCellOne = null;
ExcelRange dataTypeValueCellOne = null;
ExcelRange nullableValueCellOne = null;
using (SqlConnection connection = new SqlConnection(connectionString)) {
connection.Open();
foreach (var table in schema.Tables)
{
tableCell = ws.Cells[rowSchemaAndTableOne, 1, rowSchemaAndTableOne, 2];
var tableCellBorder = tableCell.Style.Border;
tableCell.Merge = true;
tableCell.Style.Font.Bold = true;
tableCell.Value = String.Format("[{0}].[{1}]", schema.Name, table.TableName);
tableCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
tableCell.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
tableCellBorder.Bottom.Style = tableCellBorder.Top.Style = tableCellBorder.Left.Style = tableCellBorder.Right.Style = ExcelBorderStyle.Thin;
tableCell.Style.HorizontalAlignment = horizontalAligment;
tableCell.Style.VerticalAlignment = verticalAligment;
using (SqlCommand command = new SqlCommand(this.TableInfo(dbName, schema.Name, table.TableName), connection)) {
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
columnValueCellOne = ws.Cells[rowInformationOne, 1];
columnValueCellOne.Value = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
dataTypeValueCellOne = ws.Cells[rowInformationOne, 2];
dataTypeValueCellOne.Value = reader.GetString(reader.GetOrdinal("DATA_TYPE"));
nullableValueCellOne = ws.Cells[rowInformationOne, 3];
nullableValueCellOne.Value = reader.GetString(reader.GetOrdinal("IS_NULLABLE"));
rowInformationOne++;
}
}
}
rowInformationOne++;
rowSchemaAndTableOne = rowInformationOne;
rowInformationOne++;
}
}
}
}
private static void SetCellValue(ExcelRange range, int mergeIndex, DataType dataType, DataRow row, object rowIndex)
{
object value = null;
if (rowIndex.GetType() == typeof(int)) value = row[(int)rowIndex];
else value = row[rowIndex.ToString()];
if (dataType == DataType.Date)
{
DateTime dt = Cast.DateTime(value, new DateTime(1900, 1, 1));
if (dt > new DateTime(1900, 1, 1)) value = dt.ToString("yyyy-MM-dd");
else value = " ";
}
if (mergeIndex == 0 && dataType == DataType.NumberText)
range.Value = "'" + Cast.String(value);
else if (mergeIndex == 0)
range.Value = value;
else if (mergeIndex > 0)
{
string s = Cast.String(range.Value).Trim();
s = s.Length <= 0 && dataType == DataType.NumberText ? "'" : s.Length > 0 ? s + " " : s;
range.Value = s + Cast.String(value);
}
}
public async Task <string> ExportAsync <TData>(IEnumerable <TData> data
, Dictionary <string, Func <TData, object> > mappers
, string sheetName = "Sheet1")
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var p = new ExcelPackage();
p.Workbook.Properties.Author = "BlazorHero";
p.Workbook.Worksheets.Add(_localizer["Audit Trails"]);
var ws = p.Workbook.Worksheets[0];
ws.Name = sheetName;
ws.Cells.Style.Font.Size = 11;
ws.Cells.Style.Font.Name = "Calibri";
var colIndex = 1;
var rowIndex = 1;
var headers = mappers.Keys.Select(x => x).ToList();
foreach (var header in headers)
{
var cell = ws.Cells[rowIndex, colIndex];
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
var border = cell.Style.Border;
border.Bottom.Style =
border.Top.Style =
border.Left.Style =
border.Right.Style = ExcelBorderStyle.Thin;
cell.Value = header;
colIndex++;
}
var dataList = data.ToList();
foreach (var item in dataList)
{
colIndex = 1;
rowIndex++;
var result = headers.Select(header => mappers[header](item));
foreach (var value in result)
{
ws.Cells[rowIndex, colIndex++].Value = value;
}
}
using (ExcelRange autoFilterCells = ws.Cells[1, 1, dataList.Count + 1, headers.Count])
{
autoFilterCells.AutoFilter = true;
autoFilterCells.AutoFitColumns();
}
var byteArray = await p.GetAsByteArrayAsync();
return(Convert.ToBase64String(byteArray));
}
/// <summary>
/// This sample creates a new workbook from a template file containing a chart and populates it with Exchange rates from
/// the database and set the three series on the chart.
/// </summary>
/// <param name="connectionString">Connectionstring to the db</param>
/// <param name="template">the template</param>
/// <param name="outputdir">output dir</param>
/// <returns></returns>
public static string Run(string connectionString)
{
FileInfo template = FileInputUtil.GetFileInfo("17-FXReportFromDatabase", "GraphTemplate.xlsx");
using (ExcelPackage p = new ExcelPackage(template, true))
{
//Set up the headers
ExcelWorksheet ws = p.Workbook.Worksheets[0];
ws.Cells["A20"].Value = "Date";
ws.Cells["B20"].Value = "EOD Rate";
ws.Cells["B20:F20"].Merge = true;
ws.Cells["G20"].Value = "Change";
ws.Cells["G20:K20"].Merge = true;
ws.Cells["B20:K20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
using (ExcelRange row = ws.Cells["A20:G20"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
row.Style.Font.Color.SetColor(Color.White);
row.Style.Font.Bold = true;
}
ws.Cells["B21"].Value = "USD/SEK";
ws.Cells["C21"].Value = "USD/EUR";
ws.Cells["D21"].Value = "USD/INR";
ws.Cells["E21"].Value = "USD/CNY";
ws.Cells["F21"].Value = "USD/DKK";
ws.Cells["G21"].Value = "USD/SEK";
ws.Cells["H21"].Value = "USD/EUR";
ws.Cells["I21"].Value = "USD/INR";
ws.Cells["J21"].Value = "USD/CNY";
ws.Cells["K21"].Value = "USD/DKK";
using (ExcelRange row = ws.Cells["A21:K21"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
row.Style.Font.Color.SetColor(Color.Black);
row.Style.Font.Bold = true;
}
int startRow = 22;
//Connect to the database and fill the data
using (var sqlConn = new SQLiteConnection(connectionString))
{
int row = startRow;
sqlConn.Open();
using (var sqlCmd = new SQLiteCommand("SELECT date, SUM(Case when CurrencyCodeTo = 'SEK' Then rate Else 0 END) AS [SEK], SUM(Case when CurrencyCodeTo = 'EUR' Then rate Else 0 END) AS [EUR], SUM(Case when CurrencyCodeTo = 'INR' Then rate Else 0 END) AS [INR], SUM(Case when CurrencyCodeTo = 'CNY' Then rate Else 0 END) AS [CNY], SUM(Case when CurrencyCodeTo = 'DKK' Then rate Else 0 END) AS [DKK] FROM CurrencyRate where [CurrencyCodeFrom]='USD' AND CurrencyCodeTo in ('SEK', 'EUR', 'INR','CNY','DKK') GROUP BY date ORDER BY date", sqlConn))
{
using (var sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 22 onwards
while (sqlReader.Read())
{
ws.Cells[row, 1].Value = sqlReader[0];
ws.Cells[row, 2].Value = sqlReader[1];
ws.Cells[row, 3].Value = sqlReader[2];
ws.Cells[row, 4].Value = sqlReader[3];
ws.Cells[row, 5].Value = sqlReader[4];
ws.Cells[row, 6].Value = sqlReader[5];
row++;
}
}
//Set the numberformat
ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[startRow, 2, row - 1, 6].Style.Numberformat.Format = "#,##0.0000";
//Set the Formulas
ws.Cells[startRow + 1, 7, row - 1, 11].Formula = $"B${startRow}/B{startRow+1}-1";
ws.Cells[startRow, 7, row - 1, 11].Style.Numberformat.Format = "0.00%";
}
//Set the series for the chart. The series must exist in the template or the program will crash.
var chart = ((ExcelLineChart)ws.Drawings["SampleChart"]);
chart.Title.Text = "Exchange rate %";
chart.Series[0].Header = "USD/SEK";
chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
chart.Series[1].Header = "USD/EUR";
chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 8, row - 1, 8);
chart.Series[2].Header = "USD/INR";
chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 9, row - 1, 9);
var serie = chart.Series.Add("'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 10, row - 1, 10),
"'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1));
serie.Header = "USD/CNY";
serie.Marker.Style = eMarkerStyle.None;
serie = chart.Series.Add("'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 11, row - 1, 11),
"'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1));
serie.Header = "USD/DKK";
serie.Marker.Style = eMarkerStyle.None;
chart.Legend.Position = eLegendPosition.Bottom;
//Set the chart style
chart.StyleManager.SetChartStyle(236);
}
//Get the documet as a byte array from the stream and save it to disk. (This is useful in a webapplication) ...
var bin = p.GetAsByteArray();
FileInfo file = FileOutputUtil.GetFileInfo("17-FxReportFromDatabase.xlsx");
File.WriteAllBytes(file.FullName, bin);
return(file.FullName);
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
if (txtMonth.Text != "" && txtMonth.Text != null)
{
#region "For Under 96%+5K"
string count1 = "0";
string count2 = "0";
calculateMonth = GeneralUtility.ConvertMonthYearStringFormat(txtMonth.Text.Trim());
//calculateFromDate = calculateMonth + "16"; //calculateFromDate
calculateToDate = calculateMonth + "15"; //calculateToDate
string calculatedate = new AccuracyPercentage().FindPreviousMonth(calculateToDate);
DateTime calfromtime = DateTime.Parse(calculatedate);
var cdate = GeneralUtility.ConvertSystemDateStringFormat(calfromtime);
string cmonth = cdate.Substring(4, 2);
string cyear = cdate.Substring(0, 4);
cMonth2 = cyear + cmonth;
//calculateToDate = cMonth2 + "15"; //calculateToDate
calculateFromDate = cMonth2 + "16"; //calculateFromDate
if ((txtFromDate.Text == "" || txtFromDate.Text == null) || (txtToDate.Text == "" || txtToDate.Text == null))
{
MessageBox.MessageShow(this.GetType(), "Please Choose From/To Date!.", ClientScript);
return;
}
else
{
fromDate = GeneralUtility.ConvertSystemDateStringFormat(txtFromDate.Text);
toDate = GeneralUtility.ConvertSystemDateStringFormat(txtToDate.Text);
DateTime dtStartDate = DateTime.ParseExact(GeneralUtility.ConvertDisplayDateStringFormat(this.txtFromDate.Text.Trim()), "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime dtEndDate = DateTime.ParseExact(GeneralUtility.ConvertDisplayDateStringFormat(this.txtToDate.Text.Trim()), "dd/MM/yyyy", CultureInfo.InvariantCulture);
count1 = new Probes().CheckDate(calculateFromDate, calculateToDate, fromDate);
count2 = new Probes().CheckDate(calculateFromDate, calculateToDate, toDate);
if (count1 == "0" || count2 == "0")
{
MessageBox.MessageShow(this.GetType(), "Please Check From/To Date Range!.", ClientScript);
return;
}
if ((dtEndDate.Date != dtStartDate.Date))
{
if (!(dtEndDate.Date > dtStartDate.Date))
{
MessageBox.MessageShow(this.GetType(), "Invalid End Date.", ClientScript);
//this.txtToDate.Focus();
return;
}
}
string fromYear = fromDate.Substring(0, 4);
string fromMonth = fromDate.Substring(4, 2);
string toYear = toDate.Substring(0, 4);
string toMonth = toDate.Substring(4, 2);
if (fromYear == toYear && fromMonth == toMonth)
{
Month1 = fromYear + fromMonth;
Month2 = string.Empty;
FromDate2 = string.Empty;
ToDate2 = string.Empty;
}
else
{
//string comparedate = new AccuracyPercentage().FindMonthAndYear(fromDate);
//string comparemonth = comparedate.Substring(3, 2);
//string compareyear = comparedate.Substring(6, 4);
string comparedate = new AccuracyPercentage().FindMonthAndYear(fromDate);
DateTime fromtime = DateTime.Parse(comparedate);
var fdate = GeneralUtility.ConvertSystemDateStringFormat(fromtime);
string comparemonth = fdate.Substring(4, 2);
string compareyear = fdate.Substring(0, 4);
if (compareyear != toYear || comparemonth != toMonth)
{
MessageBox.MessageShow(this.GetType(), "Please Check FromDate and ToDate!.", ClientScript);
return;
}
Month1 = fromYear + fromMonth;
Month2 = compareyear + comparemonth;
FromDate2 = Month2 + "01";
ToDate2 = toDate;
toDate = new AccuracyPercentage().FindLastDayOfMonth(fromDate);
}
}
//var branchcode = string.Empty;
//if (ddlCenterName.SelectedItem.Value != "All")
//{
// branchcode = ddlCenterName.SelectedValue.ToString();
//}
var branchcode = string.Empty;
if (ddlCenterName.SelectedIndex != 0)
{
branchcode = ddlCenterName.SelectedValue.ToString();
}
else
{
MessageBox.MessageShow(this.GetType(), "Please Choose Center!.", ClientScript);
return;
}
new Probes
{
Criteria = new PPP_Project.Criteria.ImportJobsCriteria
{
CenterName = branchcode,
FromDate = calculateFromDate,
ToDate = calculateToDate,
Month1 = cMonth2,
Month2 = Month2,
FromDate2 = FromDate2,
ToDate2 = ToDate2,
}
}.CalculateTotalProbes();
int probesQty = 7500;
var probesqtyInfoEntity = new ProbesQty().FindQty();
if (probesqtyInfoEntity != null)
{
probesQty = probesqtyInfoEntity.Qty;
}
var finalAccuracyList = new AccuracyPercentage().FindByMonthForUnderGSS(branchcode, cMonth2, probesQty, 96, fromDate, toDate, Month2, FromDate2, ToDate2);
DataTable attTbl = new DataTable();
attTbl.Clear();
attTbl.Columns.Clear();
var result = (from dd in finalAccuracyList
orderby dd.QAT
select dd).ToList();
// Convert to DataTable.
DataTable table = ConvertToDataTable(result);
table.Columns.Remove("RQuality");
//table.Columns.Remove("AmountforProbes");
//table.Columns.Remove("AmountforAccuracy");
//table.Columns.Remove("PPPA");
//table.Columns.Remove("Center");
table.Columns.Remove("Month");
//int sum = finalProbesdt.AsEnumerable().Sum(s => s.Field<int>("Total Probes"));
var yrm = GeneralUtility.ConvertSystemDateStringFormat(calculateToDate);
int yr = Convert.ToInt32(yrm.Substring(0, 4).ToString());
int mth = Convert.ToInt32(yrm.Substring(4, 2).ToString());
DateTime date = new DateTime(yr, mth, 1);
var mm = date.ToString("MMMM");
var yy = date.ToString("yy");
if (result.Count().Equals(0))
{
MessageBox.MessageShow(GetType(), "No Export Data.!", ClientScript);
}
else
{
var fileName = "AccuracyUnder96%" + mm + "'" + yy + ".xlsx";
int count = 0;
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Probes_List_Export.xlsx", System.Text.Encoding.UTF8));
this.Response.AddHeader(
"content-disposition",
string.Format("attachment; filename={0}", fileName));
ExcelPackage pkg = new ExcelPackage();
using (pkg)
{
ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Under 96%");
ws.Cells["A1"].LoadFromDataTable(table, true);
#region "No need region"
// using (ExcelRange rng = ws.Cells["A1:W1"])
// using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
using (ExcelRange rng = ws.Cells[1, 1, 1, table.Columns.Count])
{
rng.Style.Font.Bold = true;
//Set Pattern for the background to Solid
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
//Set color to dark blue
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
// rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(122,160,205));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
//string PersentageCellFormat = "#0.00%";
//string PersentageCellFormat = "#0.00%";
//using (ExcelRange Rng = ws.Cells["D2"])
//{
// Rng.Style.Numberformat.Format = PersentageCellFormat;
// // Rng.Value = Convert.ToDecimal(39.9);
//}
//ws.Cells[2, 4].Style.Numberformat.Format = "0.00\\%";
// ws.Cells[2, 4].Style.Numberformat.Format = "#0.00%";
// ws.Cells[2, 4].Style.Numberformat.Format = "#0\\.00%";
//ws.Cells["A1:MT"].Style.Font.Bold = true;
////ws.Cells["A1"].Style.Font.Bold = true;
//using (ExcelRange rng = ws.Cells["A1:U" + (colcount - 1)])
//{
// rng.Style.Font.Bold = true;
//}
//var headerCell = ws.Cells["A5:MT"];
//headerCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
//headerCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BurlyWood);
//var headerFont = headerCell.Style.Font;
//headerFont.Bold = true;
//ws.Cells[1, 30].Style.Fill.PatternType = ExcelFillStyle.Solid;
//ws.Cells[1, 30].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
//ws.Cells[1, 30].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//ws.Cells[1, 30].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
#endregion
if (result.Count() > 0)
{
count = result.Count() + 2;
// pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Value = sum;//result.Sum(x => x.TotalProbes);
// pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.Bold = true;
// pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.UnderLine = true;
// pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Font.Color.SetColor(System.Drawing.Color.Blue);
// pkg.Workbook.Worksheets.FirstOrDefault().Cells[count, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
pkg.Workbook.Worksheets.FirstOrDefault().DefaultColWidth = 20;
pkg.Workbook.Worksheets.FirstOrDefault().Row(1).Height = 25;
// using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
// var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells["A1:MP" + (count - 1)]; //+ (count - 1)
var modelTable = pkg.Workbook.Worksheets.FirstOrDefault().Cells[ws.Dimension.Start.Row, 1, ws.Dimension.Start.Row + table.Rows.Count, table.Columns.Count]; //+ (count - 1)
var border = modelTable.Style.Border.Top.Style = modelTable.Style.Border.Left.Style = modelTable.Style.Border.Right.Style = modelTable.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
pkg.Workbook.Properties.Title = "Attempts";
this.Response.BinaryWrite(pkg.GetAsByteArray());
this.Response.End();
}
} // End Export Block
#endregion // End Probes
}
else
{
MessageBox.MessageShow(this.GetType(), "Please Choose Export Date!.", ClientScript);
}
}
/// <summary>
/// Задать значение ячейке, установить ее формат и стиль
/// </summary>
/// <param name="range">Диапазон ячеек</param>
/// <param name="value">Значение</param>
/// <param name="style">Стиль</param>
/// <param name="displayType">Тип данных</param>
public static ExcelRange SetFormattedValue(this ExcelRange range, object value, ExcelCellStyle style, ValueDisplayType displayType = ValueDisplayType.Default)
{
return(range.SetFormattedValue(value, displayType).ApplyStyle(style));
}
public static byte[] ExportExcel(DataTable dataTable, string heading = "")
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0} Data", heading));
int startRowFrom = String.IsNullOrEmpty(heading) ? 1 : 3;
foreach (DataRow row in dataTable.Rows)
{
foreach (DataColumn col in dataTable.Columns)
{
if (row.IsNull(col) && col.DataType == typeof(string))
{
row.SetField(col, String.Empty);
}
}
}
// add the content into the Excel file
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
int colNumber = 1;
foreach (DataColumn col in dataTable.Columns)
{
if (col.DataType == typeof(DateTime))
{
workSheet.Column(colNumber).Style.Numberformat.Format = "MM/dd/yyyy hh:mm:ss AM/PM";
}
colNumber++;
}
// autofit width of cells with small content
int columnIndex = 1;
foreach (DataColumn column in dataTable.Columns)
{
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
workSheet.Column(columnIndex).AutoFit();
columnIndex++;
}
// format header - bold, yellow on black
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
// format cells - add borders
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
// removed ignored columns
for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
{
if (i == 0)
{
continue;
}
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 5;
}
result = package.GetAsByteArray();
}
return(result);
}
/// <summary>
/// Объединить ячейки
/// </summary>
public static ExcelRange MergeRange(this ExcelRange range)
{
range.Merge = true;
range.ChangeRangeStyle(verticalAlignment: ExcelVerticalAlignment.Center, horizontalAlignment: ExcelHorizontalAlignment.CenterContinuous);
return(range);
}
/// <summary>
/// Разъединить ячейки
/// </summary>
public static ExcelRange UnMergeRange(this ExcelRange range)
{
range.Merge = false;
return(range);
}
public string ExportToEPPExcel(DataSet ds, string fileName)
{
try
{
ExcelPackage ep = new ExcelPackage();
ExcelWorksheet ewsSummary;
ExcelWorksheet ewsDetailed;
DataTable dtSummary;
DataTable dtDetailed;
string pathToWriteFile = ConfigurationManager.AppSettings["fileExportPath"].ToString();
if (ds.Tables.Contains("dtSummary") && ds.Tables["dtSummary"] != null && ds.Tables["dtSummary"].Rows.Count > 0)
{
dtSummary = new DataTable();
dtSummary = ds.Tables["dtSummary"];
if (dtSummary != null && dtSummary.Rows.Count > 0)
{
dtSummary.Columns[0].ColumnName = "Material Number";
dtSummary.Columns[1].ColumnName = "Plant Code";
dtSummary.Columns[2].ColumnName = "Parent Supplier";
dtSummary.Columns[3].ColumnName = "Supplier Site ";
dtSummary.Columns[4].ColumnName = "Receipt Qty";
dtSummary.Columns[5].ColumnName = "Spend";
ewsSummary = ep.Workbook.Worksheets.Add("Summary Receipts");
int colcnt = dtSummary.Columns.Count;
int rowcnt = dtSummary.Rows.Count;
using (ExcelRange col = ewsSummary.Cells[2, 5, rowcnt + 1, 5])
{
col.Style.Numberformat.Format = "0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
using (ExcelRange col = ewsSummary.Cells[2, 6, rowcnt + 1, 6])
{
col.Style.Numberformat.Format = "$#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
string colname = "";
ewsSummary.Cells["A1"].LoadFromDataTable(dtSummary, true, OfficeOpenXml.Table.TableStyles.None);
ewsSummary.Cells[1, 1, 1, colcnt].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ewsSummary.Cells[1, 1, 1, colcnt].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
ewsSummary.Cells[1, 1, 1, colcnt].Style.Font.Color.SetColor(Color.White);
ewsSummary.Cells[1, 1, 1, colcnt].Style.Font.Bold = true;
ewsSummary.Cells[1, 1, 1, colcnt].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.General;
ewsSummary.Column(1).Width = 18;
ewsSummary.Column(2).Width = 18;
ewsSummary.Column(3).Width = 18;
ewsSummary.Column(4).Width = 18;
ewsSummary.Column(5).Width = 18;
}
}
if (ds.Tables.Contains("dtDetailed") && ds.Tables["dtDetailed"] != null && ds.Tables["dtDetailed"].Rows.Count > 0)
{
dtDetailed = new DataTable();
dtDetailed = ds.Tables["dtDetailed"];
if (dtDetailed != null && dtDetailed.Rows.Count > 0)
{
dtDetailed.Columns[0].ColumnName = "Material Number";
dtDetailed.Columns[1].ColumnName = "Received In";
dtDetailed.Columns[2].ColumnName = "Plant Code";
dtDetailed.Columns[3].ColumnName = "Receipt Date";
dtDetailed.Columns[4].ColumnName = "PO Number";
dtDetailed.Columns[5].ColumnName = "PO Line Number";
dtDetailed.Columns[6].ColumnName = "Receipt Qty";
dtDetailed.Columns[7].ColumnName = "Unit Price";
dtDetailed.Columns[8].ColumnName = "Spend";
dtDetailed.Columns[9].ColumnName = "Planner Code";
dtDetailed.Columns[10].ColumnName = "Planner";
dtDetailed.Columns[11].ColumnName = "Parent Supplier";
dtDetailed.Columns[12].ColumnName = "Supplier Site ";
ewsDetailed = ep.Workbook.Worksheets.Add("Detailed Receipts");
int colcnt = dtDetailed.Columns.Count;
int rowcnt = dtDetailed.Rows.Count;
string colname = "";
ewsDetailed.Cells["A1"].LoadFromDataTable(dtDetailed, true, OfficeOpenXml.Table.TableStyles.None);
ewsDetailed.Cells[1, 1, 1, colcnt].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ewsDetailed.Cells[1, 1, 1, colcnt].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
ewsDetailed.Cells[1, 1, 1, colcnt].Style.Font.Color.SetColor(Color.White);
ewsDetailed.Cells[1, 1, 1, colcnt].Style.Font.Bold = true;
ewsDetailed.Cells[1, 1, 1, colcnt].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.General;
using (ExcelRange col = ewsDetailed.Cells[2, 4, rowcnt + 1, 4])
{
col.Style.Numberformat.Format = "MM/dd/yyyy";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
using (ExcelRange col = ewsDetailed.Cells[2, 7, rowcnt + 1, 7])
{
col.Style.Numberformat.Format = "0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
using (ExcelRange col = ewsDetailed.Cells[2, 8, rowcnt + 1, 8])
{
col.Style.Numberformat.Format = "$#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
using (ExcelRange col = ewsDetailed.Cells[2, 9, rowcnt + 1, 9])
{
col.Style.Numberformat.Format = "$#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
ewsDetailed.Column(1).Width = 20;
ewsDetailed.Column(2).Width = 20;
ewsDetailed.Column(3).Width = 20;
ewsDetailed.Column(4).Width = 20;
ewsDetailed.Column(5).Width = 20;
ewsDetailed.Column(6).Width = 20;
ewsDetailed.Column(7).Width = 20;
ewsDetailed.Column(8).Width = 20;
ewsDetailed.Column(9).Width = 20;
ewsDetailed.Column(10).Width = 20;
ewsDetailed.Column(11).Width = 20;
ewsDetailed.Column(12).Width = 30;
ewsDetailed.Column(13).Width = 40;
}
}
FileInfo fi = new FileInfo(pathToWriteFile + @"\" + fileName + ".xlsx");
if (fi.Exists)
{
fi.Delete();
}
//ep.SaveAs(fi);
if (ep.Workbook.Worksheets.Count > 0)
{
using (FileStream aFile = new FileStream(pathToWriteFile + @"\" + fileName + ".xlsx", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
{
aFile.Seek(0, SeekOrigin.Begin);
ep.SaveAs(aFile);
aFile.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(fileName + ".xlsx");
}
/// <summary>
/// Método para generar el documento excel del resumen
/// </summary>
/// <param name="datosResumen">Los datos a utilizar para el resumen general</param>
public static void CrearDocumentoResumenExcel(DatosGeneralesResumen datosResumen, List <VacaModel> listaVacas)
{
//Se crea una instancia del paquete de excel del documento a utilizar
ExcelPackage documentoExcel = new ExcelPackage();
//Se crea la hoja que se va a generar
ExcelWorksheet hojaResumen = documentoExcel.Workbook.Worksheets.Add(TITULO_RESUMEN);
//Se establece el primer rango de celdas a utilizar para la información general, con sus colores
ExcelRange celdasInformacionGeneral = hojaResumen.Cells[1, 1, 8, 4];
celdasInformacionGeneral.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
celdasInformacionGeneral.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(204, 255, 204));
celdasInformacionGeneral[1, 4, 8, 4].Style.Font.Color.SetColor(Color.Red);
//Se completan las celdas con sus valores respectivos
celdasInformacionGeneral[1, 1].Value = "Fecha referencia";
celdasInformacionGeneral[1, 4].Value = datosResumen.fechaActual;
celdasInformacionGeneral[2, 1].Value = "Número hembras consideradas";
celdasInformacionGeneral[2, 4].Value = datosResumen.hembrasConsideradas;
celdasInformacionGeneral[3, 1].Value = "Hembras que han parido";
celdasInformacionGeneral[3, 4].Value = datosResumen.hembrasParido;
celdasInformacionGeneral[4, 1].Value = "IEP Prom. Histórico, meses";
celdasInformacionGeneral[4, 4].Value = datosResumen.iepPromHistoricoMeses;
celdasInformacionGeneral[5, 1].Value = "% parición histórico";
celdasInformacionGeneral[5, 4].Value = datosResumen.porcParicionHistorico;
celdasInformacionGeneral[6, 1].Value = "Último IEP cada vaca, meses";
celdasInformacionGeneral[6, 4].Value = datosResumen.ultimoIEPVacaMeses;
celdasInformacionGeneral[7, 1].Value = "Último % parición ";
celdasInformacionGeneral[7, 4].Value = datosResumen.ultimoPorcParicion;
celdasInformacionGeneral[8, 1].Value = "Promedio partos hato";
celdasInformacionGeneral[8, 4].Value = datosResumen.promPartosHato;
//Generación de la lista de las vacas con su respectiva información
ExcelRange celdasListaVacas = hojaResumen.Cells[9, 1, 9 + listaVacas.Count, 12];
//Estilos
celdasListaVacas.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
celdasListaVacas[9, 1, 9, 12].Style.WrapText = true;
celdasListaVacas.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
celdasListaVacas.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
celdasListaVacas[9, 1, 9, 12].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(182, 221, 232));
celdasListaVacas[9, 1, 9, 12].Style.Font.Bold = true;
celdasListaVacas[9, 1, 9, 12].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
celdasListaVacas[9, 1, 9, 12].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
celdasListaVacas[9, 1, 9, 12].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
celdasListaVacas[9, 1, 9, 12].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
//Titulos
celdasListaVacas[9, 1].Value = "Número de orden";
celdasListaVacas[9, 2].Value = "Número de la vaca";
celdasListaVacas[9, 3].Value = "Número trazable de la vaca";
celdasListaVacas[9, 4].Value = "Edad a 1er parto, meses";
celdasListaVacas[9, 5].Value = "Nº partos";
celdasListaVacas[9, 6].Value = "Edad de la última cría, meses";
celdasListaVacas[9, 7].Value = "Fecha destete a 7 meses, última cría";
celdasListaVacas[9, 8].Value = "IEP promedio /cada/vaca, meses";
celdasListaVacas[9, 9].Value = "Último IEP cada vaca, meses";
celdasListaVacas[9, 10].Value = "Fecha de última monta o IA";
celdasListaVacas[9, 11].Value = "Gestación días";
celdasListaVacas[9, 12].Value = "Fecha parto";
//Se agregan las filas con la información de las vacas
if (listaVacas.Count > 0)
{
celdasListaVacas[10, 1, 9 + listaVacas.Count, 12].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(216, 216, 216));
celdasListaVacas[10, 1, 9 + listaVacas.Count, 12].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
celdasListaVacas[10, 1, 9 + listaVacas.Count, 12].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
for (int iterador = 0; iterador < listaVacas.Count; iterador++)
{
celdasListaVacas[10 + iterador, 1].Value = iterador + 1;
celdasListaVacas[10 + iterador, 2].Value = listaVacas[iterador].nombre;
celdasListaVacas[10 + iterador, 3].Value = listaVacas[iterador].pkNumeroTrazable;
celdasListaVacas[10 + iterador, 4].Value = listaVacas[iterador].edadAPrimerPartoMeses;
celdasListaVacas[10 + iterador, 5].Value = listaVacas[iterador].numeroDePartos;
celdasListaVacas[10 + iterador, 6].Value = listaVacas[iterador].edadUltimaCria;
celdasListaVacas[10 + iterador, 7].Value = listaVacas[iterador].fechaDesteteUltimaCria;
celdasListaVacas[10 + iterador, 8].Value = listaVacas[iterador].iepPromedioMeses;
celdasListaVacas[10 + iterador, 9].Value = listaVacas[iterador].ultimoIEPMeses;
celdasListaVacas[10 + iterador, 10].Value = listaVacas[iterador].fechaUltimaMonta;
celdasListaVacas[10 + iterador, 11].Value = listaVacas[iterador].gestacionDias;
celdasListaVacas[10 + iterador, 12].Value = listaVacas[iterador].fechaParto;
}
}
//Se guarda el documento
string ubicacionDocumentos = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
string nombreDocumentoResumen = ubicacionDocumentos + @"\" + "Resumen_" + DateTime.Now.ToString().Replace("/", ".").Replace(":", ".").Replace(" ", "_").Replace("\\", ".") + ".xlsx";
try
{
documentoExcel.SaveAs(new FileInfo(nombreDocumentoResumen));
//Se muestra el mensaje que indica al usuario en donde quedó el documento
Utilities.MostrarMessageBox(MENSAJE_CORRECTO + nombreDocumentoResumen, TITULO_MENSAJE, MessageBoxButtons.OK, MessageBoxIcon.None);
}
catch
{
//Se muestra el mensaje que indica al usuario en donde quedó el documento
Utilities.MostrarMessageBox(Utilities.MENSAJE_ERROR, Utilities.TITULO_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//Se cierra el documento
documentoExcel.Dispose();
}
/// <summary>
/// 从Excel中加载数据（泛型）
/// </summary>
/// <typeparam name="T">每行数据的类型</typeparam>
/// <param name="FileName">Excel文件名</param>
/// <returns>泛型列表</returns>
public static IEnumerable <T> LoadFromExcel <T>(FileInfo existingFile) where T : new()
{
//FileInfo existingFile = new FileInfo(FileName);//如果本地地址可以直接使用本方法，这里是直接拿到了文件
List <T> resultList = new List <T>();
Dictionary <string, int> dictHeader = new Dictionary <string, int>();
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int colStart = worksheet.Dimension.Start.Column; //工作区开始列
int colEnd = worksheet.Dimension.End.Column; //工作区结束列
int rowStart = worksheet.Dimension.Start.Row; //工作区开始行号
int rowEnd = worksheet.Dimension.End.Row; //工作区结束行号
//将每列标题添加到字典中
for (int i = colStart; i <= colEnd; i++)
{
dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i;
}
List <PropertyInfo> propertyInfoList = new List <PropertyInfo>(typeof(T).GetProperties());
for (int row = rowStart + 1; row <= rowEnd; row++)
{
T result = new T();
//为对象T的各属性赋值
foreach (PropertyInfo p in propertyInfoList)
{
try
{
ExcelRange cell = worksheet.Cells[row, dictHeader[p.Name]]; //与属性名对应的单元格
if (cell.Value == null)
{
continue;
}
switch (p.PropertyType.Name.ToLower())
{
case "string":
p.SetValue(result, cell.GetValue <String>());
break;
case "int16":
p.SetValue(result, cell.GetValue <Int16>());
break;
case "int32":
p.SetValue(result, cell.GetValue <Int32>());
break;
case "int64":
p.SetValue(result, cell.GetValue <Int64>());
break;
case "decimal":
p.SetValue(result, cell.GetValue <Decimal>());
break;
case "double":
p.SetValue(result, cell.GetValue <Double>());
break;
case "datetime":
p.SetValue(result, cell.GetValue <DateTime>());
break;
case "boolean":
p.SetValue(result, cell.GetValue <Boolean>());
break;
case "byte":
p.SetValue(result, cell.GetValue <Byte>());
break;
case "char":
p.SetValue(result, cell.GetValue <Char>());
break;
case "single":
p.SetValue(result, cell.GetValue <Single>());
break;
default:
break;
}
}
catch (KeyNotFoundException)
{ }
}
resultList.Add(result);
}
}
return(resultList);
}
/// <summary>
/// Provides basic HTML support by converting well-behaved HTML into appropriate RichText blocks.
/// HTML support is limited, and does not include font colors, sizes, or typefaces at this time,
/// and also does not support CSS style attributes. It does support line breaks using the BR tag.
///
/// This routine parses the HTML into RegEx pairings of an HTML tag and the text until the NEXT
/// tag (if any). The tag is parsed to determine the setting change to be applied to the last set
/// of settings, and if the text is not blank, a new block is added to rich text.
/// </summary>
/// <param name="range"></param>
/// <param name="html">The HTML to parse into RichText</param>
/// <param name="defaultFontName"></param>
/// <param name="defaultFontSize"></param>
public static void SetRichTextFromHtml(ExcelRange range, string html, string defaultFontName, short defaultFontSize)
{
// Reset the cell value, just in case there is an existing RichText value.
range.Value = "";
// Sanity check for blank values, skips creating Regex objects for performance.
if (String.IsNullOrEmpty(html))
{
range.IsRichText = false;
return;
}
// Change all BR tags to line breaks. http://epplus.codeplex.com/discussions/238692/
// Cells with line breaks aren't necessarily considered rich text, so this is performed
// before parsing the HTML tags.
html = System.Text.RegularExpressions.Regex.Replace(html, @"<br[^>]*>", "\r\n", RegexOptions.Compiled | RegexOptions.IgnoreCase);
string tag;
string text;
ExcelRichText thisrt = null;
bool isFirst = true;
// Get all pairs of legitimate tags and the text between them. This loop will
// only execute if there is at least one start or end tag.
foreach (Match m in System.Text.RegularExpressions.Regex.Matches(html, @"<(/?[a-z]+)[^<>]*>([\s\S]*?)(?=</?[a-z]+[^<>]*>|$)", RegexOptions.Compiled | RegexOptions.IgnoreCase))
{
if (isFirst)
{
// On the very first match, set up the initial rich text object with
// the defaults for the text BEFORE the match.
range.IsRichText = true;
thisrt = range.RichText.Add(CleanText(html.Substring(0, m.Index))); // May be 0-length
thisrt.Size = defaultFontSize; // Set the default font size
thisrt.FontName = defaultFontName; // Set the default font name
isFirst = false;
}
// Get the tag and the block of text until the NEXT tag or EOS. If there are HTML entities
// encoded, unencode them, they should be passed to RichText as normal characters (other
// than non-breaking spaces, which should be replaced with normal spaces, they break Excel.
tag = m.Groups[1].Captures[0].Value;
text = CleanText(m.Groups[2].Captures[0].Value);
if (thisrt.Text == "")
{
// The most recent rich text block wasn't *actually* used last time around, so update
// the text and keep it as the "current" block. This happens with the first block if
// it starts with a tag, and may happen later if tags come one right after the other.
thisrt.Text = text;
}
else
{
// The current rich text block has some text, so create a new one. RichText.Add()
// automatically applies the settings from the previous block, other than vertical
// alignment.
thisrt = range.RichText.Add(text);
}
// Override the settings based on the current tag, keep all other settings.
SetStyleFromTag(tag, thisrt);
}
if (thisrt == null)
{
// No HTML tags were found, so treat this as a normal text value.
range.IsRichText = false;
range.Value = CleanText(html);
}
else if (String.IsNullOrEmpty(thisrt.Text))
{
// Rich text was found, but the last node contains no text, so remove it. This can happen if,
// say, the end of the string is an end tag or unsupported tag (common).
range.RichText.Remove(thisrt);
// Failsafe -- the HTML may be just tags, no text, in which case there may be no rich text
// directives that remain. If that is the case, turn off rich text and treat this like a blank
// cell value.
if (range.RichText.Count == 0)
{
range.IsRichText = false;
range.Value = "";
}
}
}
/// <summary>
/// Изменения стиля диапазона ячеек
/// </summary>
/// <param name="range">диапазон ячеек</param>
/// <param name="merge">Объединить</param>
/// <param name="fontFamily">Семейство текста</param>
/// <param name="fontSize">Размер текста</param>
/// <param name="fontStyle">Стиль текста</param>
/// <param name="fontColor">Цвет текста</param>
/// <param name="backgroundColor">Цвет фона</param>
/// <param name="horizontalAlignment">Горизонтальное выравнивание</param>
/// <param name="verticalAlignment">Вертикальное выравнивание</param>
/// <param name="textWrap">Перенос по словам</param>
/// <param name="aroundBorder">Стиль внешнех границ</param>
/// <param name="aroundBorderColor">Цвет внешних границ</param>
/// <param name="innerBorder">Стиль внутренних границ</param>
/// <param name="innerBorderColor">Цвет внутренних границ</param>
/// <param name="textRotation">Поворот текста</param>
/// <param name="autofit">Авторазмер столбцов</param>
/// <param name="minfit">Минимальная ширина столбца</param>
/// <param name="maxfit">Максимальная ширина столбца</param>
/// <param name="fillStyle">Метод заливки</param>
/// <param name="indent">Отступ</param>
/// <param name="hidden">Невидимый</param>
/// <param name="locked">Заблокирован</param>
public static ExcelRange ChangeRangeStyle(this ExcelRange range, string fontFamily = null, int?fontSize = null, FontStyle?fontStyle = null,
Color?fontColor = null, Color?backgroundColor = null, ExcelHorizontalAlignment?horizontalAlignment = null,
ExcelVerticalAlignment?verticalAlignment = null, bool?textWrap = null, ExcelBorderStyle?aroundBorder = null,
Color?aroundBorderColor = null, ExcelBorderStyle?innerBorder = null, Color?innerBorderColor = null,
int?textRotation = null, ExcelFillStyle?fillStyle = null, int?indent = null, bool?hidden = null, bool?locked = null)
{
if (fontFamily != null)
{
range.Style.Font.SetFromFont(new Font(fontFamily, range.Style.Font.Size, GetRangeFontStyle(range)));
}
if (fontSize.HasValue)
{
range.Style.Font.Size = fontSize.Value;
}
if (fontStyle.HasValue)
{
SetRangeFontStyle(range, fontStyle.Value);
}
if (fontColor.HasValue)
{
range.Style.Font.Color.SetColor(fontColor.Value);
}
if (horizontalAlignment.HasValue)
{
range.Style.HorizontalAlignment = horizontalAlignment.Value;
}
if (verticalAlignment.HasValue)
{
range.Style.VerticalAlignment = verticalAlignment.Value;
}
if (fillStyle.HasValue)
{
range.Style.Fill.PatternType = fillStyle.Value;
}
if (backgroundColor.HasValue)
{
range.Style.Fill.BackgroundColor.SetColor(backgroundColor.Value);
}
if (textWrap.HasValue)
{
range.Style.WrapText = textWrap.Value;
}
if (indent.HasValue)
{
range.Style.Indent = indent.Value;
}
if (hidden.HasValue)
{
range.Style.Hidden = hidden.Value;
}
if (locked.HasValue)
{
range.Style.Locked = locked.Value;
}
if (innerBorder.HasValue)
{
range.Style.Border.Top.Style = innerBorder.Value;
range.Style.Border.Right.Style = innerBorder.Value;
range.Style.Border.Left.Style = innerBorder.Value;
range.Style.Border.Bottom.Style = innerBorder.Value;
}
if (innerBorderColor.HasValue && range.Style.Border.Bottom.Style != ExcelBorderStyle.None)
{
range.Style.Border.Top.Color.SetColor(innerBorderColor.Value);
range.Style.Border.Right.Color.SetColor(innerBorderColor.Value);
range.Style.Border.Left.Color.SetColor(innerBorderColor.Value);
range.Style.Border.Bottom.Color.SetColor(innerBorderColor.Value);
}
if (aroundBorder.HasValue)
{
range.Style.Border.BorderAround(aroundBorder.Value);
}
if (aroundBorderColor.HasValue && range.Style.Border.Bottom.Style != ExcelBorderStyle.None)
{
range.Style.Border.BorderAround(range.Style.Border.Bottom.Style, aroundBorderColor.Value);
}
if (textRotation.HasValue)
{
range.Style.TextRotation = textRotation.Value;
}
return(range);
}
private string formatExcel(string CellName, string type, ref ExcelWorksheet wsTemplate, string pathXMLCell)
{
try
{
type = type.ToLower();
Dictionary <string, string> Format = ReadXML(pathXMLCell + "/" + type);
switch (type)
{
case "merge":
#region "format merge"
using (ExcelRange r = wsTemplate.Cells[Format["MergeRanges"]])
{
r.Merge = true;
switch (Format["HorizontalAlignment"])
{
case "CenterContinuous":
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
break;
case "Center":
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
break;
case "Right":
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
break;
case "Distributed":
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Distributed;
break;
case "Fill":
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Fill;
break;
case "General":
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.General;
break;
case "Justify":
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Justify;
break;
default:
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
break;
}
}
#endregion
break;
case "wraptext":
wsTemplate.Cells[CellName].Style.WrapText = true;
break;
default:
break;
}
}
catch (Exception ex)
{
return(ex.Message);
}
return("");
}
/// <summary>
/// Автоматический подбор ширины столбца
/// </summary>
/// <param name="range">Диапазон ячеек</param>
/// <param name="maxWidth">Максимальная ширина. В ячейках с текстом шире будет установлен перенос по словам</param>
/// <param name="minWidth">Минимальная ширина столбца</param>
public static ExcelRange AutofitRangeColumns(this ExcelRange range, double maxWidth = -1, double minWidth = -1)
{
int startCol = range.Start.Column;
int startRow = range.Start.Row;
int endRow = range.End.Row;
int endCol = range.End.Column;
double columnMaxWidth;
double currentWidth;
//Проходим по всем колонкам
for (int c = startCol; c <= endCol; c++)
{
//Ищем максимальную строку в колонке
columnMaxWidth = 0;
for (int r = startRow; r <= endRow; r++)
{
if (!range[r, c].Merge)
{
string col = new string(range[r, c].Address.Where(z => !char.IsDigit(z)).ToArray());
int row = int.Parse(new string(range[r, c].Address.Where(a => char.IsDigit(a)).ToArray()));
if (range[r, c].Style.WrapText)//Для врапленных ячеек ширина берется по ширине самого длинного слова
{
currentWidth = range[r, c].Text.Split(' ').ToList().Max(m => m.Length);
}
else//Для остальных по ширине всего содержимого
{
currentWidth = Math.Ceiling(range[r, c].Text.Where(w => char.IsLetterOrDigit(w)).Count() + 0.5 * range[r, c].Text.Where(w => !char.IsLetterOrDigit(w)).Count()) + 1;
}
if (maxWidth > 0) //если установлена максимальная ширина столбца
{
if (currentWidth > maxWidth) //И размер строки больше нее
{
range[r, c].ChangeRangeStyle(textWrap: true); //Устанавливаем перенос по словам
columnMaxWidth = maxWidth; //Устанавливаем размер строки на максимальный
}
else
{
if (currentWidth > columnMaxWidth)
{
columnMaxWidth = currentWidth;
}
}
}
else
{
if (currentWidth > columnMaxWidth)
{
columnMaxWidth = currentWidth;
}
}
}
}
//Если установлен минимальный размер столбца, проверяем не меньше ли максимальное значение строки
if (columnMaxWidth < minWidth)
{
columnMaxWidth = minWidth;
}
//Устанавливаем размер столбца
range.Worksheet.Column(c).Width = columnMaxWidth;
}
//проверяем чтобы содержимое мерженных ячеек в выбранном диапазоне корректно отображалось
range.Worksheet.MergedCells.Where(w => range[w].Start.Row >= startRow && range[w].End.Row <= endRow && range[w].Start.Column >= startCol && range[w].End.Column <= endCol)
.ToList().ForEach(delegate(string adress)
{
int endColumn = range[adress].End.Column;
int startColumn = range[adress].Start.Column;
int row = range[adress].Start.Row;
var worksheet = range[adress].Worksheet;
if (range[row, startColumn].Style.WrapText)//Для врапленных ячеек ширина берется по ширине самого длинного слова
{
currentWidth = range[row, startColumn].Text.Split(' ').ToList().Max(m => m.Length);
}
else//Для остальных по ширине всего содержимого
{
currentWidth = Math.Ceiling(range[row, startColumn].Text.Where(w => char.IsLetterOrDigit(w)).Count() + 0.5 * range[row, startColumn].Text.Where(w => !char.IsLetterOrDigit(w)).Count());
}
//Если ширина содержимого мерженной ячейки больше ширины столбцов входящих в мерж, то к каждому столбцу прибавляем одинаковый размер
double mergedWidth = 0;
for (int i = startColumn; i <= endColumn; i++)
{
mergedWidth += worksheet.Column(i).Width;
}
if (currentWidth > mergedWidth)
{
double addWidth = Math.Ceiling((currentWidth - mergedWidth) / (endColumn - startColumn + 1)) + 1;
for (int i = startColumn; i <= endColumn; i++)
{
worksheet.Column(i).Width += addWidth;
}
}
});
return(range);
}
public static void ExcelToXml(string xmlRegName)
{
string className = "";
string excelName = "";
string xmlName = "";
//读取reg文件数据
Dictionary <string, SheetClass> allSheetClassDic = ReadReg(xmlRegName, ref className, ref excelName, ref xmlName);
//读取excel文件数据
string excelPath = EXCELPATH + excelName;
Dictionary <string, SheetData> allSheetDataDic = new Dictionary <string, SheetData>();
try
{
using (FileStream stream = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
ExcelWorksheets worksheetArray = excelPackage.Workbook.Worksheets;
for (int i = 0; i < worksheetArray.Count; i++)
{
SheetData sheetData = new SheetData();
ExcelWorksheet worksheet = worksheetArray[i + 1];//索引从1开始
string key = GetDicKey(allSheetClassDic, worksheet.Name);
if (string.IsNullOrEmpty(key))
{
Debug.Log("配置表在reg文件中不存在!请检查");
return;
}
SheetClass sheetClass = allSheetClassDic[key];
int colCount = worksheet.Dimension.End.Column;
int rowCount = worksheet.Dimension.End.Row;
for (int j = 0; j < sheetClass.VarList.Count; j++)
{
sheetData.AllName.Add(sheetClass.VarList[j].Name);
sheetData.AllType.Add(sheetClass.VarList[j].Type);
}
for (int j = 1; j < rowCount; j++)
{
RowData rowData = new RowData();
int k = 0;
if (string.IsNullOrEmpty(sheetClass.SplitStr) && sheetClass.ParentVar != null &&
!string.IsNullOrEmpty(sheetClass.ParentVar.Foregin))
{
rowData.parentValue = worksheet.Cells[j + 1, 1].Value.ToString().Trim();
k = 1;
}
for (; k < colCount; k++)
{
ExcelRange range = worksheet.Cells[j + 1, k + 1];//索引从1开始
string value = "";
if (range.Value != null)
{
value = range.Value.ToString().Trim();
}
string colValue = worksheet.Cells[1, k + 1].Value.ToString().Trim();
rowData.RowDataDic.Add(GetNameFormCol(sheetClass.VarList, colValue), value);
}
sheetData.AllData.Add(rowData);
}
allSheetDataDic.Add(worksheet.Name, sheetData);
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
//根据类结构创建类，并将execl数据将变量赋值，然后调用xml序列化
object objClass = CreatClass(className);
List <SheetClass> outKeyList = new List <SheetClass>();
foreach (string key in allSheetClassDic.Keys)
{
SheetClass sheet = allSheetClassDic[key];
if (sheet.Depth == 1)
{
outKeyList.Add(sheet);
}
}
for (int i = 0; i < outKeyList.Count; i++)
{
ReadDataToClass(objClass, allSheetClassDic[ParseDicKey(outKeyList[i])],
allSheetDataDic[outKeyList[i].SheetName], allSheetClassDic, allSheetDataDic, null);
}
BinarySerializeOpt.XmlSerialize(XMLPATH + xmlName, objClass);
Debug.Log(excelName + "表导入完成！");
AssetDatabase.Refresh();
}
public static ExcelRange GetCell(this ExcelWorksheet sheet, cursor cur)
{
ExcelRange cellAddress = sheet.Cells[cur.y + 1, cur.x + 1];
return(cellAddress);
}
/// <summary>
/// Exportar os dados do modelo para uma planilha excel
/// </summary>
/// <typeparam name="T">Tipo do objeto de modelo</typeparam>
/// <param name="nomeArquivo">Nome do arquivo, incluindo a extensão, que será gerado</param>
/// <param name="nomePlanilha">Nome da planilha (aba) a ser utilizada como fonte de dados</param>
/// <param name="registros">Lista de registros (modelo) a ser exportado</param>
/// <param name="estiloTabela">Estilo da tabela (Excel Table)</param>
/// <param name="nomeTabela">Nome da tabela (Excel Table)</param>
/// <returns>O caminho+nome do arquivo gerado</returns>
public string Exportar <T>(string nomeArquivo, string nomePlanilha, IList <T> registros, bool sobrescreverArquivo, TableStyles estiloTabela, string nomeTabela)
where T : class
{
// Abrindo arquivo, excluindo se existir
FileInfo arqInfo = new FileInfo(Path.Combine(_caminho, nomeArquivo));
if (arqInfo.Exists && sobrescreverArquivo)
{
arqInfo.Delete();
arqInfo = new FileInfo(Path.Combine(_caminho, nomeArquivo));
}
// Lendo attributos do modelo
LerAtributosModelo <T>();
using (ExcelPackage pkg = new ExcelPackage(arqInfo))
{
// Adicionando a planilha à pasta de trabalho
ExcelWorksheet ws = pkg.Workbook.Worksheets.Add(nomePlanilha);
if (ws != null && registros != null && registros.Count() > 0)
{
// Adicionando o cabeçalho
foreach (KeyValuePair <string, ExcelColumnAttribute> item in _attributos)
{
string columnTitle = (item.Value.Title ?? item.Key);
ws.Cells[1, item.Value.Order].Value = columnTitle;
ws.Cells[1, item.Value.Order].Style.Font.Bold = true;
ws.Cells[1, item.Value.Order].Style.VerticalAlignment = item.Value.TitleVerticalAlignment;
ws.Cells[1, item.Value.Order].Style.HorizontalAlignment = item.Value.TitleHorizontalAlignment;
}
// Populando a planilha
for (int pos = 0; pos < registros.Count; pos++)
{
T reg = registros[pos];
int linha = (pos + 2);
foreach (var item in _attributos)
{
Type t = reg.GetType();
PropertyInfo[] props = t.GetProperties();
PropertyInfo prop = props.Where(x => x.Name == item.Key).First();
Type propType = prop.GetType();
var value = prop.GetValue(reg);
if (item.Value.BooleanoSimNao)
{
if (value.GetType().ToString().ToLower().Equals("system.boolean"))
{
if (bool.Parse(value.ToString()))
{
value = "Sim";
}
else
{
value = "Não";
}
}
}
ws.Cells[linha, item.Value.Order].Value = value;
}
}
// Formatando a planilha
int ultimaLinha = ws.Dimension.End.Row;
int ultimaColuna = ws.Dimension.End.Column;
foreach (KeyValuePair <string, ExcelColumnAttribute> item in _attributos)
{
int coluna = item.Value.Order;
using (ExcelRange cells = ws.Cells[2, coluna, ultimaLinha, coluna])
{
cells.Style.VerticalAlignment = item.Value.ContentVerticalAlignment;
cells.Style.HorizontalAlignment = item.Value.ContentHorizontalAlignment;
cells.Style.Numberformat.Format = item.Value.NumberFormat;
cells.Style.WrapText = item.Value.WrapText;
}
}
// Atribuindo tabela
if (estiloTabela != TableStyles.None)
{
var handle = ws.Tables.Add(new ExcelAddressBase(1, 1, ultimaLinha, ultimaColuna), nomeTabela);
handle.TableStyle = estiloTabela;
}
}
pkg.Save(); // Salvando a planilha
}
return(arqInfo.FullName);
}
//#EXPORT EXCEL
public void ExportExcel()
{
if ((sesion = SessionDB.start(Request, Response, false, db)) == null)
{
return;
}
try
{
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add("IDSIU", typeof(string));
tbl.Columns.Add("Nombre", typeof(string));
tbl.Columns.Add("Apellidos", typeof(string));
tbl.Columns.Add("Origen de pago", typeof(string));
tbl.Columns.Add("Campus", typeof(string));
tbl.Columns.Add("Periodo", typeof(string));
tbl.Columns.Add("Contrato", typeof(string));
tbl.Columns.Add("Concepto", typeof(string));
tbl.Columns.Add("Monto", typeof(string));
tbl.Columns.Add("IVA", typeof(string));
tbl.Columns.Add("IVA Ret", typeof(string));
tbl.Columns.Add("ISR Ret", typeof(string));
tbl.Columns.Add("Fecha Pago", typeof(string));
tbl.Columns.Add("Cta Contable", typeof(string));
tbl.Columns.Add("Tipo de pago", typeof(string));
List <string> filtros = new List <string>();
if (Request.Params["sedes"] != "" && Request.Params["sedes"] != null)
{
filtros.Add("CVE_SEDE = '" + Request.Params["sedes"] + "'");
}
if (Request.Params["periodos"] != "" && Request.Params["periodos"] != null)
{
filtros.Add("PERIODO = '" + Request.Params["periodos"] + "'");
}
if (Request.Params["tipospago"] != "" && Request.Params["tipospago"] != null)
{
filtros.Add("CVE_TIPODEPAGO = '" + Request.Params["tipospago"] + "'");
}
if (Request.Params["escuelas"] != "" && Request.Params["escuelas"] != null)
{
filtros.Add("CVE_ESCUELA = '" + Request.Params["escuelas"] + "'");
}
string conditions = string.Join <string>(" AND ", filtros.ToArray());
string union = "";
if (conditions.Length != 0)
{
union = " WHERE ";
}
ResultSet res = db.getTable("SELECT * FROM VESTADO_CUENTA_DETALLE " + union + " " + conditions);
while (res.Next())
{
// Here we add five DataRows.
tbl.Rows.Add(res.Get("IDSIU"), res.Get("NOMBRES")
, res.Get("APELLIDOS"), res.Get("CVE_ORIGENPAGO")
, res.Get("CVE_SEDE"), res.Get("PERIODO")
, res.Get("ID_ESQUEMA"), res.Get("CONCEPTO"), res.Get("MONTO")
, res.Get("MONTO_IVA"), res.Get("MONTO_IVARET")
, res.Get("MONTO_ISRRET"), res.Get("FECHAPAGO")
, res.Get("CUENTACONTABLE")
, res.Get("TIPODEPAGO"));
}
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Detalle de Pagos");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
ws.Cells["A1:O1"].AutoFitColumns();
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:O1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=DetallePagos.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
Log.write(this, "Start", LOG.CONSULTA, "Exporta Excel Detalle Pagos", sesion);
}
catch (Exception e)
{
ViewBag.Notification = Notification.Error(e.Message);
Log.write(this, "Start", LOG.ERROR, "Exporta Excel Detalle Pagos" + e.Message, sesion);
}
}
public void ApllyStyle(ExcelRange cells)
{
cells.Style.Border.Right.Style = this.BorderStyle;
}
public MemoryStream DataTableToStream(DataTable dtSource, bool autoColumnHeder, string[] tableHeadNames, bool isFirstRowForTitle, string sheetTitle)
{
using (ExcelPackage pck = new ExcelPackage())
{
int startRow = this.StartRow;
int startCol = this.StartCol;
int dtColCount = dtSource.Columns.Count;
ExcelWorksheet worksheet = pck.Workbook.Worksheets.Add("sheet1");
worksheet.DefaultColWidth = DefaultColWidth;
worksheet.DefaultRowHeight = DefaultRowHeight;
if (string.IsNullOrEmpty(sheetTitle))
{
sheetTitle = "Table1";
}
worksheet.Name = sheetTitle;
if (isFirstRowForTitle)
{
using (ExcelRange curRange = worksheet.Cells[startRow, startCol, startRow, startCol + dtColCount - 1])
{
curRange.Value = sheetTitle;
curRange.Merge = true;
curRange.SetStyleAlignment(ExcelHorizontalAlignment.Center, ExcelVerticalAlignment.Center);
}
worksheet.Row(startRow).Height = 24;
startRow++;
}
bool printdtHeader = true;
if (!autoColumnHeder)
{
printdtHeader = false;
for (int i = 0; i < tableHeadNames.Length; i++)
{
worksheet.Cells[startRow, i + startCol].Value = tableHeadNames[i];
}
using (ExcelRange curRange = worksheet.Cells[startRow, startCol, startRow, startCol + dtColCount - 1])
{
curRange.SetStyleAlignment(ExcelHorizontalAlignment.Center);
curRange.Style.Font.Bold = true;
}
worksheet.Row(startRow).Height = DefaultRowHeight + 2;
startRow++;
}
worksheet.Cells[startRow, startCol].LoadFromDataTable(dtSource, printdtHeader, this.TableStyle);
foreach (DataColumn col in dtSource.Columns)
{
using (ExcelRange curRange = worksheet.Cells[startRow, col.Ordinal + startCol, startRow + col.Table.Rows.Count, col.Ordinal + startCol])
{
curRange.SetCellStyleByType(col.DataType);
}
}
using (MemoryStream stream = new MemoryStream())
{
pck.SaveAs(stream);
return(stream);
}
}
}
public static void List2Excel <T>(HttpResponseBase responsePage, IList <T> lista, String Titulo, String nombre, List <ReportColumnHeader> columnsNames, DataTable dt = null)
{
var filename = nombre;
DataTable DataTablelista;
if (dt != null)
{
DataTablelista = dt;
}
else
{
DataTablelista = CollectionHelper.ConvertTo(lista);
}
//ordena columnas dearcuerdo el backoffice
int contadorOrden = 0;
for (int i = 0; i < columnsNames.Count; i++)
{
string nombrecolumna = columnsNames[i].BindField;
DataColumn col = DataTablelista.Columns[nombrecolumna];
if (col != null)
{
DataTablelista.Columns[nombrecolumna].SetOrdinal(contadorOrden);
contadorOrden++;
}
}
//pone nombres a las columnas
List <DataColumn> lstIndexremoves = new List <DataColumn>();
for (int i = 0; i < DataTablelista.Columns.Count; i++)
{
DataColumn col = DataTablelista.Columns[i];
ReportColumnHeader mcolumn = columnsNames.Find(x => x.BindField == col.ColumnName);
if (mcolumn == null)
{
lstIndexremoves.Add(col);
}
else
{
if (mcolumn.FlgOculto == "1")
{
lstIndexremoves.Add(col);
}
else
{
DataTablelista.Columns[i].ColumnName = mcolumn.HeaderName;
}
}
}
//elimina columnas innecesarias.
for (int i = 0; i < lstIndexremoves.Count; i++)
{
DataTablelista.Columns.Remove(lstIndexremoves[i]);
}
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Hoja 1");
workSheet.Cells[1, 1].Value = "" + Titulo.Replace("_", " ").ToUpper().Trim() + "";
workSheet.Cells[1, 1].Style.Font.Bold = true;
workSheet.Cells[1, 1].Style.Font.Size = 24;
workSheet.Cells[1, 1].Style.Font.Name = "Calibri";
workSheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
workSheet.Cells["A1:J2"].Merge = true;
workSheet.Cells[3, 1].Value = "Fecha de generación:";
workSheet.Cells[3, 1].Style.Font.Bold = true;
workSheet.Cells[3, 1].Style.Font.Size = 11;
workSheet.Cells[3, 1].Style.Font.Name = "Calibri";
workSheet.Cells[3, 1].Style.WrapText = true;
workSheet.Cells[3, 2].Value = DateTime.Now.ToLongDateString();
workSheet.Cells[3, 2].Style.Font.Size = 11;
workSheet.Cells[3, 2].Style.Font.Name = "Calibri";
workSheet.Cells[3, 2].Style.WrapText = true;
workSheet.Cells[5, 1].LoadFromDataTable(DataTablelista, true);
workSheet.Cells.AutoFitColumns();
for (int i = 1; i <= DataTablelista.Columns.Count; i++)
{
workSheet.Column(i).AutoFit();
workSheet.Column(i).BestFit = true;
//workSheet.Cells[5, i].Style.Font.Bold = true;
workSheet.Cells[5, i].Style.Font.Color.SetColor(Color.White);
workSheet.Cells[5, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells[5, i].Style.Fill.BackgroundColor.SetColor(Color.Black);
//workSheet.Cells[5, i].Style.Border.BorderAround(ExcelBorderStyle.Thin, System.Drawing.Color.Black);
workSheet.Cells[5, i].Style.Font.Size = 11;
workSheet.Cells[5, i].Style.Font.Name = "Calibri";
//workSheet.Cells[5, i].Style.WrapText = true;
}
workSheet.Column(1).Width = 20;
int maxpos = 6;
ExcelRange rangoTabla = workSheet.Cells[maxpos, 1, maxpos + DataTablelista.Rows.Count - 1, DataTablelista.Columns.Count];
//rangoTabla.Style.Font.Bold = true;
//rangoTabla.Style.Fill.BackgroundColor = colorNegro;
rangoTabla.Style.Fill.PatternType = ExcelFillStyle.Solid;
rangoTabla.Style.Font.Color.SetColor(Color.Black);
rangoTabla.Style.Fill.BackgroundColor.SetColor(Color.White);
rangoTabla.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
rangoTabla.Style.Font.Size = 11;
rangoTabla.Style.Font.Name = "Calibri";
rangoTabla.Style.WrapText = true;
responsePage.Clear();
using (MemoryStream memoryStream = new MemoryStream())
{
responsePage.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
responsePage.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", filename.Replace(" ", "_")));
excel.SaveAs(memoryStream);
memoryStream.WriteTo(responsePage.OutputStream);
responsePage.Flush();
responsePage.End();
}
}
/// <summary>
/// Merge Cell
/// </summary>
/// <param name="excelWorksheet"></param>
/// <param name="excelRange">Address need to merge</param>
/// <returns></returns>
public ExcelWorksheet MergeCell(ExcelWorksheet excelWorksheet, ExcelRange excelRange)
{
excelWorksheet.Cells[excelRange.Address].Merge = true;
return(excelWorksheet);
}
public ActionResult ExportToExcel(int SC = 0)
{
try
{
var data = db.View_SC.Where(x => x.Gen_NO == SC && x.APP_Status == 1).Select(x => new ViewModelSC()
{
SC_NameTH = x.SC_NameTH,
Gen_Name = x.Gen_Name,
Gen_Date = x.Gen_Date,
REG_Name = x.REG_Name,
REG_Email = x.REG_Email,
REG_Tel = x.REG_Tel
});
var find = db.View_SC.FirstOrDefault(x => x.Gen_NO == SC);
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("รายงาน");
using (ExcelRange Rng = ws.Cells["a1:f5"])
{
ws.Cells["a1:f1"].Merge = true;
ws.Cells["a1"].Value = "รายชื่อผู้เข้าอบรม";
ws.Cells["a2"].Value = "หลักสูตร";
ws.Cells["b2:f2"].Merge = true;
ws.Cells["b2"].Value = find.SC_NameTH;
ws.Cells["a3"].Value = "รุ่น";
ws.Cells["b3"].Value = find.Gen_Name;
ws.Cells["c3"].Value = "วันที่";
ws.Cells["d3:f3"].Merge = true;
ws.Cells["d3"].Value = find.Gen_Date;
ws.Cells["a4:f4"].Merge = true;
ws.Cells["a5"].Value = "ลำดับที่";
ws.Cells["b5:c5"].Merge = true;
ws.Cells["b5"].Value = "ชื่อ-นามสกุล";
ws.Cells["d5"].Value = "เบอร์โทร";
ws.Cells["e5"].Value = "อีเมลล์";
ws.Cells["f5"].Value = "";
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
using (ExcelRange Rng = ws.Cells["a6:f100"])
{
var startindex = 6;
foreach (var item in data)
{
var name = item.REG_Name;
var tel = item.REG_Tel;
var email = item.REG_Email;
ws.Cells["a" + startindex].Value = startindex - 5;
ws.Cells["b" + startindex + ":c" + startindex].Merge = true;
ws.Cells["b" + startindex].Value = name;
ws.Cells["d" + startindex].Value = tel;
ws.Cells["e" + startindex].Value = email;
ws.Cells["f" + startindex].Value = "";
startindex += 1;
}
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
ws.Cells["a:fz"].AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + find.SC_NameTH + ".xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
Response.End();
return(RedirectToAction("Report_Index"));
}
catch (Exception e)
{
return(RedirectToAction("Report_Index"));
}
}
/// <summary>
/// Split Cell
/// </summary>
/// <param name="excelWorksheet"></param>
/// <param name="excelRange">Address need to merge</param>
/// <returns></returns>
public ExcelWorksheet SplitCell(ExcelWorksheet excelWorksheet, ExcelRange excelRange)
{
excelWorksheet.Cells[excelRange.FullAddress].Merge = false;
return(excelWorksheet);
}
public void ExcelGenearte()
{
var temp = new FileInfo(TemplateFileDir + Path.DirectorySeparatorChar + TemplateFileName);
var originManiName = ManifestFileDir + Path.DirectorySeparatorChar + ManifestFileName;
FileInfo mani;
if (ManifestFileName.EndsWith(".xls"))
{
var originMani = new FileInfo(originManiName);
mani = new FileInfo(originMani + "x");
var IsConverted = converter.XlsToXlsx(originMani);
if (!IsConverted)
{
Console.WriteLine("Convert fail.");
return;
}
}
else
{
mani = new FileInfo(originManiName);
}
using (ExcelPackage maniPackage = new ExcelPackage(mani))
{
var maniWs = maniPackage.Workbook.Worksheets.FirstOrDefault();
if (!ColumnNameExist(maniWs, InventoryName))
{
throw new InvalidOperationException($"列{InventoryName}未配置");
}
if (!ColumnNameExist(maniWs, TotalAmountWithTax))
{
throw new InvalidOperationException($"列{TotalAmountWithTax}未配置");
}
if (!ColumnNameExist(maniWs, InventoryAmount))
{
throw new InvalidOperationException($"列{InventoryAmount}未配置");
}
if (!ColumnNameExist(maniWs, UnitWithTax))
{
throw new InvalidOperationException($"列{UnitWithTax}未配置");
}
//To Do clean up the Data
using (ExcelPackage tempPackage = new ExcelPackage(temp))
{
//增值税清单模板
var tempWs = tempPackage.Workbook.Worksheets.FirstOrDefault();
int currentIndex = 2;
int count = 1;
int preIndex = 2;
double moneySum = 0;
string outputDir = @"D:\清单结果";
string fileName = ManifestFileName.Split('.')[0] + "子表";
var lastIndex = GetColumnLastRow(maniWs, InventoryName);
while (!(currentIndex > lastIndex))
{
if (moneySum < 113000)
{
double money;
var moneyExist =
double.TryParse(
maniWs.Cells[currentIndex, GetColumnByName(maniWs, TotalAmountWithTax)].Value?.ToString(),
out money);
if (!moneyExist)
{
money = 0;
}
else if (money < 0)
{
throw new InvalidOperationException($"列{TotalAmountWithTax}不能为负数");
}
moneySum += money;
var stockName = maniWs.Cells[currentIndex, GetColumnByName(maniWs, InventoryName)];
var nameLength = stockName.Value?.ToString().Length;
if (nameLength > 30)
{
stockName.Value = stockName.Value.ToString().Substring(0, 29);
}
if (moneySum >= 113000 || currentIndex == lastIndex)
{
if (moneySum >= 113000)
{
moneySum -= money;
currentIndex--;
}
//生成子表
var fi = new FileInfo(outputDir + Path.DirectorySeparatorChar + fileName +
count.ToString() + ".xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(fi))
{
//sheet already exist
var xlSheetsList = xlPackage.Workbook.Worksheets;
var destWs = xlSheetsList.All(x => x.Name != "增值税清单")
? xlSheetsList.Add("增值税清单", tempWs)
: xlSheetsList["增值税清单"];
var interval = currentIndex - preIndex + 2;
string destColumnName = "货物或应税劳务、服务名称";
string srcColumnName = InventoryName;
ExcelRange destRange = GetExcelRange(destWs, 2, interval, destColumnName, destColumnName);
ExcelRange srcRange = GetExcelRange(maniWs, preIndex, currentIndex, srcColumnName,
srcColumnName);
CopyFrom(srcRange, destRange);
if (ColumnNameExist(maniWs, InventoryCode))
{
destColumnName = "规格型号";
srcColumnName = InventoryCode;
destRange = GetExcelRange(destWs, 2, interval, destColumnName, destColumnName);
srcRange = GetExcelRange(maniWs, preIndex, currentIndex, srcColumnName,
srcColumnName);
CopyFrom(srcRange, destRange);
}
destColumnName = "数量";
srcColumnName = InventoryAmount;
destRange = GetExcelRange(destWs, 2, interval, destColumnName, destColumnName);
srcRange = GetExcelRange(maniWs, preIndex, currentIndex, srcColumnName,
srcColumnName);
CopyFrom(srcRange, destRange);
destColumnName = "金额";
srcColumnName = TotalAmountWithTax;
destRange = GetExcelRange(destWs, 2, interval, destColumnName, destColumnName);
srcRange = GetExcelRange(maniWs, preIndex, currentIndex, srcColumnName,
srcColumnName);
CopyFrom(srcRange, destRange);
destColumnName = "单价";
srcColumnName = UnitWithTax;
destRange = GetExcelRange(destWs, 2, interval, destColumnName, destColumnName);
srcRange = GetExcelRange(maniWs, preIndex, currentIndex, srcColumnName,
srcColumnName);
CopyFrom(srcRange, destRange);
//copy operation
CopyValue("价格方式", interval, destWs);
//CopyValue("税收分类编码版本号", interval, destWs);
CopyValue("使用优惠政策标识", interval, destWs);
CopyValue("中外合作油气田标识", interval, destWs);
FillValue("税率", interval, destWs, 0.13);
FillValue("税收分类编码", interval, destWs, "1060201070000000000");
FillValue("税收分类编码版本号", interval, destWs, "33.0");
FillValue("计量单位", interval, destWs, "张");
//set index column
var st_lastRow = GetColumnLastRow(destWs, "金额");
var st_columnIndex = GetColumnByName(destWs, "序号");
for (int i = 2; i <= st_lastRow; i++)
{
destWs.Cells[i, st_columnIndex].Value = i - 1;
}
preIndex = currentIndex + 1;
xlPackage.SaveAs(fi);
converter.XlsxToXls(fi);
}
moneySum = 0;
count++;
}
}
currentIndex++;
}
}
}
if (File.Exists(mani.ToString()))
{
File.Delete(mani.ToString());
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
///<param name="ignoreNames">忽略不导出的字段</param>
/// <param name="columnCNName">导出的所有列名， ValueTuple<string, int>为列中文名字，及表格宽度</param>
/// <param name="dicNos">为字典编号，字典name/value</param>
/// <param name="savePath">保存路径</param>
/// <param name="fileName">保存的文件名</param>
/// <param name="template">是否为下载模板</param>
/// <returns></returns>
public static string Export <T>(List <T> list, IEnumerable <string> ignoreNames, string savePath, string fileName, bool template = false)
{
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
//获取代码生成器对应的配置信息
List <CellOptions> cellOptions = GetExportColumnInfo(typeof(T).GetEntityTableName(), template);
string fullPath = savePath + fileName;
//获取所有有值的数据源
var dicNoKeys = cellOptions
.Where(x => !string.IsNullOrEmpty(x.DropNo) && x.KeyValues != null && x.KeyValues.Keys.Count > 0)
.Select(x => new { x.DropNo, x.ColumnName }).Distinct().ToList();
//List<string> ignoreColumn = typeof(T).GetProperties().Where(x => !cellOptions.Select(s => s.ColumnName).Contains(x.Name)).Select(s => s.Name).ToList();
List <PropertyInfo> propertyInfo = (
ignoreNames == null
? typeof(T).GetProperties()
.ToList()
: typeof(T).GetProperties()
.Where(x => !ignoreNames.Select(g => g.ToLower()).Contains(x.Name.ToLower())))
.Where(x => cellOptions.Select(s => s.ColumnName) //获取代码生成器配置的列
.Contains(x.Name)).ToList();
string[] dateArr = null;
if (!template)
{
dateArr = propertyInfo.Where(x => x.PropertyType == typeof(DateTime) ||
x.PropertyType == typeof(DateTime?))
.Select(s => s.Name).ToArray();
}
using (ExcelPackage package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("sheet1");
for (int i = 0; i < propertyInfo.Count; i++)
{
string columnName = propertyInfo[i].Name;
using (ExcelRange range = worksheet.Cells[1, i + 1])
{
worksheet.Cells[1, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
//默认灰色背景，白色字体
Color backgroundColor = Color.Gray;
//字体颜色
Color fontColor = Color.White;
//下载模板并且是必填项，将表格设置为黄色
if (template)
{
fontColor = Color.Black;
if (cellOptions.Exists(x => x.ColumnName == columnName && x.Requierd))
{
backgroundColor = Color.Yellow; //黄色必填
}
else
{
backgroundColor = Color.White;
}
}
worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(backgroundColor); //背景色
worksheet.Cells[1, i + 1].Style.Font.Color.SetColor(fontColor); //字体颜色
}
CellOptions options = cellOptions.Where(x => x.ColumnName == columnName).FirstOrDefault();
if (options != null)
{
worksheet.Column(i + 1).Width = options.ColumnWidth / 6.00;
worksheet.Cells[1, i + 1].Value = options.ColumnCNName;
}
else
{
worksheet.Column(i + 1).Width = 15;
worksheet.Cells[1, i + 1].Value = columnName;
}
}
//下载模板直接返回
if (template)
{
package.SaveAs(new FileInfo(fullPath));
return(fullPath);
}
for (int i = 0; i < list.Count; i++)
{
for (int j = 0; j < propertyInfo.Count; j++)
{
string cellValue = null;
if (dateArr != null && dateArr.Contains(propertyInfo[j].Name))
{
object value = propertyInfo[j].GetValue(list[i]);
cellValue = value == null ? "" : ((DateTime)value).ToString("yyyy-MM-dd HH:mm:sss").Replace(" 00:00:00", "");
}
else
{
cellValue = (propertyInfo[j].GetValue(list[i]) ?? "").ToString();
}
if (dicNoKeys.Exists(x => x.ColumnName == propertyInfo[j].Name))
{
cellOptions.Where(x => x.ColumnName == propertyInfo[j].Name)
.Select(s => s.KeyValues)
.FirstOrDefault()
.TryGetValue(cellValue, out string result);
cellValue = result ?? cellValue;
}
worksheet.Cells[i + 2, j + 1].Value = cellValue;
}
}
package.SaveAs(new FileInfo(fullPath));
}
return(fullPath);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dataTable">数据源</param>
/// <param name="heading">工作簿Worksheet</param>
/// <param name="showSrNo">//是否显示行编号</param>
/// <param name="columnsToTake">要导出的列</param>
/// <returns></returns>
public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(string.Format("{0}Data", heading));
int startRowFrom = string.IsNullOrEmpty(heading) ? 1 : 3; //开始的行
//是否显示行编号
if (showSrNo)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
dataColumn.SetOrdinal(0);
int index = 1;
foreach (DataRow item in dataTable.Rows)
{
item[0] = index;
index++;
}
}
//Add Content Into the Excel File
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
// autofit width of cells with small content
int columnIndex = 1;
foreach (DataColumn item in dataTable.Columns)
{
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
if (maxLength < 150)
{
workSheet.Column(columnIndex).AutoFit();
}
columnIndex++;
}
// format header - bold, yellow on black
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
// format cells - add borders
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
// removed ignored columns
for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
{
if (i == 0 && showSrNo)
{
continue;
}
if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName))
{
workSheet.DeleteColumn(i + 1);
}
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 5;
}
result = package.GetAsByteArray();
}
return(result);
}
//#EXPORT EXCEL
public void ExportExcel()
{
if (sesion == null)
{
sesion = SessionDB.start(Request, Response, false, db);
}
try
{
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add("Clave", typeof(string));
tbl.Columns.Add("Nombre", typeof(string));
ResultSet res = db.getTable("SELECT * FROM FORMATORETENCIONES");
while (res.Next())
{
// Here we add five DataRows.
tbl.Rows.Add(res.Get("CVE_RETENCION"), res.Get("RETENCION"));
}
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Catalogo Constancias Retenciones");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
//ws.Cells["A1:B1"].AutoFitColumns();
ws.Column(1).Width = 20;
ws.Column(2).Width = 80;
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:B1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ContratosWeb.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
Log.write(this, "Start", LOG.CONSULTA, "Exporta Excel Catalogo Constancias Retenciones", sesion);
}
catch (Exception e)
{
ViewBag.Notification = Notification.Error(e.Message);
Log.write(this, "Start", LOG.ERROR, "Exporta Excel Catalogo Constancias Retenciones" + e.Message, sesion);
}
}
public ExcelChartSerie Add(ExcelRange Serie, ExcelRange XSerie)
{
return AddSeries(Serie.Address, XSerie.Address);
}
private static void ApplyAlternativeBackgroundColors(IReadOnlyList <KeyValuePair <string, Color> > alternateBackgroundColors, ExcelRange allRange, int maxRow, int maxColumn)
{
if (alternateBackgroundColors is null || alternateBackgroundColors.Count == 0)
{
return;
}
for (var r = 2; r <= maxRow; r++)
{
var inneRange = allRange[r, 1, r, maxColumn];
var alternativeColor = alternateBackgroundColors[r % alternateBackgroundColors.Count];
if (inneRange.Style.Fill.PatternType != ExcelFillStyle.Solid)
{
inneRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
}
if (inneRange.Style.Fill.BackgroundColor.Rgb != alternativeColor.Key)
{
inneRange.Style.Fill.BackgroundColor.SetColor(alternativeColor.Value);
}
}
}
