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