/// <summary>
/// 合并单元格。坐标：(x1,x2,y1,y2)
/// </summary>
/// <param name="startRowIndex">起始行索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="startColumnIndex">开始列索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <returns></returns>
public IExcel MergeCell(int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex)
{
var region = new NPOI.SS.Util.CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex);
_sheet.AddMergedRegion(region);
return(this);
}
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet)
{
var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2);
sheet.AddMergedRegion(merge);
var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var caminho = $"{diretorioAtual}/Recursos/logoVLI.png";
byte[] data = ArquivosUtil.RetornarArquivo(caminho);
int pictureIndex = workbook.AddPicture(data, PictureType.JPEG);
ICreationHelper helper = workbook.GetCreationHelper();
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = helper.CreateClientAnchor();
anchor.Col1 = 1;
anchor.Row1 = 1;
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/
return(sheet);
}
/// <summary>
/// 删除行，解决因为shiftRows上移删除行而造成的格式错乱问题
/// </summary>
/// <param name="worksheet">目标sheet</param>
/// <param name="startRow">需要删除开始行(从0开始)</param>
/// <param name="count">删除行数</param>
public static void DelRows(ISheet worksheet, int startRow, int count)
{
int NumMergedRegions = worksheet.NumMergedRegions;
//int[,] allMergedRegion = getAllMergedRegions(worksheet);
for (int i = NumMergedRegions - 1; i >= 0; i--)
{
NPOI.SS.Util.CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow >= startRow + count ||
cellRangeAddress.LastRow <= startRow)
{
//只有一行的合并单元格FirstRow==LastRow
if (cellRangeAddress.FirstRow == cellRangeAddress.LastRow)
{
//刚好在删除区域的startRow或endRow
if (cellRangeAddress.FirstRow == startRow ||
cellRangeAddress.FirstRow == startRow + count - 1)
{
worksheet.RemoveMergedRegion(i);
}
}
}
else
{
//该合并单元格的行已经在被删除的区域内，所以解除该合并单元格
//如果不删除该合并单元格将会导致全部格式错乱
worksheet.RemoveMergedRegion(i);
}
}
worksheet.ShiftRows(startRow + count, worksheet.LastRowNum, -count, true, false);
}
public static void TrainingReport(IList <SelectableDriver> drivers, string absoluteFileName)
{
IWorkbook workbook = GetWorkbook(absoluteFileName);
ISheet mainWorksheet = CreateSheet(workbook, "Training Report");
// Get All The CellStyles
ICellStyle titleCellStyle = GetTitleStyle(workbook);
ICellStyle tableHeaderCellStyle = GetTableHeaderStyle(workbook);
ICellStyle infoCellStyle = GetInfoStyle(workbook);
ICellStyle centeredInfoCellStyle = GetCenteredInfoStyle(workbook);
ICellStyle boldInfoCellStyle = GetBoldInfoFont(workbook);
// Add Titles and Date in Report.
IRow titleRow = mainWorksheet.CreateRow(0);
var reportTitleCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3);
mainWorksheet.AddMergedRegion(reportTitleCellRangeAddress);
AddCell(titleRow, 0, "Training Report", titleCellStyle);
AddCell(titleRow, 5, DateTime.Now.ToString(DateFormat), boldInfoCellStyle);
// Add Headers.
IRow headerRow = mainWorksheet.CreateRow(2);
AddCell(headerRow, 0, "Last Name", tableHeaderCellStyle);
AddCell(headerRow, 1, "First Name", tableHeaderCellStyle);
AddCell(headerRow, 2, "Group", tableHeaderCellStyle);
AddCell(headerRow, 3, "Lesson(s) Completed", tableHeaderCellStyle);
AddCell(headerRow, 4, "Total Lessons", tableHeaderCellStyle);
AddCell(headerRow, 5, "Course Completed?", tableHeaderCellStyle);
// Add data now.
int rowIndex = 3;
// Adding data now.
foreach (Driver driver in drivers)
{
int completedLessonCount = driver.GroupedAttemptsByLessons.Count(x => x.IsComplete);
int totalLessonCount = driver.GroupedAttemptsByLessons.Count();
string couserCompleted = completedLessonCount == totalLessonCount ? "Yes" : "No";
IRow row = mainWorksheet.CreateRow(rowIndex);
AddCell(row, 0, driver.LastName, infoCellStyle);
AddCell(row, 1, driver.FirstName, infoCellStyle);
AddCell(row, 2, driver.Group != null ? driver.Group.Name : string.Empty, infoCellStyle);
AddCell(row, 3, completedLessonCount.ToString(), centeredInfoCellStyle);
AddCell(row, 4, totalLessonCount.ToString(), centeredInfoCellStyle);
AddCell(row, 5, couserCompleted, centeredInfoCellStyle);
rowIndex++;
}
AutoSizeColumns(mainWorksheet, 6);
// Creating file now
using (FileStream fs = new FileStream(absoluteFileName, FileMode.Create))
{
workbook.Write(fs);
}
workbook.Close();
}
/// <summary>
/// 跨sheet复制多行，连合并单元格
/// </summary>
/// <param name="worksheet">源sheet</param>
/// <param name="targetsheet">目标</param>
/// <param name="sourceRowNum">源行值</param>
/// <param name="copyRowsCount">复制行数</param>
/// <param name="destinationRowNum">目标行值</param>
/// <param name="cover">是否以覆盖原来内容，true为覆盖，false为插入</param>
public static void CopyRowsOverSheet(ISheet worksheet, ISheet targetsheet,
int sourceRowNum, int copyRowsCount, int destinationRowNum, bool cover)
{
int[,] sm = getAllMergedRegions(worksheet);
List <int[]> copyAreaMergedRegions = new List <int[]>();
//遍历一次源sheet的全部合并单元格
for (int i = 0; i < sm.GetLength(0); i++)
{
if (sm[i, 0] >= sourceRowNum && sm[i, 0] < (sourceRowNum + copyRowsCount))//18年8月30日发现bug：小于等于改为小于
{
copyAreaMergedRegions.Add(new int[4] {
sm[i, 0] - sourceRowNum, sm[i, 1], sm[i, 2] - sourceRowNum, sm[i, 3]
});
}
}
//int addpoint = destinationRowNum;
for (int row = 0; row < copyRowsCount; row++)
{
//跨sheet复制行
NpoiHelperV1.CopyRowOverSheet(worksheet, targetsheet, sourceRowNum + row, destinationRowNum + row, false, cover);
}
//将新复制的行进行合并
foreach (int[] cell in copyAreaMergedRegions)
{
NPOI.SS.Util.CellRangeAddress newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(
cell[0] + destinationRowNum, //顶
cell[2] + destinationRowNum, //底
cell[1], //左
cell[3] //右
);
targetsheet.AddMergedRegion(newCellRangeAddress);
}
}
/// <summary>
/// 复制多行并合拼其中的单元格
/// </summary>
/// <param name="workbook"></param>
/// <param name="worksheet">目标sheet</param>
/// <param name="sourceRowNum">起始行号</param>
/// <param name="copyRowsCount">复制行数</param>
/// <param name="destinationRowNum">目标行号</param>
public static void CopyRows(IWorkbook workbook, ISheet worksheet, int sourceRowNum, int copyRowsCount, int destinationRowNum)
{
int[,] sm = getAllMergedRegions(worksheet);
List <int[]> copyAreaMergedRegions = new List <int[]>();
for (int i = 0; i < sm.GetLength(0); i++)
{
if (sm[i, 0] >= sourceRowNum && sm[i, 0] < (sourceRowNum + copyRowsCount))
{
copyAreaMergedRegions.Add(new int[4] {
sm[i, 0] - sourceRowNum, sm[i, 1], sm[i, 2] - sourceRowNum, sm[i, 3]
});
}
}
int addpoint = destinationRowNum;
for (int row = 0; row < copyRowsCount; row++)
{
//复制行
NpoiHelperV1.CopyRowWithoutMergedRegion(workbook, worksheet, sourceRowNum + row, addpoint);
addpoint++;
}
foreach (int[] cell in copyAreaMergedRegions)
{
NPOI.SS.Util.CellRangeAddress newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(
cell[0] + destinationRowNum, //顶
cell[2] + destinationRowNum, //底
cell[1], //左
cell[3] //右
);
worksheet.AddMergedRegion(newCellRangeAddress);
}
}
/// <summary>
/// 判断当前列是否为简单列
/// </summary>
/// <param name="dt"></param>
/// <param name="row">最大行索引</param>
/// <param name="column">列序号</param>
/// <returns></returns>
private static bool DataColumnIsSimple(ISheet sheet, DataTable dt, int row, int column, int groups)
{
if (dt.Rows.Count < row || dt.Columns.Count < column || string.IsNullOrEmpty(dt.Rows[row][column].ToString()) || row == 0)
{
return(false);
}
//for (int i = column; i >= 0; i--)
//{
// if (dt.Rows[row][column].ToString() != dt.Rows[row - 1][column].ToString()) return false;
//}
//bool flag = false;
if (dt.Rows[row][column].ToString() == dt.Rows[row - 1][column].ToString())
{
int numMergedRegions = sheet.NumMergedRegions;
if (numMergedRegions == 0 || column == 0)
{
return(true);
}
//如果前一列的同一行和上一行处于相同的合并区域内
for (int m = 0; m < numMergedRegions; m++)
{
NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
int preCol = column - 1 > 0 ? column - 1 : 0;
if (a.FirstColumn <= preCol && a.LastColumn >= preCol && a.FirstRow <= groups + row - 1 && a.LastRow >= row + groups)
{
return(true);
}
}
}
return(false);
}
public void CriarCelulaMerge(int primeiraLinha, int ultimaLinha, int primeiraColuna, int ultimaColuna,
string nomeAba)
{
ISheet Sheet = SelecionarAba(nomeAba);
var cra = new NPOI.SS.Util.CellRangeAddress(primeiraLinha, ultimaLinha, primeiraColuna, ultimaColuna);
Sheet.AddMergedRegion(cra);
}
private static ISheet IncluirSubTituloPlanilha(HSSFWorkbook workbook, ISheet sheet, string tituloPlanilha, IRow linha)
{
var merge = new NPOI.SS.Util.CellRangeAddress(3, 3, 3, 5);
sheet.AddMergedRegion(merge);
IncluirNaPlanilha(workbook, tituloPlanilha, linha, 16, "Calibri");
return(sheet);
}
protected override void SetValue(ICell cell, object source)
{
NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell);
ISheet sheet = cell.Sheet;
IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
IClientAnchor anchor = region != null?
draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) :
draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
draw.CreatePicture(anchor, sheet.Workbook.AddPicture(_func(source), PictureType.JPEG));//PNG、JPEG都没问题
}
private static void WriteData(ref NPOI.SS.UserModel.ISheet sheet1, DataTable dt, Dictionary <string, string> columnInfo, string datadesc, int datastartrow)
{
//int datastartrow = 0;
if (!string.IsNullOrEmpty(datadesc))
{
NPOI.SS.UserModel.IRow desc = sheet1.CreateRow(datastartrow);
for (int i = 0; i < 10; i++)
{
desc.CreateCell(i);
}
NPOI.SS.Util.CellRangeAddress address = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9);
sheet1.AddMergedRegion(address);
sheet1.GetRow(0).GetCell(0).SetCellValue(datadesc);
datastartrow++;
}
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(datastartrow);
int tick = 0;
foreach (string value in columnInfo.Values)
{
row1.CreateCell(tick).SetCellValue(value);
tick++;
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + datastartrow + 1);
int index = 0;
foreach (string key in columnInfo.Keys)
{
bool isnumberic = false;
if (key.ToLower() == "rowid")
{
rowtemp.CreateCell(index).SetCellValue(i + 1);
}
else
{
string data = GetValue(dt.Rows[i], key, out isnumberic);
if (isnumberic && data != "--")
{
rowtemp.CreateCell(index).SetCellValue(string.IsNullOrEmpty(data) ? 0 : double.Parse(data));
}
else
{
rowtemp.CreateCell(index).SetCellValue(data);
}
}
index++;
}
}
}
private static ISheet IncluirTextoVLI(HSSFWorkbook workbook, ISheet sheet, IRow linha)
{
var merge = new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 5);
sheet.AddMergedRegion(merge);
var textoVLI = "VLI - Valor da Logística Integrada";
IncluirNaPlanilha(workbook, textoVLI, linha, 15, "Calibri");
return(sheet);
}
/// <summary>
/// 获取一个sheet的所有合并单元格
/// </summary>
/// <param name="worksheet">目标sheet</param>
/// <returns>返回数组结构：起始行，起始列，终止行，终止列；所以第一二个值可用于对该合并格赋值或读取
/// 但该数组的排序根据操作的顺序而定，最新加入的合并单元格为第0个
/// </returns>
public static int[,] getAllMergedRegions(ISheet worksheet)
{
int NumMergedRegions = worksheet.NumMergedRegions;
int[,] output = new int[NumMergedRegions, 4];
for (int i = 0; i < worksheet.NumMergedRegions; i++) //NumMergedRegions：整个sheet的合拼单元格数量
{
NPOI.SS.Util.CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i); //获取合拼单元格的地址字符串
output[i, 0] = cellRangeAddress.FirstRow;
output[i, 1] = cellRangeAddress.FirstColumn;
output[i, 2] = cellRangeAddress.LastRow;
output[i, 3] = cellRangeAddress.LastColumn;
}
return(output);
}
private void createTableHead(ISheet excelSheet, int baseRow, int baseColumn)
{
IRow row = excelSheet.CreateRow(baseRow);
row.CreateCell(baseColumn).SetCellValue("CIF");
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(baseRow, baseRow + 1, baseColumn, baseColumn));
row.CreateCell(baseColumn + 1).SetCellValue("Tên KH");
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(baseRow, baseRow + 1, baseColumn + 1, baseColumn + 1));
row.CreateCell(baseColumn + 2).SetCellValue("BDS");
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(baseRow, baseRow + 1, baseColumn + 2, baseColumn + 2));
row.CreateCell(baseColumn + 3).SetCellValue("fx");
var cra = new NPOI.SS.Util.CellRangeAddress(baseRow, baseRow, baseColumn + 3, baseColumn + 19);
excelSheet.AddMergedRegion(cra);
}
private void SetTitleRow(IRow targetRow, string title, int columnsToMerge)
{
List <ICell> l = new List <ICell>();
for (int i = 0; i < columnsToMerge; i++)
{
l.Add(targetRow.CreateCell(i));
}
var addr = new NPOI.SS.Util.CellRangeAddress(targetRow.RowNum, targetRow.RowNum, 0, columnsToMerge - 1);
targetRow.Sheet.AddMergedRegion(addr);
var first = l.First();
first.SetCellType(CellType.String);
first.SetCellValue(title);
}
public static void ExportReportHeader(string title, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber)
{
var row = sheet.CreateRow(rowNumber++);
row.HeightInPoints = 27;
var titleCell = row.CreateCell(0);
titleCell.SetCellValue(title);
titleCell.CellStyle = allStyles.HeaderStyle;
titleCell.CellStyle.WrapText = true;
var titleMerge = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7);
sheet.AddMergedRegion(titleMerge);
row = sheet.CreateRow(rowNumber++);
}
static void CreateExportData(List <VendeurData> vendeursData, string fileFullName)
{
// https://stackoverflow.com/questions/19838743/trying-to-create-a-new-xlsx-file-using-npoi-and-write-to-it
using (FileStream stream = new FileStream(fileFullName, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet(SHEET_NAME);
ICreationHelper cH = wb.GetCreationHelper();
int rowIndex = FIRST_ROW;
foreach (var vendeur in vendeursData)
{
var vendeurCourant = vendeur.VendeurName;
IRow rowVendeur = sheet.CreateRow(rowIndex);
ICell cellVendeur = rowVendeur.CreateCell(0);
cellVendeur.SetCellValue(cH.CreateRichTextString(vendeurCourant));
//merged region https://scottstoecker.wordpress.com/2011/05/18/merging-excel-cells-with-npoi/
NPOI.SS.Util.CellRangeAddress craVendeur = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 4);
sheet.AddMergedRegion(craVendeur);
rowIndex++;
foreach (var clientDataAllYears in vendeur.Clients)
{
IRow rowClient = sheet.CreateRow(rowIndex);
ICell cellClientName = rowClient.CreateCell(1);
cellClientName.SetCellValue(cH.CreateRichTextString(clientDataAllYears.Name));
NPOI.SS.Util.CellRangeAddress craClient = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 1, 3);
sheet.AddMergedRegion(craClient);
InsertCellForYear(cH, clientDataAllYears, rowClient, "2016", 4);
InsertCellForYear(cH, clientDataAllYears, rowClient, "2017", 5);
InsertCellForYear(cH, clientDataAllYears, rowClient, "2018", 6);
rowIndex++;
}
rowIndex += 2;
}
wb.Write(stream);
}
}
public static void ExportReportHeader(string title, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber)
{
var row = sheet.CreateRow(rowNumber++);
row.HeightInPoints = 27;
var titleCell = row.CreateCell(0);
titleCell.SetCellValue(title);
titleCell.CellStyle = allStyles.HeaderStyle;
titleCell.CellStyle.WrapText = true;
var titleMerge = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7);
sheet.AddMergedRegion(titleMerge);
row = sheet.CreateRow(rowNumber++);
}
public byte[] GetExcelDocument(ExcelMeta excelMeta)
{
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet("CARS");
fontWhite = workbook.CreateFont(); fontWhite.Color = IndexedColors.White.Index; fontWhite.IsBold = true;
fontBlack = workbook.CreateFont(); fontBlack.IsBold = true;
heading1 = GetBasicHeaderStyle(IndexedColors.Grey80Percent.Index, fontWhite, IndexedColors.Grey80Percent.Index);
heading2 = GetBasicHeaderStyle(IndexedColors.Grey40Percent.Index, fontBlack, IndexedColors.Grey80Percent.Index);
heading2.Alignment = HorizontalAlignment.Left;
rowHead = GetBasicHeaderStyle(IndexedColors.White.Index, fontBlack, IndexedColors.Grey80Percent.Index);
columnHead = rowHead;
content = GetBasicStyle();
for (int index = 0; index < excelMeta.ColumnWidths.Length; index++)
{
sheet.SetColumnWidth(index, Convert.ToInt32(256 * 20 * (excelMeta.ColumnWidths[index] > 0 ? excelMeta.ColumnWidths[index] : 1)));
}
for (int r = 0; r < excelMeta.Meta.Count; r++)
{
IRow row = sheet.GetRow(r) == null?sheet.CreateRow(r) : sheet.GetRow(r);
List <ExcelCellMeta> cList = excelMeta.Meta.ElementAt(r);
for (int c = 0; c < cList.Count; c++)
{
List <ICell> placeHoldres = row.Cells;
ExcelCellMeta cellMeta = cList.ElementAt(c);
if (!placeHoldres.Any(pc => pc.ColumnIndex == cellMeta.ColumnIndex))
{
ICell cell = row.CreateCell(cellMeta.ColumnIndex);
if (!string.IsNullOrEmpty(cellMeta.Content))
{
cell.SetCellValue(cellMeta.Content);
}
if (GetPropValue(cellMeta.StyleName) != null && GetPropValue(cellMeta.StyleName) is ICellStyle)
{
cell.CellStyle = GetPropValue(cellMeta.StyleName) as ICellStyle;
}
else
{
cell.CellStyle = content;
}
int spanUptoColumn = cellMeta.ColumnIndex + cellMeta.ColSpan - 1;
int spanUptoRow = r + cellMeta.RowSpan - 1;
if (cellMeta.ColSpan > 1 && cellMeta.RowSpan <= 1)
{
for (int s = 1; s < cellMeta.ColSpan; s++)
{
ICell dummy = row.CreateCell(cellMeta.ColumnIndex + s);
dummy.CellStyle = cell.CellStyle;
}
}
else if (cellMeta.RowSpan > 1 && cellMeta.ColSpan <= 1)
{
for (int s = 1; s < cellMeta.RowSpan; s++)
{
IRow dummyRow = sheet.CreateRow(r + s);
ICell dummy = dummyRow.CreateCell(cellMeta.ColumnIndex);
dummy.CellStyle = cell.CellStyle;
}
}
else if (cellMeta.RowSpan > 1 && cellMeta.ColSpan > 1)
{
for (int cs = 0; cs < cellMeta.ColSpan; cs++)
{
if (cs != 0)
{
ICell dummy = row.CreateCell(cellMeta.ColumnIndex + cs);
dummy.CellStyle = cell.CellStyle;
}
for (int rs = 1; rs < cellMeta.RowSpan; rs++)
{
IRow dummyRow = sheet.GetRow(r + rs) == null?sheet.CreateRow(r + rs) : sheet.GetRow(r + rs);
ICell dummy = dummyRow.CreateCell(cellMeta.ColumnIndex + cs);
dummy.CellStyle = cell.CellStyle;
}
}
}
if (cellMeta.RowSpan > 1 || cellMeta.ColSpan > 1)
{
NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(r, spanUptoRow, cellMeta.ColumnIndex, spanUptoColumn);
sheet.AddMergedRegion(cra);
}
}
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
byte[] toReturn = ms.ToArray();
return(toReturn);
}
private void saveFileDialog1_FileOk(object sender, CancelEventArgs e)
{
try
{
var result = _iReportService.GetProductivity(new DateTime(start.Year, start.Month, start.Day, 0, 0, 0), new DateTime(end.Year, end.Month, end.Day, 23, 59, 59));
var quantityViewModel = new List<SaleViewModel>();
foreach (var productivity in result)
{
var temp = new SaleViewModel();
temp.Sales = _iSaleService.GetQuantity(new DateTime(start.Year, start.Month, start.Day, 0, 0, 0), new DateTime(end.Year, end.Month, end.Day, 23, 59, 59), productivity.Code);
temp.Code = productivity.Code;
quantityViewModel.Add(temp);
}
string name = saveFileDialog1.FileName;
var wb = new XSSFWorkbook();
// tab name
ISheet sheet = wb.CreateSheet("Bao cao SL ban ra hang ngay");
// header
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("BÁO CÁO SẢN PHẨM HÀNG NGÀY CÔNG TY ATZ");
NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 2);
sheet.AddMergedRegion(cra);
// column header
IRow row3 = sheet.CreateRow(2);
ICell cell0 = row3.CreateCell(0);
cell0.SetCellValue("STT");
ICell cell1 = row3.CreateCell(1);
cell1.SetCellValue("MÃ CHƯƠNG TRÌNH");
ICell cell2 = row3.CreateCell(2);
cell2.SetCellValue("CHƯƠNG TRÌNH");
//ICell cell3 = row3.CreateCell(3);
//cell3.SetCellValue("DURATION");
ICell cell4 = row3.CreateCell(3);
cell4.SetCellValue("CATEGORY");
//ICell cell5 = row3.CreateCell(5);
//cell5.SetCellValue("GIÁ SẢN PHẨM");
ICell cell6 = row3.CreateCell(4);
cell6.SetCellValue("Ghi chú");
var tempStart = start;
var k = 5;
while (DateTime.Compare(tempStart, end) <= 0)
{
ICell cellk = row3.CreateCell(k);
cellk.SetCellValue(tempStart.ToString("dd/MM/yyyy"));
tempStart = tempStart.AddDays(1);
k++;
}
// add Program Code
int i = 3;
foreach (var item in result)
{
var time = Convert.ToDateTime(item.Duration);
if (time.Minute > 4)
{
IRow row_temp = sheet.CreateRow(i);
ICell cell_temp0 = row_temp.CreateCell(0);
cell_temp0.SetCellValue(i - 2);
ICell cell_temp1 = row_temp.CreateCell(1);
cell_temp1.SetCellValue(item.Code);
ICell cell_temp2 = row_temp.CreateCell(2);
cell_temp2.SetCellValue(item.Name);
//ICell cell_temp3 = row_temp.CreateCell(3);
//DateTime time1 = DateTime.Today;
//time1 = time1.AddMinutes(time.Minute).AddSeconds(time.Second);
//cell_temp3.SetCellValue(time1);
//ICellStyle style = wb.CreateCellStyle();
//cell_temp3.CellStyle = style;
//IDataFormat dataFormatCustom = wb.CreateDataFormat();
//cell_temp3.CellStyle.DataFormat = dataFormatCustom.GetFormat("mm:ss");
ICell cell_temp4 = row_temp.CreateCell(3);
cell_temp4.SetCellValue(item.Category);
//ICell cell_temp5 = row_temp.CreateCell(5);
//cell_temp5.SetCellValue(item.Price);
ICell cell_temp6 = row_temp.CreateCell(4);
cell_temp6.SetCellValue(item.Note);
var tempStart1 = start;
var k1 = 5;
var sales = quantityViewModel.Where(x=> x.Code == item.Code).FirstOrDefault();
while (DateTime.Compare(tempStart1, end) <= 0)
{
ICell cellk = row_temp.CreateCell(k1);
if (sales != null)
{
var q = sales.Sales.Where(y => y.Date.Year == tempStart1.Year && y.Date.Month == tempStart1.Month && y.Date.Day == tempStart1.Day).FirstOrDefault();
if (q != null)
{
cellk.SetCellValue(q.Quantity);
}
else
{
cellk.SetCellValue(0);
}
}
else
{
cellk.SetCellValue(0);
}
tempStart1 = tempStart1.AddDays(1);
k1++;
}
i++;
}
}
for (int l = 0; l < row3.LastCellNum; l++)
{
sheet.AutoSizeColumn(l);
}
using (FileStream stream = new FileStream(name, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
wb.Write(stream);
stream.Close();
}
var successForm = new SuccessForm();
successForm.ShowDialog();
}
catch (Exception ex)
{
var errorForm = new ErrorForm(ex.Message);
errorForm.ShowDialog();
}
}
protected void btnReport_Click(object sender, EventArgs e)
{
ReportDB _db = new ReportDB();
DataTable formTable = _db.GetMenuItem(pcId);
DataSet ds = null;
string actualYear = ConfigurationManager.AppSettings["ActualYear"];
////Create new Excel Workbook
var workbook = new HSSFWorkbook();
CellStyle cellStyleBold = workbook.CreateCellStyle();
Font boldFont = workbook.CreateFont();
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
cellStyleBold.SetFont(boldFont);
foreach (DataRow item in formTable.Rows)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(item[0].ToString());
//Getting reportData
ds = new ReportDB().GetReportDataForExcel(pcId, item[0].ToString(), Convert.ToInt32(actualYear), unit);
//setting width of cloumns in excel
sheet.SetColumnWidth(0, 25 * 256);
for (int i = 2; i < ds.Tables[0].Columns.Count; i++)
{
sheet.SetColumnWidth(i, 15 * 256);
}
HSSFRow firstRow = (HSSFRow)sheet.CreateRow(0);
firstRow.CreateCell(1).SetCellValue(headerContent);
firstRow.GetCell(1).CellStyle = cellStyleBold;
firstRow.GetCell(1).CellStyle.WrapText = true;
//firstRow.GetCell(1).Row.Height = 800;
NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 5);
sheet.AddMergedRegion(cra);
HSSFRow thirdRow = (HSSFRow)sheet.CreateRow(2);
thirdRow.CreateCell(0).SetCellValue(string.Format(ConfigurationManager.AppSettings["formName"], tfName.ToUpper()));
thirdRow.GetCell(0).CellStyle = cellStyleBold;
HSSFRow forthRow = (HSSFRow)sheet.CreateRow(3);
if (ds.Tables[2].Rows.Count > 0)
{
forthRow.CreateCell(0).SetCellValue(Convert.ToString(ds.Tables[2].Rows[0][0]));
forthRow.GetCell(0).CellStyle = cellStyleBold;
}
HSSFRow fifthRow = (HSSFRow)sheet.CreateRow(5);
fifthRow.CreateCell(1).SetCellValue(GetActualYear(actualYear));
for (int i = 2; i < ds.Tables[1].Rows.Count + 2; i++)
{
var row = ds.Tables[1].Rows[i - 2];
fifthRow.CreateCell(i).SetCellValue(Convert.ToString(row[0]));
}
// handling value.
int rowIndex = 6;
HSSFRow sixthRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < ds.Tables[0].Columns.Count - 1; i++)
{
sixthRow.CreateCell(i).SetCellValue(ds.Tables[0].Columns[i].ColumnName);
sixthRow.GetCell(i).CellStyle = cellStyleBold;
}
foreach (DataRow headerRow in ds.Tables[3].Rows)
{
string filterExp = string.Format("MainHead_tariff_Name='{0}'", headerRow[0]);
HSSFRow subHeadRow = (HSSFRow)sheet.CreateRow(++rowIndex);
subHeadRow.CreateCell(0).SetCellValue(Convert.ToString(headerRow[0]));
subHeadRow.GetCell(0).CellStyle = cellStyleBold;
foreach (DataRow row in ds.Tables[0].Select(filterExp))
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(++rowIndex);
foreach (DataColumn column in ds.Tables[0].Columns)
{
if (column.ColumnName == "MainHead_tariff_Name")
continue;
if (!System.DBNull.Value.Equals(row[column]))
{
if (column.DataType == typeof(Nullable))
dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column]));
else if (column.DataType == typeof(DateTime))
dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToDateTime(row[column]));
else if (column.DataType == typeof(decimal))
dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToDouble(row[column]));
else
dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column]));
}
}
}
HSSFRow subTotalRow = (HSSFRow)sheet.CreateRow(++rowIndex);
subTotalRow.CreateCell(0).SetCellValue("Sub Total");
subTotalRow.GetCell(0).CellStyle = cellStyleBold;
foreach (DataColumn column in ds.Tables[0].Columns)
{
}
for (int i = 1; i < ds.Tables[0].Columns.Count - 1; i++)
{
if (headerRow[i] != DBNull.Value)
{
subTotalRow.CreateCell(i).SetCellValue(Convert.ToDouble(headerRow[i]));
subTotalRow.GetCell(i).CellStyle = cellStyleBold;
}
}
HSSFRow blankRow = (HSSFRow)sheet.CreateRow(++rowIndex);
}
}
//Write the Workbook to a memory stream
MemoryStream output = new MemoryStream();
workbook.Write(output);
//return File(output.ToArray(), //The binary data of the XLS file
//"application/vnd.ms-excel",//MIME type of Excel files
//"ArticleList.xls");
string fileName = "report.xls";
const int bufferLength = 10000;
byte[] buffer = new Byte[bufferLength];
int length = 0;
if (!string.IsNullOrEmpty(Convert.ToString(Session["centername"])))
{
fileName = Convert.ToString(Session["centername"]) + ".xls";
}
Response.ContentType = "Application/x-msexcel";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + "");
Stream download = null;
try
{
using (FileStream file = new FileStream(Server.MapPath("/Export/" + fileName + ""), FileMode.Create, FileAccess.Write))
{
output.WriteTo(file);
}
download = new FileStream(Server.MapPath("/Export/" + fileName + ""),
FileMode.Open,
FileAccess.Read);
do
{
if (Response.IsClientConnected)
{
length = download.Read(buffer, 0, bufferLength);
Response.OutputStream.Write(buffer, 0, length);
buffer = new Byte[bufferLength];
}
else
{
length = -1;
}
}
while (length > 0);
Response.Flush();
Response.End();
}
finally
{
if (download != null)
download.Close();
}
BindGrid();
}
public async Task <IActionResult> Export([FromQuery] DateTime date)
{
string sWebRootFolder = _hostingEnvironment.WebRootPath + "/Export";
string d = date.ToShortDateString().Split(@"/").Join("");
string sFileName = @"OrderReport_" + d + @".xlsx";
string URL = string.Format("{0}://{1}/{2}/{3}", Request.Scheme, "localhost:44309", "Export", sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet(date.ToShortDateString().Split(@"/").Join(""));
IRow rowHeader = excelSheet.CreateRow(0);
ICellStyle defaultStyle = workbook.CreateCellStyle();
defaultStyle.Alignment = HorizontalAlignment.Center;
defaultStyle.VerticalAlignment = VerticalAlignment.Center;
defaultStyle.WrapText = true; //wrap the text in the cell
defaultStyle.BorderBottom = BorderStyle.Thin;
defaultStyle.BorderTop = BorderStyle.Thin;
defaultStyle.BorderLeft = BorderStyle.Thin;
defaultStyle.BorderRight = BorderStyle.Thin;
rowHeader.CreateCell(0).SetCellValue("Hóa đơn");
rowHeader.CreateCell(1).SetCellValue("Khách hàng");
rowHeader.CreateCell(2).SetCellValue("Địa chỉ");
rowHeader.CreateCell(3).SetCellValue("Danh sách mặt hàng");
rowHeader.CreateCell(4).SetCellValue("");
rowHeader.CreateCell(5).SetCellValue("");
rowHeader.CreateCell(6).SetCellValue("Trạng thái");
rowHeader.CreateCell(7).SetCellValue("Tổng giá");
rowHeader.GetCell(0).CellStyle = defaultStyle;
rowHeader.GetCell(1).CellStyle = defaultStyle;
rowHeader.GetCell(2).CellStyle = defaultStyle;
rowHeader.GetCell(3).CellStyle = defaultStyle;
rowHeader.GetCell(4).CellStyle = defaultStyle;
rowHeader.GetCell(5).CellStyle = defaultStyle;
rowHeader.GetCell(6).CellStyle = defaultStyle;
rowHeader.GetCell(7).CellStyle = defaultStyle;
IRow rowHeader2 = excelSheet.CreateRow(1);
rowHeader2.CreateCell(0).SetCellValue("");
rowHeader2.CreateCell(1).SetCellValue("");
rowHeader2.CreateCell(2).SetCellValue("");
rowHeader2.CreateCell(3).SetCellValue("Mặt hàng");
rowHeader2.CreateCell(4).SetCellValue("Số lượng");
rowHeader2.CreateCell(5).SetCellValue("Giá");
rowHeader2.CreateCell(6).SetCellValue("");
rowHeader2.CreateCell(7).SetCellValue("");
rowHeader2.GetCell(0).CellStyle = defaultStyle;
rowHeader2.GetCell(1).CellStyle = defaultStyle;
rowHeader2.GetCell(2).CellStyle = defaultStyle;
rowHeader2.GetCell(3).CellStyle = defaultStyle;
rowHeader2.GetCell(4).CellStyle = defaultStyle;
rowHeader2.GetCell(5).CellStyle = defaultStyle;
rowHeader2.GetCell(6).CellStyle = defaultStyle;
rowHeader2.GetCell(7).CellStyle = defaultStyle;
var cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 3, 5);
var cr1 = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
var cr2 = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
var cr3 = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
var cr4 = new NPOI.SS.Util.CellRangeAddress(0, 1, 6, 6);
var cr5 = new NPOI.SS.Util.CellRangeAddress(0, 1, 7, 7);
excelSheet.AddMergedRegion(cra);
excelSheet.AddMergedRegion(cr1);
excelSheet.AddMergedRegion(cr2);
excelSheet.AddMergedRegion(cr3);
excelSheet.AddMergedRegion(cr4);
excelSheet.AddMergedRegion(cr5);
excelSheet.SetColumnWidth(0, 8000);
excelSheet.SetColumnWidth(1, 6000);
excelSheet.SetColumnWidth(2, 9000);
excelSheet.SetColumnWidth(3, 6000);
//in ra danh sach cac khach hang
IEnumerable <CustomerModel> customers = _CustomerAppService.All();
SearchInvoiceRequest request = new SearchInvoiceRequest()
{
customer_code = null,
customer_id = 0,
from_time = date,
page = 0,
page_size = 0
};
var result = await _InvoiceAppService.getAllInvoice(request);
if (result.Data == null)
{
return(BadRequest());
}
int rowIndex = 2;
int startRowIndex = 2;
string address = "";
foreach (InvoiceModel invoice in result.Data)
{
startRowIndex = rowIndex;
IRow row = excelSheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(invoice.Code);
row.CreateCell(1).SetCellValue(invoice.CustomerName);
address = invoice.Address == null ? "" :
invoice.Address.StreetNumber + ", "
+ invoice.Address.Street + ", "
+ invoice.Address.District + ", "
+ invoice.Address.City + ", "
+ invoice.Address.Country;
row.CreateCell(2).SetCellValue(address);
row.CreateCell(6).SetCellValue(Common.Common.GetStatusInvoice(invoice.Status));
row.CreateCell(7).SetCellValue(invoice.TotalPrice);
foreach (InvoiceItemModel item in invoice.Items)
{
if (rowIndex != startRowIndex)
{
row = excelSheet.CreateRow(rowIndex);
row.CreateCell(0).CellStyle = defaultStyle;
row.CreateCell(1).CellStyle = defaultStyle;
row.CreateCell(2).CellStyle = defaultStyle;
row.CreateCell(6).CellStyle = defaultStyle;
row.CreateCell(7).CellStyle = defaultStyle;
}
row.CreateCell(3).SetCellValue(item.ProductName);
row.GetCell(3).CellStyle = defaultStyle;
row.CreateCell(4).SetCellValue(item.Quantity + " " + item.UnitName);
row.GetCell(4).CellStyle = defaultStyle;
row.CreateCell(5).SetCellValue(item.TotalPrice);
row.GetCell(5).CellStyle = defaultStyle;
rowIndex++;
}
var cra1 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 0, 0);
var cra2 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 1, 1);
var cra3 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 2, 2);
var cra4 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 6, 6);
var cra5 = new NPOI.SS.Util.CellRangeAddress(startRowIndex, rowIndex - 1, 7, 7);
excelSheet.AddMergedRegion(cra1);
excelSheet.AddMergedRegion(cra2);
excelSheet.AddMergedRegion(cra3);
excelSheet.AddMergedRegion(cra4);
excelSheet.AddMergedRegion(cra5);
IRow rowStart = excelSheet.GetRow(startRowIndex);
rowStart.GetCell(0).CellStyle = defaultStyle;
rowStart.GetCell(1).CellStyle = defaultStyle;
rowStart.GetCell(2).CellStyle = defaultStyle;
rowStart.GetCell(6).CellStyle = defaultStyle;
rowStart.GetCell(7).CellStyle = defaultStyle;
}
workbook.Write(fs);
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
//FileStreamResult f = File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName);
return(Ok(URL));
}
/// <summary>
/// 将DataTable转为NPOI工作簿时添加表数据
/// </summary>
/// <param name="sheet"></param>
/// <param name="dt"></param>
/// <param name="cellStyle"></param>
/// <param name="colTypeList">每一列的数据类型</param>
/// <param name="groups">表头行数（作为数据行第一行的索引）</param>
/// <param name="maxColumnMerge">Excel所有非表头区域的合并单元格的最大列索引，若不启用则传人-1</param>
private static void AddSheetBody(ISheet sheet, DataTable dt, ICellStyle cellStyle, List <int> colTypeList, int groups, int maxColumnMerge)
{
IRow row;
ICell cell;
ICellStyle cellStyleDecimal = GetCellStyleDecimal(sheet.Workbook);
ICellStyle cellStyleDateTime = GetCellStyleDateTime(sheet.Workbook);
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet.GetRow(i + groups);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j] == null || string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
{
continue;
}
#region 当列索引小于最大合并列索引时判断单元格是否处于合并区域内
//当列索引小于最大合并列索引时判断单元格是否处于合并区域内
bool flagSkip = false;
if (j <= maxColumnMerge)
{
for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
{
NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
if (a.LastRow < groups)//剔除标题处的合并区域
{
continue;
}
//当前单元格是处于合并区域内且不为合并区域第一个单元格时，跳过此单元格
if (a.FirstRow < i + groups && a.LastRow > i + groups)
{
flagSkip = true;
//Debug.WriteLine("第" + i.ToString() + "行" + j.ToString() + "列被跳过");
break;
}
}
}
if (flagSkip)
{
continue;
}
#endregion
//创建单元格
cell = row.CreateCell(j);
if (colTypeList.Count == 0 || colTypeList.Count < j || colTypeList[j] <= 0)//无法获取到该列类型
{
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
else
{
string cellText = dt.Rows[i][j].ToString();
try
{
switch (colTypeList[j])
{
case 1: cell.SetCellValue(int.Parse(cellText)); //int类型
cell.CellStyle = cellStyle;
break;
case 2: cell.SetCellValue(double.Parse(cellText)); //decimal数据类型
cell.CellStyle = cellStyleDecimal;
break;
case 3: cell.SetCellValue(DateTime.Parse(cellText)); //日期类型
cell.CellStyle = cellStyleDateTime;
break;
default: cell.SetCellValue(cellText);
cell.CellStyle = cellStyle;
break;
}
}
catch
{
cell.SetCellValue("单元格导出失败");
}
}
}
}
}
private void generate_my_data()
{
ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");//*
DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
DataTable dT = (DataTable)Session["header_01"];
table.TableName = "Załatwienia";
table.Columns.Remove("id_");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
var crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0);
IRow row0 = sheet0.CreateRow(0);
#region tabela1
foreach (DataRow dR in dr.getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(1);
foreach (DataRow dR in dr.getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(2);
foreach (DataRow dR in dr.getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
int rol = 3;
foreach (DataRow rowik in table.Rows)
{
row0 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row0.CreateCell(i).SetCellValue(ji);
row0.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
#endregion tabela1
#region drugi arkusz
// druga tabela
view = (DataView)dane_do_tabeli_2.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.TableName = "Załatwienia";
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
//
//robienie
int ro = 2;
//-----------------
IDataFormat format = hssfworkbook.CreateDataFormat();
ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
IRow row2 = sheet1.CreateRow(0);
dT.Clear();
dT = (DataTable)Session["header_02"];
//===========
foreach (DataRow dR in dr.getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(1);
foreach (DataRow dR in dr.getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(2);
foreach (DataRow dR in dr.getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
rol = 3;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
foreach (DataRow rowik in table.Rows)
{
row2 = sheet1.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
#endregion drugi arkusz
// trzeci sheet
view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
sheet1.AutoSizeColumn(0, true);
sheet1.AutoSizeColumn(1, true);
ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");
row2 = sheet2.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet2.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Wyznaczenia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet2.AddMergedRegion(crs);
row2 = sheet2.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet2.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// czwarty sheet
view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
//table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_09");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");
row2 = sheet3.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet3.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet3.AddMergedRegion(crs);
row2 = sheet3.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
// row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet3.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
}
public string dataTableToExcel(System.Data.DataTable dt,string title,string subject,string category,string company,string author)
{
Microsoft.Win32.SaveFileDialog saveFileDialog1 = new Microsoft.Win32.SaveFileDialog();
//设置文件类型
saveFileDialog1.Filter = " Excel files(*.xls)|*.xls|All files(*.*)|*.*";
//设置默认文件类型显示顺序
saveFileDialog1.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
saveFileDialog1.RestoreDirectory = true;
string localFilePath = "";
if (!saveFileDialog1.ShowDialog() == true)
{
return null;
}
//创建一个新的xls文件
HSSFWorkbook workbook = new HSSFWorkbook();
//创建DocumentSummaryInformation（选填）
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = company;// "国家电网XXXXX公司";
dsi.Category = category;// "报表";
//创建SummaryInformation（选填）
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;// "商品列表";
si.Title = title;
si.Author = author;// "Kevin Pan";
//把创建好的对象赋给Workbook
workbook.DocumentSummaryInformation = dsi;
workbook.SummaryInformation = si;
//创建一个Sheet
ISheet sheet = workbook.CreateSheet("Sheet1");
sheet.DefaultRowHeight = 300;
//创建标题
IRow rowTitle = sheet.CreateRow(0);
rowTitle.Height = 500;
ICellStyle styleTitle = workbook.CreateCellStyle();
styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleTitle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
IFont fontTitle = workbook.CreateFont();
fontTitle.FontName = "宋体";
fontTitle.FontHeightInPoints = 18;
styleTitle.SetFont(fontTitle);
//创建表格样式
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
ICellStyle style = workbook.CreateCellStyle(); ;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font);
// 标题
ICell cellTitle = rowTitle.CreateCell(0);
cellTitle.SetCellValue(title);
cellTitle.CellStyle = styleTitle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0,dt.Columns.Count)); //合并单元格
//创建表头
IRow rowHead = sheet.CreateRow(1);
rowHead.CreateCell(0).SetCellValue("序号");
rowHead.GetCell(0).CellStyle = style;
sheet.SetColumnWidth(0, 256 * 5);
for(int i =0; i< dt.Columns.Count;i++)
{
rowHead.CreateCell(i+1).SetCellValue(dt.Columns[i].ColumnName);
rowHead.GetCell(i+1).CellStyle = style;
sheet.SetColumnWidth(i+1, 256 * 20);
}
sheet.SetColumnWidth(1, 256 * 30);
sheet.SetColumnWidth(6, 256 * 25);
//获取商品列表数据
//List<ProductModel> dataList = GetProductList();
//绑定表内容
string last_id = "";
int last_row = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow rowContent = sheet.CreateRow(i+2);
rowContent.CreateCell(0).SetCellValue(i+1);
rowContent.GetCell(0).CellStyle = style;
//string id = dt.Rows[1][dt.Columns[0].ColumnName].ToString();
if (last_id.Equals(dt.Rows[i][dt.Columns[0].ColumnName].ToString()))
{
//sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(last_row, i+2, 1, 1));
//string v = sheet.GetRow(last_row).GetCell(1).RichStringCellValue.ToString();
//sheet.GetRow(last_row).GetCell(1).SetCellValue("");
NPOI.SS.Util.CellRangeAddress r = new NPOI.SS.Util.CellRangeAddress(last_row, i + 2, 1, 1);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i+1, i + 2, 1, 1));
//sheet.GetRow(last_row).GetCell(1).SetCellValue(v);
}
else
{
rowContent.CreateCell(1).SetCellValue(dt.Rows[i][dt.Columns[0].ColumnName].ToString());
rowContent.GetCell(1).CellStyle = style;
last_id = dt.Rows[i][dt.Columns[0].ColumnName].ToString();
last_row = i + 2;
}
for(int j =1; j< dt.Columns.Count;j++)
{
rowContent.CreateCell(j+1).SetCellValue(dt.Rows[i][dt.Columns[j].ColumnName].ToString());
rowContent.GetCell(j+1).CellStyle = style;
}
}
//输出
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
//File f = new File(ms, "application/vnd.ms-excel", "商品列表.xls");
///
// Microsoft.Win32.SaveFileDialog saveFileDialog1 = new Microsoft.Win32.SaveFileDialog();
//设置文件类型
//saveFileDialog1.Filter = " Excel files(*.xls)|*.xls|All files(*.*)|*.*";
//设置默认文件类型显示顺序
// saveFileDialog1.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
// saveFileDialog1.RestoreDirectory = true;
//string localFilePath = "";
//点了保存按钮进入
//获得文件路径
localFilePath = saveFileDialog1.FileName.ToString();
//获取文件名，不带路径
string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
//获取文件路径，不带文件名
//FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
//给文件名前加上时间
//newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;
//在文件名里加字符
//saveFileDialog1.FileName.Insert(1,"dameng");
////
try
{
FileStream fs = File.OpenWrite(@localFilePath);
workbook.Write(fs);
fs.Close();
}catch(FileLoadException e)
{
;//
}
//ConfirmDialog m = new ConfirmDialog();
//m.Show();
//m.ShowDialog();
return localFilePath;
}
public CellRangeAddressTreeNode(NPOI.SS.Util.CellRangeAddress record)
{
this.Record = record;
this.Text = record.GetType().Name;
this.ImageKey = "Binary";
}
public static IRow CopyRow(this IRow dstRow, ISheet srcSheet, int srcRowNum)
{
IRow sourceRow = srcSheet.GetRow(srcRowNum) as IRow;
ICell oldCell, newCell;
int i;
// Loop through source columns to add to new row
for (i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
oldCell = sourceRow.GetCell(i) as ICell;
newCell = dstRow.GetCell(i) as ICell;
if (newCell == null)
{
newCell = dstRow.CreateCell(i) as ICell;
}
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
newCell.CellStyle = oldCell.CellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
//If there are are any merged regions in the source row, copy to new row
NPOI.SS.Util.CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
for (i = 0; i < srcSheet.NumMergedRegions; i++)
{
cellRangeAddress = srcSheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
newCellRangeAddress = new NPOI.SS.Util.CellRangeAddress(dstRow.RowNum,
(dstRow.RowNum + (cellRangeAddress.LastRow - cellRangeAddress.FirstRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
dstRow.Sheet.AddMergedRegion(newCellRangeAddress);
}
}
//複製行高到新列
//if (copyRowHeight)
dstRow.Height = sourceRow.Height;
////重製原始列行高
//if (resetOriginalRowHeight)
// sourceRow.Height = worksheet.DefaultRowHeight;
////清掉原列
//if (IsRemoveSrcRow == true)
// worksheet.RemoveRow(sourceRow);
return(dstRow);
}
public CellRangeAddressTreeNode(NPOI.SS.Util.CellRangeAddress record)
{
this.Record = record;
this.Text = record.GetType().Name;
this.ImageKey = "Binary";
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
/// <summary>
/// 将DataTable各列中相邻行值相同的单元格合并显示
/// </summary>
/// <param name="sheet"></param>
/// <param name="dt"></param>
/// <param name="groupCount">NPOI表头行数</param>
/// <returns>被合并的列的最大索引值</returns>
private static int DataTableMergSimpleValueRow(ISheet sheet, DataTable dt, int groupCount)
{
if (dt.Rows.Count == 0 || dt.Columns.Count == 0)
{
return(0);
}
int maxColumnMerge = 0;//被合并的列的最大索引值
for (int ColumnIndex = 0; ColumnIndex < dt.Columns.Count; ColumnIndex++)
{
#region 是否停止添加合并区域
bool flagSkip = true;
if (ColumnIndex == 0)//第一列始终需要合并
{
flagSkip = false;
}
else
{
for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
{
NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
if (a.LastRow < groupCount)//剔除标题处的合并区域
{
continue;
}
//当存在最大列包含上一列的合并区域时，当前列仍为可合并状态
if (a.LastColumn >= maxColumnMerge)
{
flagSkip = false;
break;
}
}
}
if (flagSkip)//当前列的前一列不包括任何合并行时，停止后续列的行合并
{
return(maxColumnMerge - 1 >= 0 ? maxColumnMerge - 1 : 0);
}
#endregion
int rowspan = 0;
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
if (DataColumnIsSimple(sheet, dt, i, ColumnIndex, groupCount))
{
rowspan++;
}
else if (rowspan > 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(groupCount + i, groupCount + i + rowspan, ColumnIndex, ColumnIndex));
rowspan = 0;
}
}
if (rowspan > 1)//行数超过一行时第一列完全一致
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(groupCount, groupCount + rowspan + 1, ColumnIndex, ColumnIndex));
}
maxColumnMerge = ColumnIndex;
}
if (maxColumnMerge == dt.Columns.Count - 1)
{
return(maxColumnMerge);
}
return(maxColumnMerge - 1);
}
private void CargaHojaExcel(DataTable tablaExcel, string titulo, string nombreHoja, bool incluirGrafico)
{
#region --[Estilos]--
IFont fuenteTitulo = excelFile.CreateFont();
fuenteTitulo.FontName = "Calibri";
//fuenteTitulo.FontHeight = (short)FontSize.Large.GetHashCode();
fuenteTitulo.Boldweight = (short)FontBoldWeight.BOLD.GetHashCode();
IFont unaFuente = excelFile.CreateFont();
unaFuente.FontName = "Tahoma";
//unaFuente.FontHeight = (short)FontSize.Medium.GetHashCode();
IFont fuenteEncabezado = excelFile.CreateFont();
fuenteEncabezado.FontName = "Tahoma";
//fuenteEncabezado.FontHeight = (short)FontSize.Medium.GetHashCode(); ;
fuenteEncabezado.Boldweight = (short)FontBoldWeight.BOLD.GetHashCode();
ICellStyle unEstiloDecimal = excelFile.CreateCellStyle();
IDataFormat format = excelFile.CreateDataFormat();
unEstiloDecimal.DataFormat = format.GetFormat("0.00");
unEstiloDecimal.SetFont(unaFuente);
#endregion
#region --[Hoja]--
ISheet hojaExcel = excelFile.CreateSheet(nombreHoja);
NPOI.SS.Util.CellRangeAddress rango = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, tablaExcel.Columns.Count - 1);
hojaExcel.AddMergedRegion(rango);
hojaExcel.AutoSizeColumn(0);
hojaExcel.AutoSizeColumn(1);
int idxAux = 0;
IRow fila = hojaExcel.CreateRow(idxAux);
fila.CreateCell(idxAux).SetCellValue(titulo);
fila.Cells[idxAux].CellStyle.SetFont(fuenteTitulo);
idxAux++;
fila = hojaExcel.CreateRow(idxAux);
idxAux++;
RptIndicadores alumno = null;
int idAlumno = 0;
string nombre = string.Empty;
hojaExcel.AutoSizeColumn(0);
//--Agrego los encabezados--
#region --[Encabezados]--
for (int i = 0; i < tablaExcel.Columns.Count; i++)
{
fila.CreateCell(i).CellStyle.Alignment = HorizontalAlignment.CENTER;
nombre = tablaExcel.Columns[i].ColumnName;
alumno = new RptIndicadores();
int.TryParse(tablaExcel.Columns[i].ColumnName, out idAlumno);
if (idAlumno > 0)
{
alumno = lista.Find(p => p.idAlumno == Convert.ToInt16(idAlumno));
nombre = alumno.alumnoApellido.Trim() + " " + alumno.alumnoNombre.Trim();
}
fila.CreateCell(i).SetCellValue(nombre);
fila.Cells[i].CellStyle.SetFont(fuenteEncabezado);
hojaExcel.AutoSizeColumn(i);
}
#endregion
//--Agrego los datos hoja--
#region --[Datos]--
decimal valorDato = 0;
int cantFilas = 0;
try
{
for (int i = 0; i < tablaExcel.Rows.Count; i++)
{
fila = hojaExcel.CreateRow(idxAux);
idxAux++;
cantFilas++;
for (int j = 0; j < tablaExcel.Columns.Count; j++)
{
try
{
valorDato = decimal.Parse(tablaExcel.Rows[i][j].ToString());
fila.CreateCell(j).SetCellValue(Convert.ToDouble(valorDato));
fila.Cells[j].CellStyle = unEstiloDecimal;
fila.Cells[j].SetCellType(CellType.NUMERIC);
}
catch
{
fila.CreateCell(j).SetCellValue(tablaExcel.Rows[i][j].ToString());
fila.Cells[j].CellStyle.SetFont(fuenteEncabezado);
}
}
}
}
catch (Exception ex)
{ throw ex; }
#endregion
//Acomodo las columnas
for (int j = 0; j < tablaExcel.Columns.Count; j++)
{
hojaExcel.AutoSizeColumn(j);
}
hojaExcel.AutoSizeColumn(0);
#endregion
if (incluirGrafico)
{
#region --[Grafico]--
//map the path to the img folder
//string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), "img");
string imagesPath = System.IO.Path.GetFullPath(NombrePNG);
//grab the image file
//create an image from the path
System.Drawing.Image image = System.Drawing.Image.FromFile(imagesPath);
MemoryStream ms = new MemoryStream();
//pull the memory stream from the image (I need this for the byte array later)
image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
//the drawing patriarch will hold the anchor and the master information
var patriarch = hojaExcel.CreateDrawingPatriarch();
//store the coordinates of which cell and where in the cell the image goes
HSSFClientAnchor anchor = new HSSFClientAnchor(20, 0, 40, 20, 3, cantFilas + 3, 4, cantFilas + 10);
//types are 0, 2, and 3. 0 resizes within the cell, 2 doesn't
anchor.AnchorType = 2;
//add the byte array and encode it for the excel file
int index = excelFile.AddPicture(ms.ToArray(), PictureType.PNG);
var picture = patriarch.CreatePicture(anchor, index);
picture.Resize();
picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;
hojaExcel.ForceFormulaRecalculation = true;
#endregion
}
}
public void FillReg17SpreadSheet(Dictionary <string, Reg17Record> records)
{
xssfwb = new XSSFWorkbook();
XSSFFont myFont = (XSSFFont)xssfwb.CreateFont();
myFont.FontHeightInPoints = 12;
myFont.Boldweight = (short)FontBoldWeight.Bold;
//myFont.FontName = "Tahoma";
XSSFFont myFont2 = (XSSFFont)xssfwb.CreateFont();
myFont2.FontHeightInPoints = 18;
myFont2.Boldweight = (short)FontBoldWeight.Bold;
//myFont2.FontName = "Tahoma";
IFont boldFont = xssfwb.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
var color = new XSSFColor(new byte[] { 196, 215, 155 });
XSSFCellStyle borderedCellStyle = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle.SetFont(myFont);
borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
XSSFCellStyle borderedCellStyle2 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle2.SetFont(myFont2);
borderedCellStyle2.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle2.Alignment = HorizontalAlignment.Center;
borderedCellStyle2.SetFillForegroundColor(color);
borderedCellStyle2.SetFillBackgroundColor(color);
borderedCellStyle2.FillBackgroundXSSFColor = color;
borderedCellStyle2.FillForegroundXSSFColor = color;
borderedCellStyle2.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle borderedCellStyle3 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle3.SetFont(myFont);
borderedCellStyle3.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle3.Alignment = HorizontalAlignment.Center;
borderedCellStyle3.SetFillForegroundColor(color);
borderedCellStyle3.SetFillBackgroundColor(color);
borderedCellStyle3.FillBackgroundXSSFColor = color;
borderedCellStyle3.FillForegroundXSSFColor = color;
borderedCellStyle3.FillPattern = FillPattern.SolidForeground;
borderedCellStyle3.WrapText = true;
ISheet Sheet = xssfwb.CreateSheet("Report");
Sheet.SetColumnWidth(0, 22 * 256);
Sheet.SetColumnWidth(1, 17 * 256);
Sheet.SetColumnWidth(2, 25 * 256);
Sheet.SetColumnWidth(3, 15 * 256);
Sheet.SetColumnWidth(4, 96 * 256);
Sheet.SetColumnWidth(5, 35 * 256);
//Creat The Headers of the excel
IRow row1 = Sheet.CreateRow(0);
//styling
ICellStyle boldStyle = xssfwb.CreateCellStyle();
boldStyle.SetFont(boldFont);
//Create The Actual Cells
row1.CreateCell(0).SetCellValue("Sistema PCI DSS");
row1.GetCell(0).CellStyle = boldStyle;
IRow row2 = Sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("REG-17");
row2.GetCell(0).CellStyle = boldStyle;
IRow row3 = Sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("Informe de Aplicación de Parches");
row3.GetCell(0).CellStyle = borderedCellStyle2;
var cra = new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 5);
Sheet.AddMergedRegion(cra);
row2.CreateCell(0).SetCellValue("REG-17");
row2.GetCell(0).CellStyle = boldStyle;
IRow row4 = Sheet.CreateRow(4);
row4.CreateCell(0).SetCellValue("Servidores");
row4.GetCell(0).CellStyle = borderedCellStyle;
row4.CreateCell(1).SetCellValue("SQLPCI");
row4.GetCell(1).CellStyle = boldStyle;
row4.CreateCell(2).SetCellValue("Fecha de Aplicación");
row4.GetCell(2).CellStyle = borderedCellStyle;
row4.CreateCell(3).SetCellValue(DateTime.Now.ToString());
row4.GetCell(3).CellStyle = borderedCellStyle;
IRow row5 = Sheet.CreateRow(5);
row5.CreateCell(1).SetCellValue("SRVFILE");
row5.GetCell(1).CellStyle = boldStyle;
IRow row6 = Sheet.CreateRow(6);
row6.CreateCell(1).SetCellValue("Site-Transfer");
row6.GetCell(1).CellStyle = boldStyle;
IRow row7 = Sheet.CreateRow(7);
row7.CreateCell(1).SetCellValue("WEBPCI");
row7.GetCell(1).CellStyle = boldStyle;
IRow row8 = Sheet.CreateRow(8);
row8.CreateCell(1).SetCellValue("Proxy Inverso");
row8.GetCell(1).CellStyle = boldStyle;
IRow row9 = Sheet.CreateRow(9);
row9.CreateCell(1).SetCellValue("Proxy Servicios");
row9.GetCell(1).CellStyle = boldStyle;
IRow row10 = Sheet.CreateRow(10);
row10.CreateCell(1).SetCellValue("Proxy Web");
row10.GetCell(1).CellStyle = boldStyle;
IRow row11 = Sheet.CreateRow(11);
row11.CreateCell(1).SetCellValue("Parches");
row11.GetCell(1).CellStyle = boldStyle;
IRow row12 = Sheet.CreateRow(12);
row12.CreateCell(1).SetCellValue("ADMPCI");
row12.GetCell(1).CellStyle = boldStyle;
IRow row13 = Sheet.CreateRow(13);
row13.CreateCell(1).SetCellValue("File Gateway");
row13.GetCell(1).CellStyle = boldStyle;
IRow row14 = Sheet.CreateRow(14);
row14.CreateCell(1).SetCellValue("Volume Gateway");
row14.GetCell(1).CellStyle = boldStyle;
IRow row15 = Sheet.CreateRow(15);
row15.CreateCell(1).SetCellValue("RD Gateway");
row15.GetCell(1).CellStyle = boldStyle;
IRow row16 = Sheet.CreateRow(16);
row16.CreateCell(1).SetCellValue("Data Analysis");
row16.GetCell(1).CellStyle = boldStyle;
IRow row18 = Sheet.CreateRow(18);
row18.CreateCell(0).SetCellValue("Código Parche");
row18.GetCell(0).CellStyle = borderedCellStyle3;
row18.CreateCell(1).SetCellValue("Fecha Publicación");
row18.GetCell(1).CellStyle = borderedCellStyle3;
row18.CreateCell(2).SetCellValue("Producto");
row18.GetCell(2).CellStyle = borderedCellStyle3;
row18.CreateCell(3).SetCellValue("Clasificación");
row18.GetCell(3).CellStyle = borderedCellStyle3;
row18.CreateCell(4).SetCellValue("Mejoras y Correcciones");
row18.GetCell(4).CellStyle = borderedCellStyle3;
row18.CreateCell(5).SetCellValue("Opinión Impacto del Parche");
row18.GetCell(5).CellStyle = borderedCellStyle3;
XSSFFont myFont4 = (XSSFFont)xssfwb.CreateFont();
myFont4.FontHeightInPoints = 10;
//myFont4.Boldweight = (short)FontBoldWeight.Bold;
//myFont.FontName = "Tahoma";
XSSFFont myFont5 = (XSSFFont)xssfwb.CreateFont();
myFont5.FontHeightInPoints = 8.5;
//myFont5.Boldweight = (short)FontBoldWeight.Bold;
//myFont2.FontName = "Tahoma";
boldFont.Boldweight = (short)FontBoldWeight.Bold;
XSSFCellStyle borderedCellStyle4 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle4.SetFont(myFont4);
borderedCellStyle4.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle4.Alignment = HorizontalAlignment.Center;
XSSFCellStyle borderedCellStyle5 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle5.SetFont(myFont5);
borderedCellStyle5.VerticalAlignment = VerticalAlignment.Center;
//borderedCellStyle5.Alignment = HorizontalAlignment.Center;
borderedCellStyle5.WrapText = true;
XSSFCellStyle borderedCellStyle6 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle6.SetFont(myFont4);
borderedCellStyle6.VerticalAlignment = VerticalAlignment.Center;
//borderedCellStyle6.Alignment = HorizontalAlignment.Center;
borderedCellStyle6.WrapText = true;
int i = 19;
//List<Reg17Record> records = new List<Reg17Record>();
foreach (KeyValuePair <string, Reg17Record> entry in records)
{
// do something with entry.Value or entry.Key
Reg17Record record = (Reg17Record)entry.Value;
IRow rowi = Sheet.CreateRow(i);
rowi.CreateCell(0).SetCellValue(record.PatchCode);
rowi.GetCell(0).CellStyle = borderedCellStyle4;
rowi.CreateCell(1).SetCellValue(record.PublicationDate);
rowi.GetCell(1).CellStyle = borderedCellStyle4;
rowi.CreateCell(2).SetCellValue(record.Product);
rowi.GetCell(2).CellStyle = borderedCellStyle4;
rowi.CreateCell(3).SetCellValue(record.Classification);
rowi.GetCell(3).CellStyle = borderedCellStyle4;
rowi.CreateCell(4).SetCellValue(record.EnhancementsAndCorrections);
rowi.GetCell(4).CellStyle = borderedCellStyle5;
rowi.CreateCell(5).SetCellValue(record.ImpactOpinion);
rowi.GetCell(5).CellStyle = borderedCellStyle6;
i++;
}
}
/// <summary>
/// 为Excel添加表头
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRow">GridView的HeaderRow属性</param>
/// <param name="headerCellStyle">表头格式</param>
/// <param name="flagNewLine">转行标志</param>
/// <param name="colCount">Excel表列数</param>
/// <returns>Excel表格行数</returns>
private static int AddSheetHeader(ISheet sheet, GridViewRow headerRow, ICellStyle headerCellStyle, string flagNewLine, out int colCount)
{
//int
colCount = 0; //记录GridView列数
int rowInex = 0; //记录表头的行数
IRow row = sheet.CreateRow(0);
ICell cell;
int groupCount = 0; //记录分组数
int colIndex = 0; //记录列索引，并于结束表头遍历后记录总列数
for (int i = 0; i < headerRow.Cells.Count; i++)
{
if (rowInex != groupCount)//新增了标题行时重新创建
{
row = sheet.CreateRow(rowInex);
groupCount = rowInex;
}
#region 是否跳过当前单元格
for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
{
NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
//当前单元格是处于合并区域内
if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex &&
a.FirstRow <= rowInex && a.LastRow >= rowInex)
{
colIndex++;
m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域
}
}
#endregion
cell = row.CreateCell(colIndex);
cell.CellStyle = headerCellStyle;
TableCell tablecell = headerRow.Cells[i];
//跨列属性可能为添加了html属性colspan，也可能是由cell的ColumnSpan属性指定
int colSpan = 0;
int rowSpan = 0;
#region 获取跨行跨列属性值
//跨列
if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"]))
{
colSpan = int.Parse(tablecell.Attributes["colspan"].ToString());
colSpan--;
}
if (tablecell.ColumnSpan > 1)
{
colSpan = tablecell.ColumnSpan;
colSpan--;
}
//跨行
if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"]))
{
rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString());
rowSpan--;
}
if (tablecell.RowSpan > 1)
{
rowSpan = tablecell.RowSpan;
rowSpan--;
}
#endregion
//添加excel合并区域
if (colSpan > 0 || rowSpan > 0)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan));
colIndex += colSpan + 1;//重新设置列索引
}
else
{
colIndex++;
}
string strHeader = headerRow.Cells[i].Text;
if (strHeader.Contains(flagNewLine))//换行标记，当只存在一行标题时不存在</th></tr><tr>，此时colCount无法被赋值
{
rowInex++;
colCount = colIndex;
colIndex = 0;
strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>"));
}
cell.SetCellValue(strHeader);
}
if (groupCount == 0)//只有一行标题时另外为colCount赋值
{
colCount = colIndex;
}
rowInex++;//表头结束后另起一行开始记录控件数据行索引
ICellStyle _headrStyle = GetCellStyleHead(sheet.Workbook);
for (int i = 0; i < rowInex; i++)
{
for (int j = 0; j < colCount; j++)
{
if (sheet.GetRow(i).GetCell(j) != null)
{
sheet.GetRow(i).GetCell(j).CellStyle = _headrStyle;
}
}
}
return(rowInex);
}
private int _addMonthlyInvoices(ref ISheet accountSheet, int rowNumber, Account account, int monthIndex, string monthName)
{
ICell cell;
var startRow = rowNumber;
var minRows = 30;
var headerStyle = _workbook.CreateCellStyle();
headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Maroon.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
var headerFont = _workbook.CreateFont();
headerFont.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
headerStyle.SetFont(headerFont);
var headerRange = new NPOI.SS.Util.CellRangeAddress(rowNumber, rowNumber, 0, 6);
accountSheet.AddMergedRegion(headerRange);
var row = accountSheet.CreateRow(rowNumber);
cell = row.CreateCell(0);
cell.SetCellValue($"{monthName}");
cell.CellStyle = headerStyle;
cell = row.CreateCell(6);
cell.SetCellValue($"{monthName}");
cell.CellStyle = headerStyle;
rowNumber++;
foreach (var invoice in account.MonthlyDetails[monthIndex + 1].Invoices)
{
foreach (var invoiceAccount in invoice.AccountTotals)
{
row = accountSheet.GetRow(rowNumber) ?? accountSheet.CreateRow(rowNumber);
cell = row.CreateCell(0);
cell.SetCellValue(new AccountNumberTemplate(invoiceAccount.Account).stringifyAccountNumber());
cell.CellStyle = _borderStyle;
cell = row.CreateCell(1);
cell.SetCellValue(invoice.Vendor.Name);
cell.CellStyle = _borderStyle;
cell = row.CreateCell(2);
cell.SetCellValue(invoice.InvoiceDate);
cell.CellStyle = _dateStyle;
cell = row.CreateCell(3);
cell.SetCellValue(invoice.DatePaid);
cell.CellStyle = _dateStyle;
cell = row.CreateCell(4);
cell.SetCellValue(invoice.InvoiceNumber);
cell.CellStyle = _borderStyle;
cell = row.CreateCell(5);
cell.SetCellValue(invoice.Description);
cell.CellStyle = _borderStyle;
cell = row.CreateCell(6);
cell.SetCellValue(System.Convert.ToDouble(invoiceAccount.Expense));
cell.CellStyle = _moneyStyle;
rowNumber++;
}
}
var usedRows = rowNumber - startRow;
while (usedRows < minRows)
{
row = accountSheet.GetRow(rowNumber) ?? accountSheet.CreateRow(rowNumber);
cell = row.CreateCell(0);
cell.CellStyle = _borderStyle;
cell = row.CreateCell(1);
cell.CellStyle = _borderStyle;
cell = row.CreateCell(2);
cell.CellStyle = _dateStyle;
cell = row.CreateCell(3);
cell.CellStyle = _dateStyle;
cell = row.CreateCell(4);
cell.CellStyle = _borderStyle;
cell = row.CreateCell(5);
cell.CellStyle = _borderStyle;
cell = row.CreateCell(6);
cell.CellStyle = _moneyStyle;
rowNumber++;
usedRows++;
}
row = accountSheet.CreateRow(rowNumber);
cell = row.CreateCell(0);
cell.SetCellValue($"{monthName} Total");
cell.CellStyle = _monthlyTotalStyle;
cell = row.CreateCell(1);
cell.CellStyle = _monthlyTotalStyle;
cell = row.CreateCell(2);
cell.CellStyle = _monthlyTotalStyle;
cell = row.CreateCell(3);
cell.CellStyle = _monthlyTotalStyle;
cell = row.CreateCell(4);
cell.CellStyle = _monthlyTotalStyle;
cell = row.CreateCell(5);
cell.CellStyle = _monthlyTotalStyle;
cell = row.CreateCell(6);
cell.CellFormula = $"SUM(G{startRow+2}:G{rowNumber})";
cell.CellStyle = _monthlyTotalEndStyle;
rowNumber++;
return(rowNumber);
}
private void WriteToExcel(DirectoryOperation operation)
{
try
{
string targetToOutput = ConfigurationManager.AppSettings["TargetToOutput"].ToString();
var workbook = new XSSFWorkbook();
if (File.Exists(targetToOutput))
{
this.fileStream = new FileStream(targetToOutput, FileMode.Open /*, FileAccess.ReadWrite, FileShare.ReadWrite*/);
workbook = new XSSFWorkbook(this.fileStream);
this.fileStream.Close();
}
else
{
this.fileStream = new FileStream(targetToOutput, FileMode.Create);
this.fileStream.Close();
workbook = new XSSFWorkbook();
}
//this.fileStream = new FileStream("C:\\Users\\GOOD\\Desktop\\test.xls", FileMode.Create, FileAccess.ReadWrite);
ISheet sheet = null;
bool notContain = true;
if (workbook.NumberOfSheets != 0)
{
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
if (workbook.GetSheetName(i).Equals($"{operation.Method.Name}"))
{
//workbook.RemoveSheetAt(i);
notContain = false;
sheet = workbook.GetSheet($"{operation.Method.Name}");
break;
}
else
{
notContain = true;
}
}
}
if (File.Exists(targetToOutput))
{
this.fileStream = new FileStream(targetToOutput, FileMode.Truncate /*, FileAccess.ReadWrite*/);
}
if (notContain)
{
sheet = workbook.CreateSheet($"{operation.Method.Name}");
var row = sheet.CreateRow(0);
var row2 = sheet.CreateRow(1);
var row3 = sheet.CreateRow(2);
var cellStyleBorder = workbook.CreateCellStyle();
cellStyleBorder.BorderBottom = BorderStyle.Thin;
cellStyleBorder.BorderLeft = BorderStyle.Thin;
cellStyleBorder.BorderRight = BorderStyle.Thin;
cellStyleBorder.BorderTop = BorderStyle.Thin;
cellStyleBorder.Alignment = HorizontalAlignment.Center;
cellStyleBorder.VerticalAlignment = VerticalAlignment.Center;
var cellStyleBorderAndColorGreen = workbook.CreateCellStyle();
cellStyleBorderAndColorGreen.CloneStyleFrom(cellStyleBorder);
cellStyleBorderAndColorGreen.FillPattern = FillPattern.SolidForeground;
((XSSFCellStyle)cellStyleBorderAndColorGreen).SetFillForegroundColor(new XSSFColor(new byte[] { 198, 239, 206 }));
var cellStyleBorderAndColorYellow = workbook.CreateCellStyle();
cellStyleBorderAndColorYellow.CloneStyleFrom(cellStyleBorder);
cellStyleBorderAndColorYellow.FillPattern = FillPattern.SolidForeground;
((XSSFCellStyle)cellStyleBorderAndColorYellow).SetFillForegroundColor(new XSSFColor(new byte[] { 255, 235, 156 }));
row.CreateCell(0);
row.CreateCell(1);
row2.CreateCell(0);
row2.CreateCell(1);
row3.CreateCell(0, CellType.String).SetCellValue("FullPath");
row3.Cells[0].CellStyle = cellStyleBorderAndColorYellow;
row3.CreateCell(1, CellType.String).SetCellValue("FileName");
row3.Cells[1].CellStyle = cellStyleBorderAndColorYellow;
var cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 1);
var cra2 = new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 1);
sheet.AddMergedRegion(cra);
sheet.AddMergedRegion(cra2);
ICell cell = sheet.GetRow(0).GetCell(0);
cell.SetCellType(CellType.String);
cell.SetCellValue($"{operation.Method.Name}");
cell.CellStyle = cellStyleBorderAndColorGreen;
sheet.GetRow(0).GetCell(1).CellStyle = cellStyleBorderAndColorGreen;
ICell cell2 = sheet.GetRow(1).GetCell(0);
cell2.SetCellType(CellType.String);
cell2.SetCellValue($"Count files in ExpectSet {this.ShowerDerectoryInstance.SymmetricalDifference().Count}");
cell2.CellStyle = cellStyleBorderAndColorGreen;
sheet.GetRow(1).GetCell(1).CellStyle = cellStyleBorderAndColorGreen;
int x = 3;
foreach (FileInfo info in this.ShowerDerectoryInstance.SymmetricalDifference())
{
row = sheet.CreateRow(x);
row.CreateCell(0).SetCellValue(info.FullName);
row.CreateCell(1).SetCellValue(info.Name);
x++;
}
}
else
{
var row = sheet.GetRow(0) == null?sheet.CreateRow(0) : sheet.GetRow(0);
var row2 = sheet.GetRow(1) == null?sheet.CreateRow(1) : sheet.GetRow(1);
var row3 = sheet.GetRow(2) == null?sheet.CreateRow(2) : sheet.GetRow(2);
var row3Cell0 = row3.GetCell(0) == null?row3.CreateCell(0) : row3.GetCell(0);
var row3Cell1 = row3.GetCell(1) == null?row3.CreateCell(1) : row3.GetCell(1);
row3Cell0.SetCellValue("FilePath");
row3Cell1.SetCellValue("FileName");
ICell cell = sheet.GetRow(0).GetCell(0);
var row0Cell0 = row.GetCell(0) == null?row.CreateCell(0) : row.GetCell(0);
row0Cell0.SetCellValue($"{operation.Method.Name}");
var row2Cell0 = row2.GetCell(0) == null?row2.CreateCell(0) : row2.GetCell(0);
row2Cell0.SetCellValue($"Count files in ExpectSet {this.ShowerDerectoryInstance.SymmetricalDifference().Count}");
int x = 3;
foreach (FileInfo info in this.ShowerDerectoryInstance.SymmetricalDifference())
{
if (sheet.GetRow(x) == null)
{
row = sheet.CreateRow(x);
}
else
{
row = sheet.GetRow(x);
}
if (row.GetCell(0) != null)
{
row.GetCell(0).SetCellValue(info.FullName);
}
else
{
row.CreateCell(0).SetCellValue(info.FullName);
}
if (row.GetCell(1) != null)
{
row.GetCell(1).SetCellValue(info.Name);
}
else
{
row.CreateCell(1).SetCellValue(info.Name);
}
x++;
}
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
workbook.Write(this.fileStream);
}
catch (ConfigurationErrorsException e)
{
throw e;
}
catch (NullReferenceException e)
{
throw e;
}
catch (ArgumentException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
finally
{
if (this.fileStream != null)
{
this.fileStream.Close();
}
}
}
private void generate_my_data()
{
ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");
DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
IRow row0 = sheet0.CreateRow(0);
table.TableName = "Załatwienia";
table.Columns.Remove("id_");
row0.CreateCell(0).SetCellValue("Opis");
row0.CreateCell(1).SetCellValue("Sprawy według repetoriów i wykazów");
var crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet0.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 8);
sheet0.AddMergedRegion(crs);
row0 = sheet0.CreateRow(1);
row0.CreateCell(1).SetCellValue("C");
row0.CreateCell(2).SetCellValue("CG-G");
row0.CreateCell(3).SetCellValue("Ns");
row0.CreateCell(4).SetCellValue("Nc");
row0.CreateCell(5).SetCellValue("Co");
row0.CreateCell(6).SetCellValue("Cps");
row0.CreateCell(7).SetCellValue("WSC");
row0.CreateCell(8).SetCellValue("Łącznie");
int rol = 2;
foreach (DataRow rowik in table.Rows)
{
row0 = sheet0.CreateRow(rol);
for (int i = 0; i < 9; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row0.CreateCell(i).SetCellValue(ji);
row0.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
// druga tabela
view = (DataView)statystyki.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table = view.ToTable();
table.TableName = "Załatwienia";
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
//table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
//
//robienie
int ro = 2;
//-----------------
IDataFormat format = hssfworkbook.CreateDataFormat();
//-----------------
ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
IRow row2 = sheet1.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet1.AddMergedRegion(crs);
row2.CreateCell(1).SetCellValue("Nazwisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet1.AddMergedRegion(crs);
row2.CreateCell(2).SetCellValue("Imię");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet1.AddMergedRegion(crs);
row2.CreateCell(3).SetCellValue("Funkcja");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet1.AddMergedRegion(crs);
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet1.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Liczba sesji");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 6);
sheet1.AddMergedRegion(crs);
row2.CreateCell(7).SetCellValue("Załatwienia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 7, 14);
sheet1.AddMergedRegion(crs);
row2.CreateCell(15).SetCellValue("Il. sporządzonych uzasadnień");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 15, 15);
sheet1.AddMergedRegion(crs);
row2.CreateCell(16).SetCellValue("Nieobecności");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 16, 17);
sheet1.AddMergedRegion(crs);
row2 = sheet1.CreateRow(1);
row2.CreateCell(5).SetCellValue("rozprawy");
row2.CreateCell(6).SetCellValue("posiedzenia");
row2.CreateCell(7).SetCellValue("C");
row2.CreateCell(8).SetCellValue("C-GC");
row2.CreateCell(9).SetCellValue("Ns");
row2.CreateCell(10).SetCellValue("Nc");
row2.CreateCell(11).SetCellValue("Co");
row2.CreateCell(12).SetCellValue("Cps");
row2.CreateCell(13).SetCellValue("WSC");
row2.CreateCell(14).SetCellValue("Razem");
row2.CreateCell(16).SetCellValue("Urlopy");
row2.CreateCell(17).SetCellValue("Zwolnienia");
foreach (DataRow rowik in table.Rows)
{
row2 = sheet1.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// trzeci sheet
view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
sheet1.AutoSizeColumn(0, true);
sheet1.AutoSizeColumn(1, true);
ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");
row2 = sheet2.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet2.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Wyznaczenia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet2.AddMergedRegion(crs);
row2 = sheet2.CreateRow(1);
row2.CreateCell(5).SetCellValue("C");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("Ns");
row2.CreateCell(8).SetCellValue("Nc");
row2.CreateCell(9).SetCellValue("Co");
row2.CreateCell(10).SetCellValue("Cps");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet2.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// czwarty sheet
view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
// table.Columns.Remove("id_tabeli");
// table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_09");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");
row2 = sheet3.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet3.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet3.AddMergedRegion(crs);
row2 = sheet3.CreateRow(1);
row2.CreateCell(5).SetCellValue("C");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("Ns");
row2.CreateCell(8).SetCellValue("Nc");
row2.CreateCell(9).SetCellValue("Co");
row2.CreateCell(10).SetCellValue("Cps");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
// row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet3.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
}
private void btnsave_Click(object sender, EventArgs e)
{
try
{
path = Environment.CurrentDirectory.ToString() + @"\Excel\" + "\\";
IWorkbook workbook = new XSSFWorkbook(); //建立空白工作簿
ISheet sheet = workbook.CreateSheet("扫描数据"); //在工作簿中：建立空白工作表
IRow row = sheet.CreateRow(0); //在工作表中：建立行，参数为行号，从0计
ICell cell = row.CreateCell(0); //在行中：建立单元格，参数为列号，从0计
cell.SetCellValue("小型化激光雷达扫描数据"); //设置单元格内容
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //设置单元格的样式：水平对齐居中
IFont font = workbook.CreateFont(); //新建一个字体样式对象
//font.Boldweight = short.MaxValue; //设置字体加粗样式
font.FontName = "宋体";
font.FontHeight = 16;
style.SetFont(font); //使用SetFont方法将字体样式添加到单元格样式中
cell.CellStyle = style; //将新的样式赋给单元格
row.Height = 30 * 20; //设置单元格的高度
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15); //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
sheet.AddMergedRegion(region);
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("实际值");
row1.CreateCell(1).SetCellValue("最大值");
row1.CreateCell(2).SetCellValue("最小值");
row1.CreateCell(3).SetCellValue("平均值");
row1.CreateCell(4).SetCellValue(" ");
row1.CreateCell(5).SetCellValue("最大偏差");
row1.CreateCell(6).SetCellValue("最小偏差");
row1.CreateCell(7).SetCellValue("平均值偏差");
row1.CreateCell(8).SetCellValue("离散度");
row1.CreateCell(9).SetCellValue(" ");
row1.CreateCell(10).SetCellValue("绝对最大偏差");
row1.CreateCell(11).SetCellValue("绝对最小偏差");
row1.CreateCell(12).SetCellValue("绝对平均偏差");
row1.CreateCell(13).SetCellValue("偏移量");
row1.CreateCell(14).SetCellValue("绝对精度");
//设置单元格的宽度
for (int i = 0; i < 15; i++)
{
sheet.SetColumnWidth(i, 13 * 256);//设置单元格的宽度
//row1.GetCell(i).CellStyle = style;//设置单元格格式
}
int len = Recordlist.Count;
//int l_n32criticalno = 0;
int temp = 0;
if (len > 0)
{
//for (int i = 0; i < len; i++) //查找临界值
//{
// if (((RecordStruct)Recordlist[temp]).m_int32realdisc > 500 &&
// ((RecordStruct)Recordlist[temp]).m_int32realdisc < 1500)
// {
// l_n32criticalno = i;
// break;
// }
//}
for (int i = 0; i < len; i++)
{
IRow rowx = sheet.CreateRow(i + 2);
//if (i % 2 == 0)
//{
// temp = (i / 2 + 10); //temp = (i / 2 + l_n32criticalno+1);
//}
//else
//{
// temp = (i - 1) / 2; //temp = (l_n32criticalno - (i - 1) / 2);
//}
temp = i;
rowx.CreateCell(0).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32realdisc);
rowx.CreateCell(1).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32max);
rowx.CreateCell(2).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32min);
rowx.CreateCell(3).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32ave);
rowx.CreateCell(4);
rowx.CreateCell(5);
rowx.CreateCell(6);
rowx.CreateCell(7);
rowx.CreateCell(8);
rowx.CreateCell(9);
rowx.CreateCell(10); //绝对最大偏差
rowx.CreateCell(11); //绝对最小偏差
rowx.CreateCell(12); //绝对平均偏差
rowx.CreateCell(13); //偏移量
rowx.CreateCell(14); //绝对精度
rowx.CreateCell(15); //绝对精度
}
//设置保留小数点的个数
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
IRow row2 = sheet.GetRow(2);
ICell cellx13 = row2.GetCell(13);
cellx13.SetCellFormula("AVERAGE(H3:H" + (len + 2).ToString() + ")"); //偏移量：平均偏差的平均值
cellx13.CellStyle = cellStyle;
if (len > 0)
{
for (int i = 0; i < len; i++)
{
IRow rowx = sheet.GetRow(i + 2);
string rowno = (i + 3).ToString();
ICell cellx5 = rowx.GetCell(5);
cellx5.SetCellFormula("SUM(B" + rowno + ",A" + rowno + "*(-1))"); //最大偏差： 最大值减实际值
ICell cellx6 = rowx.GetCell(6);
cellx6.SetCellFormula("SUM(C" + rowno + ",A" + rowno + "*(-1))"); //最小偏差：最小值减实际值
ICell cellx7 = rowx.GetCell(7);
cellx7.SetCellFormula("SUM(D" + rowno + ",A" + rowno + "*(-1))"); //平均偏差： 平均值减实际值
ICell cellx8 = rowx.GetCell(8);
cellx8.SetCellFormula("SUM(B" + rowno + ",C" + rowno + "*(-1))"); //离散度：最大值减最小值
ICell cellx10 = rowx.GetCell(10);
cellx10.SetCellFormula("SUM(F" + rowno + ",N3*(-1))"); //绝对最大偏差：最大偏差减去偏移量
cellx10.CellStyle = cellStyle;
ICell cellx11 = rowx.GetCell(11);
cellx11.SetCellFormula("SUM(G" + rowno + ",N3*(-1))"); //绝对最小偏差：最小偏差减去偏移量
cellx11.CellStyle = cellStyle;
ICell cellx12 = rowx.GetCell(12);
cellx12.SetCellFormula("SUM(H" + rowno + ",N3*(-1))"); //绝对平均偏差：平均偏差减去偏移量
cellx12.CellStyle = cellStyle;
}
}
//ICell cellx14 = row2.GetCell(14);
//cellx14.SetCellFormula("SUM(MAX(F3:F"+(len+2).ToString()+"),MIN(G3:G"+(len+2).ToString()+")*(-1))"); //绝对精度：最大偏差的最大值减去最小偏差的最小值
IRow row3 = sheet.GetRow(3);
ICell cell314 = row3.GetCell(14);
cell314.SetCellValue("0.5-6m"); //0.5-6
ICell cell315 = row3.GetCell(15);
cell315.SetCellFormula("SUM(MAX(F4:F14),MIN(G4:G14)*(-1))"); //0.5-6 绝对精度：最大偏差的最大值减去最小偏差的最小值
IRow row4 = sheet.GetRow(4);
ICell cell414 = row4.GetCell(14);
cell414.SetCellValue("0.5-8m"); //0.5-8
ICell cell415 = row4.GetCell(15);
cell415.SetCellFormula("SUM(MAX(F4:F18),MIN(G4:G18)*(-1))"); //0.5-8绝对精度：最大偏差的最大值减去最小偏差的最小值
IRow row5 = sheet.GetRow(5);
ICell cell514 = row5.GetCell(14);
cell514.SetCellValue("0-10m"); //0.5-10
ICell cell515 = row5.GetCell(15);
cell515.SetCellFormula("SUM(MAX(F3:F" + (len + 2).ToString() + "),MIN(G3:G" + (len + 2).ToString() + ")*(-1))"); //绝对精度：最大偏差的最大值减去最小偏差的最小值
if (ExcelFormName == null || ExcelFormName.IsDisposed)
{
ExcelFormName = new ExcelDocumentName();
}
ExcelFormName.ShowDialog(this);
ExcelName = ExcelFormName.name;
if (ExcelName != null)
{
path = path + ExcelName + ".xlsx";
FileStream file2007 = new FileStream(path, FileMode.Create);
workbook.Write(file2007);
file2007.Close();
workbook.Close();
MessageBox.Show("数据保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//加载excel
System.Diagnostics.Process.Start(path);
}
}
else
{
MessageBox.Show("记录数据为空，请重新记录！", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch
{
MessageBox.Show("保存数据出错，请重新操作", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
