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