private void SetColumnWidth(Worksheet worksheet, uint Index, DoubleValue dwidth) { DocumentFormat.OpenXml.Spreadsheet.Columns cs = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Columns>(); if (cs != null) { IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Column> ic = cs.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(r => r.Min == Index).Where(r => r.Max == Index); if (ic.Count() > 0) { DocumentFormat.OpenXml.Spreadsheet.Column c = ic.First(); c.Width = dwidth; } else { DocumentFormat.OpenXml.Spreadsheet.Column c = new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = Index, Max = Index, Width = dwidth, CustomWidth = true }; cs.Append(c); } } else { cs = new DocumentFormat.OpenXml.Spreadsheet.Columns(); DocumentFormat.OpenXml.Spreadsheet.Column c = new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = Index, Max = Index, Width = dwidth, CustomWidth = true }; cs.Append(c); worksheet.InsertAfter(cs, worksheet.GetFirstChild <SheetFormatProperties>()); } }
public void AttributeValueRangeConstraintTest() { Excel.Column column = new Excel.Column(); ValidationContext context = new ValidationContext() { Element = column }; AttributeValueRangeConstraint constraint = new AttributeValueRangeConstraint() { AttributeLocalName = "outlineLevel", AttributeNamespace = "", IsValidRange = true, MinValue = 0, MaxValue = 7 }; Assert.Null(constraint.Validate(context)); column.OutlineLevel = null; Assert.Null(constraint.Validate(context)); column.OutlineLevel = 8; Assert.NotNull(constraint.Validate(context)); column.OutlineLevel = 7; Assert.Null(constraint.Validate(context)); column.OutlineLevel = 0; Assert.Null(constraint.Validate(context)); }
static void SaveColumn(Excel.Columns exportedColumns, Column column) { var exportedColumn = new Excel.Column() { Min = ColumnIndexNum(column), Max = ColumnIndexNum(column) }; if (column._hidden) { exportedColumn.Hidden = true; } exportedColumn.Width = 5.0; exportedColumns.Append(exportedColumn); }
private void SetColumns(SpreadsheetDocument doc, WorksheetPart worksheetPart, int maxRow, int maxCol) { var worksheet = worksheetPart.Worksheet; var columns = new DocumentFormat.OpenXml.Spreadsheet.Columns(); var col = new DocumentFormat.OpenXml.Spreadsheet.Column(); col.Min = 1; col.Max = Convert.ToUInt32(maxCol); col.BestFit = true; columns.Append(col); worksheet.Append(columns); worksheet.Save(); }
public void AttributeMinMaxConstraintTest() { Excel.Column column = new Excel.Column(); ValidationContext context = new ValidationContext() { Element = column }; AttributeMinMaxConstraint constraint = new AttributeMinMaxConstraint("", "min", "", "max") ; column.Max = 2; column.Min = 1; Assert.Null(constraint.Validate(context)); //max > min, should pass validation column.Max = 2; column.Min = 2; Assert.Null(constraint.Validate(context)); //max == min, should pass validation column.Max = 2; column.Min = 3; Assert.NotNull(constraint.Validate(context)); //max < min, validation should be failed. }
//This function is created by Apt to append header cell with corresponding column private static void AppendHeaderCell(string cellReference, string cellStringValue, Row excelRow, uint columnIndex, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet) { DocumentFormat.OpenXml.Spreadsheet.Columns columns; DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null; // Add a new Excel Cell to our Row Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String }; CellValue cellValue = new CellValue(); cellValue.Text = cellStringValue; cell.Append(cellValue); excelRow.Append(cell); columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); // Check if the column collection exists if (columns == null) { columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0); } // Check if the column exists if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0) { // Find the previous existing column in the columns for (uint counter = columnIndex - 1; counter > 0; counter--) { previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault(); if (previousColumn != null) { break; } } columns.InsertAfter( new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = columnIndex, Max = columnIndex, CustomWidth = true, Width = 9 }, previousColumn); } }
public void AttributeMinMaxConstraintTest() { var column = new Excel.Column(); var context = new ValidationContext(); context.Stack.Push(element: column); var constraint = new AttributeMinMaxConstraint(string.Empty, "min", string.Empty, "max"); column.Max = 2; column.Min = 1; Assert.Null(constraint.ValidateCore(context)); //max > min, should pass validation column.Max = 2; column.Min = 2; Assert.Null(constraint.ValidateCore(context)); //max == min, should pass validation column.Max = 2; column.Min = 3; Assert.NotNull(constraint.ValidateCore(context)); //max < min, validation should be failed. }
public void AttributeMinMaxConstraintTest() { Excel.Column column = new Excel.Column(); ValidationContext context = new ValidationContext() { Element = column }; AttributeMinMaxConstraint constraint = new AttributeMinMaxConstraint("", "min", "", "max"); column.Max = 2; column.Min = 1; Assert.Null(constraint.Validate(context)); //max > min, should pass validation column.Max = 2; column.Min = 2; Assert.Null(constraint.Validate(context)); //max == min, should pass validation column.Max = 2; column.Min = 3; Assert.NotNull(constraint.Validate(context)); //max < min, validation should be failed. }
/// <summary> /// Set column width in characters /// </summary> /// <param name="width">column width in characters</param> /// <returns></returns> public Column SetWidth(double width) { var columns = Worksheet.GetFirstChild <x.Columns>(); if (columns == null) { columns = new x.Columns(); Worksheet.Insert(columns).AfterOneOf(typeof(x.Dimension), typeof(x.SheetViews), typeof(x.SheetFormatProperties)); } var columnProp = columns.Descendants <x.Column>().Where(c => c.Min.HasValue && c.Min.Value <= ColumnNum && c.Max.HasValue && c.Max.Value >= ColumnNum).FirstOrDefault(); if (columnProp == null) { columnProp = new x.Column() { Min = (uint)ColumnNum, Max = (uint)ColumnNum }; columns.Append(columnProp); } columnProp.CustomWidth = true; columnProp.Width = width; return(this); }
/// <summary> /// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="valueType">Type of the value</param> /// <param name="value">The actual value</param> /// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param> /// <param name="save">Save the worksheet?</param> /// <returns>True if succesful</returns> private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true) { DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>(); DocumentFormat.OpenXml.Spreadsheet.Row row; DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null; DocumentFormat.OpenXml.Spreadsheet.Cell cell; DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null; DocumentFormat.OpenXml.Spreadsheet.Columns columns; DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null; string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex; // Check if the row exists, create if necessary if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First(); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; //sheetData.Append(row); for (uint counter = rowIndex - 1; counter > 0; counter--) { previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault(); if (previousRow != null) { break; } } sheetData.InsertAfter(row, previousRow); } // Check if the cell exists, create if necessary if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0) { cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First(); } else { // Find the previous existing cell in the row for (uint counter = columnIndex - 1; counter > 0; counter--) { previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault(); if (previousCell != null) { break; } } cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress }; row.InsertAfter(cell, previousCell); } // Check if the column collection exists columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); if (columns == null) { columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0); } // Check if the column exists if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0) { // Find the previous existing column in the columns for (uint counter = columnIndex - 1; counter > 0; counter--) { previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault(); if (previousColumn != null) { break; } } columns.InsertAfter( new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = columnIndex, Max = columnIndex, CustomWidth = true, Width = 9 }, previousColumn); } // Add the value cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value); if (styleIndex != null) { cell.StyleIndex = styleIndex; } if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date) { cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType); } if (save) { worksheet.Save(); } return(true); }
/// <summary> /// Preenche a Ficha de Inspeção Especial em Excel e disponibiliza para download /// </summary> /// <param name="ord_id">Id da O.S pertinente ao objeto</param> /// <param name="origem">Tela que chamou o evento</param> /// <returns>string</returns> public string FichaInspecaoEspecialAnomalias_ExportarXLS(int ord_id, string origem) { string arquivo_modelo_caminhoFull = System.Web.HttpContext.Current.Server.MapPath("~/Reports/Ficha_Cadastramento_Anomalias.xlsx"); string arquivo_saida = "Ficha_Cadastramento_Anomalias_" + DateTime.Now.ToString().Replace(" ", "").Replace(":", "").Replace("/", "") + ".xlsx"; string arquivo_saida_caminhoFull = System.Web.HttpContext.Current.Server.MapPath("~/temp/") + "/" + arquivo_saida; string arquivo_saida_caminho_virtual = HttpContext.Current.Request.Url.Host + "/temp/" + arquivo_saida; string saida = ""; List <string> Headers = new List <string>(); try { Gerais ger = new Gerais(); limpaArquivosAntigos(); File.Copy(arquivo_modelo_caminhoFull, arquivo_saida_caminhoFull); // faz busca os DADOS no banco List <InspecaoAnomalia> lstDADOS = new InspecaoDAO().InspecaoAnomalias_Valores_ListAll(ord_id); // Abre a planilha para edicao using (SpreadsheetDocument doc = SpreadsheetDocument.Open(arquivo_saida_caminhoFull, true)) { // LEITURA DA PLANILHA Worksheet worksheet = ger.GetWorksheet(doc, "Ficha_Cadastramento_Anomalias"); Worksheet worksheetRodape = ger.GetWorksheet(doc, "Rodape"); // ======= PREENCHE OS DADOS =============================================== if (lstDADOS.Count > 0) { if (origem == "OrdemServico") // remove cabecalho e oculta coluna A { // remove cabecalhos Cell cell_A1 = ger.InsertCellInWorksheet("A", 1, worksheet); string letra = "A"; for (int t = 82; t < 88; t++) { if (t > 82) { letra = ((char)t).ToString(); } for (int m = 5; m <= 7; m++) { ger.copyCell(doc, worksheet, cell_A1, letra, Convert.ToUInt32(m)); } } DocumentFormat.OpenXml.Spreadsheet.Columns columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); DocumentFormat.OpenXml.Spreadsheet.Column col = columns.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Column>(); //col.Width = 0; col.Hidden = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true); } for (int li = 0; li < lstDADOS.Count; li++) { for (int col = 65; col < 87; col++) // VARRE as COLUNAS A até V { if ((origem == "OrdemServico") && (col >= 83)) { break; } Cell cell = ger.InsertCellInWorksheet(((char)col).ToString(), Convert.ToUInt32(li + 9), worksheet); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.StyleIndex = cell_estilo(lstDADOS[li].clo_id, false, ref worksheetRodape); switch (col) { case 65: if (origem == "Inspecao") { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].obj_codigo)); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 66: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].item)); break; case 67: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].obj_descricao)); break; } if (lstDADOS[li].ian_id > 0) { cell.StyleIndex = cell_estilo(lstDADOS[li].clo_id, true, ref worksheetRodape); switch (col) { case 68: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_localizacao_especifica)); break; case 69: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_numero)); break; case 70: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].leg_codigo)); break; case 71: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].atp_codigo)); break; case 72: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ale_codigo)); break; case 73: if (ger.IsNumeric(lstDADOS[li].ian_quantidade)) { cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_quantidade))); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 74: if (ger.IsNumeric(lstDADOS[li].ian_espacamento)) { cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_espacamento))); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 75: if (ger.IsNumeric(lstDADOS[li].ian_largura)) { cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_largura))); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 76: if (ger.IsNumeric(lstDADOS[li].ian_comprimento)) { cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_comprimento))); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 77: if (ger.IsNumeric(lstDADOS[li].ian_abertura_minima)) { cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_abertura_minima))); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 78: if (ger.IsNumeric(lstDADOS[li].ian_abertura_maxima)) { cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_abertura_maxima))); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 79: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].aca_codigo)); break; case 80: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_fotografia)); break; case 81: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_croqui)); break; case 82: cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].ian_observacoes)); break; case 83: if (origem == "Inspecao") { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(Convert.ToString(lstDADOS[li].rpt_id_sugerido_codigo)); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 84: if (origem == "Inspecao") { if (lstDADOS[li].ian_quantidade_sugerida > 0) { //cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; //cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_quantidade_sugerida))); cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[li].ian_quantidade_sugerida.ToString() + " " + lstDADOS[li].rpt_id_sugerido_unidade); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 85: if (origem == "Inspecao") { if ((ger.IsNumeric(lstDADOS[li].rpt_id_adotado)) && (Convert.ToDecimal(lstDADOS[li].rpt_id_adotado) > 0)) { cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].rpt_id_adotado))); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 86: if (origem == "Inspecao") { if (lstDADOS[li].ian_quantidade_adotada > 0) { // cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; // cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].ian_quantidade_adotada))); cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[li].ian_quantidade_adotada.ToString() + " " + lstDADOS[li].rpt_id_adotado_unidade); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; case 87: if (origem == "Inspecao") { if (lstDADOS[li].apt_id > 0) { cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number; cell.CellValue = new CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(Convert.ToDecimal(lstDADOS[li].apt_id))); } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } } else { cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(""); } break; } } } // for col } // for li // ============ coloca o rodape ================================================ uint LinhaDestino = (uint)lstDADOS.Count + 8; for (int li = 1; li <= 3; li++) { for (int col = 65; col < 87; col++) // VARRE as COLUNAS A até V { // copia o Quadro A da planilha "Rodape" para o rodape dos dados Cell cellOrigem = ger.InsertCellInWorksheet(((char)col).ToString(), Convert.ToUInt32(li), worksheetRodape); ger.copyCell(doc, worksheet, cellOrigem, ((char)col).ToString(), Convert.ToUInt32(li + LinhaDestino)); } // mescla as celulas if (li == 1) { ger.MergeCells(worksheet, "B" + (li + LinhaDestino).ToString(), "V" + (li + LinhaDestino).ToString()); } else { ger.MergeCells(worksheet, "A" + (li + LinhaDestino).ToString(), "E" + (li + LinhaDestino).ToString()); ger.MergeCells(worksheet, "F" + (li + LinhaDestino).ToString(), "V" + (li + LinhaDestino).ToString()); } } // preenche os valores Cell cell2 = ger.InsertCellInWorksheet("C", 2, worksheet); cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].obj_codigo_TipoOAE); cell2 = ger.InsertCellInWorksheet("R", 2, worksheet); cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].ins_anom_data); cell2 = ger.InsertCellInWorksheet("C", 3, worksheet); cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].ins_anom_Responsavel); cell2 = ger.InsertCellInWorksheet("G", (LinhaDestino + 2), worksheet); cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].ins_anom_quadroA_1); // cell2.StyleIndex = cell_Modelo4.StyleIndex; cell2 = ger.InsertCellInWorksheet("G", (LinhaDestino + 3), worksheet); cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(lstDADOS[0].ins_anom_quadroA_2); // cell2.StyleIndex = cell_Modelo4.StyleIndex; // ============ coloca o rodape Observacoes ================================================ int li2 = 4; for (int col = 65; col < 87; col++) // VARRE as COLUNAS A até V { // copia Obs da planilha "Rodape" para o rodape dos dados Cell cellOrigem = ger.InsertCellInWorksheet(((char)col).ToString(), Convert.ToUInt32(li2), worksheetRodape); ger.copyCell(doc, worksheet, cellOrigem, ((char)col).ToString(), Convert.ToUInt32(li2 + LinhaDestino)); } // mescla as celulas ger.MergeCells(worksheet, "A" + (li2 + LinhaDestino).ToString(), "V" + (li2 + LinhaDestino + 6).ToString()); // ajusta a altura da linha Row linhaObs = ger.GetRow(worksheet, 4 + LinhaDestino); } // fecha o arquivo e retorna doc.Save(); doc.Close(); } // using return(arquivo_saida); } // try catch (Exception ex) { saida = ex.ToString(); } return(""); }
static void SetColumnWidth(ExcelDocument doc, List<int> widths) { Columns columns = new Columns(); uint i = 1; foreach (int width in widths) { DocumentFormat.OpenXml.Spreadsheet.Column column = new DocumentFormat.OpenXml.Spreadsheet.Column(); if (width != -1) { // min max 表示列范围编号 column.Min = UInt32Value.FromUInt32(i); column.Max = UInt32Value.FromUInt32(i); column.Width = width; column.CustomWidth = true; columns.Append(column); } i++; } doc.WorkSheet.InsertAt(columns, 0); }
// 输出到文本文件 int OutputToTextFile( List<ListViewItem> items, StreamWriter sw, ref XLWorkbook doc, out string strError) { strError = ""; int nRet = 0; Hashtable macro_table = new Hashtable(); string strNamePath = "accountbook_printoption_text"; // 获得打印参数 PrintOption option = new AccountBookPrintOption(this.MainForm.DataDir, this.comboBox_load_type.Text); option.LoadData(this.MainForm.AppInfo, strNamePath); // 检查当前排序状态和包含种价格列之间是否存在矛盾 if (bHasBiblioPriceColumn(option) == true) { if (this.SortColumns_in.Count != 0 && this.SortColumns_in[0].No == COLUMN_BIBLIORECPATH) { } else { MessageBox.Show(this, "由于当前打印用到了 “种价格”列,为保证打印结果的准确,程序自动按 ‘种记录路径’ 列对全部列表事项进行一次自动排序。\r\n\r\n为避免这里的自动排序,可在打印前用鼠标左键点栏标题进行符合自己意愿的排序,只要最后一次点的是‘种记录路径’栏标题即可。"); ForceSortColumnsIn(COLUMN_BIBLIORECPATH); } } // 2009/7/24 changed if (this.SourceStyle == "batchno") { macro_table["%batchno%"] = HttpUtility.HtmlEncode(this.BatchNo); // 批次号 macro_table["%location%"] = HttpUtility.HtmlEncode(this.LocationString); // 馆藏地点 用HtmlEncode()的原因是要防止里面出现的“<不指定>”字样 } else { macro_table["%batchno%"] = ""; macro_table["%location%"] = ""; } // macro_table["%pagecount%"] = nPageCount.ToString(); // macro_table["%linesperpage%"] = option.LinesPerPage.ToString(); macro_table["%date%"] = DateTime.Now.ToLongDateString(); // 2009/7/24 changed if (this.SourceStyle == "barcodefile") { macro_table["%barcodefilepath%"] = this.BarcodeFilePath; macro_table["%barcodefilename%"] = Path.GetFileName(this.BarcodeFilePath); } else { macro_table["%barcodefilepath%"] = ""; macro_table["%barcodefilename%"] = ""; } // 2009/7/30 if (this.SourceStyle == "recpathfile") { macro_table["%recpathfilepath%"] = this.RecPathFilePath; macro_table["%recpathfilename%"] = Path.GetFileName(this.RecPathFilePath); } else { Debug.Assert(this.SourceStyle == "batchno" || this.SourceStyle == "barcodefile", ""); macro_table["%recpathfilepath%"] = ""; macro_table["%recpathfilename%"] = ""; } macro_table["%sourcedescription%"] = this.SourceDescription; IXLWorksheet sheet = null; // 输出统计信息页 if (this.TextOutputStatisPart == true) { if (doc != null) { sheet = doc.Worksheets.Add("统计页"); sheet.Style.Font.FontName = this.Font.Name; } int nItemCount = items.Count; int nBiblioCount = GetBiblioCount(items); string strTotalPrice = GetTotalPrice(items); macro_table["%itemcount%"] = nItemCount.ToString(); macro_table["%bibliocount%"] = nBiblioCount.ToString(); macro_table["%totalprice%"] = strTotalPrice; macro_table["%pageno%"] = "1"; // 2008/11/23 macro_table["%datadir%"] = this.MainForm.DataDir; // 便于引用datadir下templates目录内的某些文件 ////macro_table["%libraryserverdir%"] = this.MainForm.LibraryServerDir; // 便于引用服务器端的CSS文件 string strTemplateFilePath = option.GetTemplatePageFilePath("统计页"); if (String.IsNullOrEmpty(strTemplateFilePath) == false) { /* * TODO:修改为纯文本方式 <html> <head> <LINK href='%libraryserverdir%/accountbook.css' type='text/css' rel='stylesheet'> </head> <body> <div class='pageheader'>%date% 财产帐簿 -- %sourcedescription% -- (共 %pagecount% 页)</div> <div class='tabletitle'>%date% 财产帐簿 -- %sourcedescription%</div> <div class='itemcount'>册数: %itemcount%</div> <div class='bibliocount'>种数: %bibliocount%</div> <div class='totalprice'>总价: %totalprice%</div> <div class='sepline'><hr/></div> <div class='batchno'>批次号: %batchno%</div> <div class='location'>馆藏地点: %location%</div> <div class='location'>条码号文件: %barcodefilepath%</div> <div class='location'>记录路径文件: %recpathfilepath%</div> <div class='pagefooter'>%pageno%/%pagecount%</div> </body> </html> * * */ // 根据模板打印 string strContent = ""; // 能自动识别文件内容的编码方式的读入文本文件内容模块 // return: // -1 出错 // 0 文件不存在 // 1 文件存在 nRet = Global.ReadTextFileContent(strTemplateFilePath, out strContent, out strError); if (nRet == -1) return -1; string strResult = StringUtil.MacroString(macro_table, strContent); if (sw != null) sw.WriteLine(strResult); // TODO: string --> excel page } else { // 缺省的固定内容打印 // 内容行 if (sw != null) { sw.WriteLine("册数\t" + nItemCount.ToString()); sw.WriteLine("种数\t" + nBiblioCount.ToString()); sw.WriteLine("总价\t" + strTotalPrice); sw.WriteLine("----------"); if (this.SourceStyle == "batchno") { // 2008/11/22 if (String.IsNullOrEmpty(this.BatchNo) == false) { sw.WriteLine("批次号\t" + this.BatchNo); } if (String.IsNullOrEmpty(this.LocationString) == false && this.LocationString != "<不指定>") { sw.WriteLine("馆藏地点\t" + this.LocationString); } } if (this.SourceStyle == "barcodefile") { if (String.IsNullOrEmpty(this.BarcodeFilePath) == false) { sw.WriteLine("条码号文件\t" + this.BarcodeFilePath); } } // 2009/7/30 if (this.SourceStyle == "recpathfile") { if (String.IsNullOrEmpty(this.RecPathFilePath) == false) { sw.WriteLine("记录路径文件\t" + this.RecPathFilePath); } } sw.WriteLine("----------"); sw.WriteLine(""); } if (doc != null) { #if NO int nLineIndex = 2; doc.WriteExcelLine( nLineIndex++, "册数", nItemCount.ToString()); doc.WriteExcelLine( nLineIndex++, "种数", nBiblioCount.ToString()); doc.WriteExcelLine( nLineIndex++, "总价", strTotalPrice); #endif int nLineIndex = 2; WriteValuePair(sheet, nLineIndex++, "册数", nItemCount.ToString()); WriteValuePair(sheet, nLineIndex++, "种数", nBiblioCount.ToString()); WriteValuePair(sheet, nLineIndex++, "总价", strTotalPrice); } } } string strMarcFilterFilePath = option.GetTemplatePageFilePath("MARC过滤器"); if (String.IsNullOrEmpty(strMarcFilterFilePath) == false) { nRet = PrepareMarcFilter(strMarcFilterFilePath, out strError); if (nRet == -1) return -1; } if (doc != null) { sheet = doc.Worksheets.Add("财产帐"); sheet.Style.Font.FontName = this.Font.Name; #if NO Columns columns = new Columns(); DocumentFormat.OpenXml.Spreadsheet.Column column = new DocumentFormat.OpenXml.Spreadsheet.Column(); column.Min = 4; column.Max = 4; column.Width = 40; column.CustomWidth = true; columns.Append(column); doc.WorkSheet.InsertAt(columns, 0); #endif #if NO List<int> widths = new List<int>(new int [] {4,4,4,40}); SetColumnWidth(doc, widths); #endif } // 构造表格标题和标题行 BuildTextPageTop(option, macro_table, sw, sheet); stop.SetProgressValue(0); stop.SetProgressRange(0, items.Count); // 表格行循环 for (int i = 0; i < items.Count; i++) { Application.DoEvents(); // 出让界面控制权 if (stop != null && stop.State != 0) { strError = "用户中断"; return -1; } BuildTextTableLine(option, items, i, sw, // ref doc, sheet, this.TextTruncate); stop.SetProgressValue(i + 1); } return 0; }