问:
How to convert .xls to .xlsx C# (.net core)
Good evening, recently i was looking for a free way of autonomous convertion from .xls to .xlsx without any limits, but, unfortunatly didnt find a suitable library to do that(found freespire.xls(which have limits), also easyXLS(but it has 600$ price).

Did anyone have this before?

How to convert old .xls to new one(.xlsx) that EPPlus can deal with?)

UPD: required a method which can work both at windows&linux

Tried this, according to tutorial on SpireXLS's website

```<code>// create a new excel document
Workbook book = new Workbook();

//load a document from file
book.LoadFromFile("test.xls");

//Save the file to the version you want
book.SaveToFile("test.xlsx", ExcelVersion.Version2007);
</code>```

Also looking for some solution from NPOI library, maybe it could convert .xls to .xlsx(now doing that)

答:
I made this class a few months ago for this purpose based on NPOI:

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System;
using NPOI.HSSF.Record;
using System.Diagnostics;

///
/// Allow to convert between xls and xlsx excel formats
///
public class ExcelFormatConverter
{
    private Dictionary<short, ICellStyle> styleDict = new Dictionary<short, ICellStyle>();
    private IWorkbook sWorkbook = null;
    private IWorkbook dWorkbook = null;

    ///
    /// Convert the source file to the format of the destination file
    ///
    /// <param name="sFileName">Source file to convert</param>
    /// <param name="dFileName">Destination file where the conversion is saved</param>
    public void ConvertFile(string sFileName, string dFileName)
    {
        styleDict.Clear();

        IWorkbook sWorkbook;
        using (FileStream sFileStream = new FileStream(sFileName, FileMode.Open, FileAccess.Read))
        {
            sWorkbook = WorkbookFactory.Create(sFileStream, ImportOption.All);
        }

        IWorkbook dWorkbook = null;

        string ext = Path.GetExtension(dFileName).ToLower().Replace(".", "");

        if (ext.Equals("xlsx"))
            dWorkbook = new XSSFWorkbook();
        else if (ext.Equals("xls"))
            dWorkbook = new HSSFWorkbook();
        else
            throw new Exception("Not Supported Format");

        this.sWorkbook = sWorkbook;
        this.dWorkbook = dWorkbook;

        if (sWorkbook is HSSFWorkbook)
        {
            try { HSSFOptimiser.OptimiseCellStyles((HSSFWorkbook)sWorkbook); }
            catch { }
        }

        for (short s = 0; s < sWorkbook.NumCellStyles; s++)
        {
            ICellStyle sCellStyle = sWorkbook.GetCellStyleAt(s);
            ICellStyle dCellStyle = dWorkbook.CreateCellStyle();

            //DebugCellStyle(Path.GetDirectoryName(sFileName) + @"\debug", sCellStyle);

            ConvertCellStyle(sCellStyle, dCellStyle);
        }

        if(dWorkbook is HSSFWorkbook)
        {
            try { HSSFOptimiser.OptimiseCellStyles((HSSFWorkbook)dWorkbook); }
            catch { }
        }

        ConvertWorkBook(sWorkbook, dWorkbook);
        using (FileStream dFileStream = new FileStream(dFileName, FileMode.CreateNew, FileAccess.ReadWrite))
        {
            dWorkbook.Write(dFileStream);
        }

        this.dWorkbook = null;
        this.sWorkbook = null;

    }

    ///
    /// Used to debug
    ///
    /// <param name="fileName"></param>
    /// <param name="cellStyle"></param>
    private void DebugCellStyle(string fileName, ICellStyle cellStyle)
    {
        string text = "---------------------------------------------------------------------\r\n";
        text += "Alignment : " + cellStyle.Alignment.ToString() + "\r\n";
        text += "BorderBottom : " + cellStyle.BorderBottom.ToString() + "\r\n";
        text += "BorderDiagonal : " + cellStyle.BorderDiagonal.ToString() + "\r\n";
        text += "BorderDiagonalColor : " + cellStyle.BorderDiagonalColor.ToString() + "\r\n";
        text += "BorderDiagonalLineStyle : " + cellStyle.BorderDiagonalLineStyle.ToString() + "\r\n";
        text += "BorderLeft : " + cellStyle.BorderLeft.ToString() + "\r\n";
        text += "BorderRight : " + cellStyle.BorderRight.ToString() + "\r\n";
        text += "BorderTop : " + cellStyle.BorderTop.ToString() + "\r\n";
        text += "BottomBorderColor : " + cellStyle.BottomBorderColor.ToString() + "\r\n";
        text += "DataFormat : " + cellStyle.DataFormat.ToString() + "\r\n";
        text += "FillBackgroundColor : " + cellStyle.FillBackgroundColor.ToString() + "\r\n";
        text += "FillBackgroundColorColor : " + (cellStyle.FillBackgroundColorColor == null ? "null" : cellStyle.FillBackgroundColorColor.ToString()) + "\r\n";
        text += "FillForegroundColor : " + cellStyle.FillForegroundColor.ToString() + "\r\n";
        text += "FillForegroundColorColor : " + (cellStyle.FillForegroundColorColor == null ? "null" : cellStyle.FillForegroundColorColor.ToString()) + "\r\n";
        text += "FillPattern : " + cellStyle.FillPattern.ToString() + "\r\n";
        text += "FontIndex : " + cellStyle.FontIndex.ToString() + "\r\n";
        text += "Indention : " + cellStyle.Indention.ToString() + "\r\n";
        text += "Index : " + cellStyle.Index.ToString() + "\r\n";
        text += "IsHidden : " + cellStyle.IsHidden.ToString() + "\r\n";
        text += "IsLocked : " + cellStyle.IsLocked.ToString() + "\r\n";
        text += "LeftBorderColor : " + cellStyle.LeftBorderColor.ToString() + "\r\n";
        text += "RightBorderColor : " + cellStyle.RightBorderColor.ToString() + "\r\n";
        text += "Rotation : " + cellStyle.Rotation.ToString() + "\r\n";
        text += "ShrinkToFit : " + (cellStyle is XSSFCellStyle ? "Not implemented" : cellStyle.ShrinkToFit.ToString()) + "\r\n";
        text += "TopBorderColor : " + cellStyle.TopBorderColor.ToString() + "\r\n";
        text += "VerticalAlignment : " + cellStyle.VerticalAlignment.ToString() + "\r\n";
        text += "WrapText : " + cellStyle.WrapText.ToString() + "\r\n";

        File.AppendAllText(fileName, text);
    }

    private void ConvertCellStyle(ICellStyle sCellStyle, ICellStyle dCellStyle)
    {
        try { dCellStyle.Alignment = sCellStyle.Alignment; }
        catch { }
        try { dCellStyle.BorderBottom = sCellStyle.BorderBottom; }
        catch { }
        try { dCellStyle.BorderDiagonal = sCellStyle.BorderDiagonal; }
        catch { }
        try { dCellStyle.BorderDiagonalColor = sCellStyle.BorderDiagonalColor; }
        catch { }
        try { dCellStyle.BorderDiagonalLineStyle = sCellStyle.BorderDiagonalLineStyle; }
        catch { }
        try { dCellStyle.BorderLeft = sCellStyle.BorderLeft; }
        catch { }
        try { dCellStyle.BorderRight = sCellStyle.BorderRight; }
        catch { }
        try { dCellStyle.BorderTop = sCellStyle.BorderTop; }
        catch { }
        try { dCellStyle.BottomBorderColor = sCellStyle.BottomBorderColor; }
        catch { }
        try { dCellStyle.DataFormat = ConvertFormat(sCellStyle.DataFormat); }
        catch { }
        try { dCellStyle.FillBackgroundColor = sCellStyle.FillBackgroundColor; }
        catch { }
        try { dCellStyle.FillForegroundColor = sCellStyle.FillForegroundColor; }
        catch { }
        try { dCellStyle.FillPattern = sCellStyle.FillPattern; }
        catch { }
        try { dCellStyle.SetFont(ConvertFont(sCellStyle.GetFont(this.sWorkbook))); }
        catch { }
        try { dCellStyle.Indention = sCellStyle.Indention; }
        catch { }
        try { dCellStyle.IsHidden = sCellStyle.IsHidden; }
        catch { }
        try { dCellStyle.IsLocked = sCellStyle.IsLocked; }
        catch { }
        try { dCellStyle.LeftBorderColor = sCellStyle.LeftBorderColor; }
        catch { }
        try { dCellStyle.RightBorderColor = sCellStyle.RightBorderColor; }
        catch { }
        try { dCellStyle.Rotation = sCellStyle.Rotation; }
        catch { }
        try { dCellStyle.TopBorderColor = sCellStyle.TopBorderColor; }
        catch { }
        try { dCellStyle.VerticalAlignment = sCellStyle.VerticalAlignment; }
        catch { }
        try { dCellStyle.WrapText = sCellStyle.WrapText; }
        catch { }
    }

    private short ConvertFormat(short index)
    {
        IDataFormat sFormat = this.sWorkbook.CreateDataFormat();
        IDataFormat dFormat = this.dWorkbook.CreateDataFormat();
        return dFormat.GetFormat(sFormat.GetFormat(index));
    }

    private IFont ConvertFont(IFont sFont)
    {
        IFont dFont = this.dWorkbook.CreateFont();
        try { dFont.Boldweight = sFont.Boldweight; }
        catch { }
        try { dFont.Charset = sFont.Charset; }
        catch { }
        try { dFont.FontName = sFont.FontName; }
        catch { }
        try { dFont.FontHeightInPoints = sFont.FontHeightInPoints; }
        catch { }
        try { dFont.IsItalic = sFont.IsItalic; }
        catch { }
        try { dFont.IsStrikeout = sFont.IsStrikeout; }
        catch { }
        try { dFont.TypeOffset = sFont.TypeOffset; }
        catch { }

        try
        {

            byte[] rgb = null;

            if (sWorkbook is HSSFWorkbook)
            {
                if (((HSSFWorkbook)sWorkbook).GetCustomPalette().GetColor(sFont.Color) == null)
                {
                    rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                }
                else
                {
                    rgb = ((HSSFWorkbook)sWorkbook).GetCustomPalette().GetColor(sFont.Color).RGB;

                    if (dWorkbook is XSSFWorkbook)
                    {
                        if (rgb[0] == 0 && rgb[1] == 0 && rgb[2] == 0)
                            rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                        else if (rgb[0] == 255 && rgb[1] == 255 && rgb[2] == 255)
                            rgb = new byte[] { (byte)0, (byte)0, (byte)0 };
                    }
                }

            }
            else if (sWorkbook is XSSFWorkbook)
            {
                if (sFont == null)
                    rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                else
                {
                    XSSFColor xColor = ((XSSFFont)sFont).GetXSSFColor();

                    if (xColor == null)
                        rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                    else
                        rgb = xColor.GetRgb();
                }
            }

            if (dWorkbook is HSSFWorkbook)
            {
                // Bug in NPOI black and white colors are reversed
                if (sWorkbook is XSSFWorkbook)
                {
                    if (rgb[0] == 0 && rgb[1] == 0 && rgb[2] == 0)
                        rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                    else if (rgb[0] == 255 && rgb[1] == 255 && rgb[2] == 255)
                        rgb = new byte[] { (byte)0, (byte)0, (byte)0 };
                }

                dFont.Underline = sFont.Underline;
                HSSFColor color = ((HSSFWorkbook)dWorkbook).GetCustomPalette().FindColor(rgb[0], rgb[1], rgb[2]);

                if (color == null)
                    color = ((HSSFWorkbook)sWorkbook).GetCustomPalette().AddColor(rgb[0], rgb[1], rgb[2]);

                dFont.Color = color.Indexed;
            }
            else if (dWorkbook is XSSFWorkbook)
            {
                // Conditional Underline because of a bug in NPOI
                if (sFont.Underline != FontUnderlineType.None)
                    ((XSSFFont)dFont).Underline = sFont.Underline;

                ((XSSFFont)dFont).SetColor(new XSSFColor(rgb));
            }
        }
        catch { }

        return dFont;
    }

    ///
    /// Convert the source work book to the format of the destination workbook
    ///
    /// <param name="sWorkbook">workbook source</param>
    /// <param name="dWorkbook">workbook destination</param>
    public void ConvertWorkBook(IWorkbook sWorkbook, IWorkbook dWorkbook)
    {
        try { dWorkbook.MissingCellPolicy = sWorkbook.MissingCellPolicy; }
        catch { }

        for (int i = 0; i < sWorkbook.NumberOfSheets; i++)
        {
            ISheet sSheet = sWorkbook.GetSheetAt(i);
            ISheet dSheet = dWorkbook.CreateSheet(sSheet.SheetName);

            try { dSheet.ForceFormulaRecalculation = sSheet.ForceFormulaRecalculation; }
            catch { }

            ConvertSheet(sSheet, dSheet);

            SheetState state = SheetState.Visible;

            try
            {
                if (sWorkbook.IsSheetHidden(i))
                    state = SheetState.Hidden;
                if (sWorkbook.IsSheetVeryHidden(i))
                    state = SheetState.VeryHidden;

                dWorkbook.SetSheetHidden(i, state);
            }
            catch { }
        }

        try { dWorkbook.SetActiveSheet(sWorkbook.ActiveSheetIndex); }
        catch { }
    }

    protected void ConvertSheet(ISheet sSheet, ISheet dSheet)
    {
        int numberOfColumns = 0;

        for(int i = sSheet.FirstRowNum; i <= sSheet.LastRowNum; i++)
        {
            try { ConvertRow(sSheet.GetRow(i), dSheet.CreateRow(i)); }
            catch { }

            try
            {
                if (sSheet.GetRow(i) != null && numberOfColumns < sSheet.GetRow(i).LastCellNum)
                    numberOfColumns = sSheet.GetRow(i).LastCellNum;
            }
            catch { }
        }

        for(int i = 1; i <= numberOfColumns;i++)
        {
            try { dSheet.SetColumnWidth(i, sSheet.GetColumnWidth(i)); }
            catch { }

            try
            {
                if (sSheet.IsColumnHidden(i))
                    dSheet.SetColumnHidden(i, true);
            }
            catch { }
        }

        for (int i = 0; i < sSheet.NumMergedRegions; i++)
        {
            try { dSheet.AddMergedRegion(sSheet.GetMergedRegion(i)); }
            catch { }
        }

        try { dSheet.DisplayFormulas = sSheet.DisplayFormulas; }
        catch { }
        try { dSheet.DisplayGridlines = sSheet.DisplayGridlines; }
        catch { }
        try { dSheet.DisplayGuts = sSheet.DisplayGuts; }
        catch { }
        try { dSheet.DisplayRowColHeadings = sSheet.DisplayRowColHeadings; }
        catch { }
        try { dSheet.DisplayZeros = sSheet.DisplayZeros; }
        catch { }
        try { dSheet.FitToPage = sSheet.FitToPage; }
        catch { }

        try { dSheet.HorizontallyCenter = sSheet.HorizontallyCenter; }
        catch { }

        try { dSheet.SetMargin(MarginType.BottomMargin, sSheet.GetMargin(MarginType.BottomMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.FooterMargin, sSheet.GetMargin(MarginType.FooterMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.HeaderMargin, sSheet.GetMargin(MarginType.HeaderMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.LeftMargin, sSheet.GetMargin(MarginType.LeftMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.RightMargin, sSheet.GetMargin(MarginType.RightMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.TopMargin, sSheet.GetMargin(MarginType.TopMargin)); }
        catch { }

    }

    protected void ConvertRow(IRow sRow, IRow dRow)
    {
        if (sRow != null && dRow != null)
        {
            try
            {
                if (sRow.RowStyle != null)
                    dRow.RowStyle = styleDict[sRow.RowStyle.Index];
            }
            catch { }

            if (sRow.FirstCellNum >= 0)
            {
                for (int i = sRow.FirstCellNum; i <= sRow.LastCellNum; i++)
                {
                    if (sRow.GetCell(i) != null)
                    {
                        try { ConvertCell(sRow.GetCell(i), dRow.CreateCell(i)); }
                        catch { }
                        try { dRow.Height = sRow.Height; }
                        catch { }
                    }
                }
            }
        }
    }

    protected void ConvertCell(ICell sCell, ICell dCell)
    {
        if (sCell != null && dCell != null)
        {
            try { dCell.CellComment = sCell.CellComment; }
            catch { }

            try
            {
                if (sCell.CellStyle != null)
                    dCell.CellStyle = styleDict[sCell.CellStyle.Index];
            }
            catch { }

            switch (sCell.CellType)
            {
                case CellType.String:
                    try { dCell.SetCellValue(sCell.StringCellValue); }
                    catch { }
                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(sCell))
                    {
                        try { dCell.SetCellValue(sCell.DateCellValue); }
                        catch { }

                        //short format = dWorkbook.CreateDataFormat().GetFormat("MM-dd-yy HH:mm:ss.fff");

                        try { dCell.CellStyle.CloneStyleFrom(sCell.CellStyle); }
                        catch { }
                    }
                    else
                    {
                        try { dCell.SetCellValue(sCell.NumericCellValue); }
                        catch { }
                    }

                    break;
                case CellType.Boolean:
                    try { dCell.SetCellValue(sCell.BooleanCellValue); }
                    catch { }
                    break;
                case CellType.Error:
                    try { dCell.SetCellErrorValue(sCell.ErrorCellValue); }
                    catch { }
                    break;
                case CellType.Formula:
                    try { dCell.SetCellFormula(sCell.CellFormula); }
                    catch { }
                    break;
                default:
                    try { dCell.SetCellType(CellType.Blank); }
                    catch { }
                    break;
            }
        }
    }
}
```

The conversion can take time depending of the size of the excel file.
And it doesn't work for excel files with charts or macros.
But it's handy for styled datasheets. I made it based on NPOI version 2.3.0
I don't know if it works with newer version.

Feel free to use it or modify it.

Body:
Good evening, recently i was looking for a free way of autonomous convertion from .xls to .xlsx without any limits, but, unfortunatly didnt find a suitable library to do that(found freespire.xls(which have limits), also easyXLS(but it has 600$ price).

Did anyone have this before?

How to convert old .xls to new one(.xlsx) that EPPlus can deal with?)

UPD: required a method which can work both at windows&linux

Tried this, according to tutorial on SpireXLS's website

// create a new excel document
Workbook book = new Workbook();

//load a document from file
book.LoadFromFile("test.xls");

//Save the file to the version you want
book.SaveToFile("test.xlsx", ExcelVersion.Version2007);

Also looking for some solution from NPOI library, maybe it could convert .xls to .xlsx(now doing that)

Body:
I made this class a few months ago for this purpose based on NPOI:

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System;
using NPOI.HSSF.Record;
using System.Diagnostics;

/// <summary>
/// Allow to convert between xls and xlsx excel formats
/// </summary>
public class ExcelFormatConverter
{
    private Dictionary<short, ICellStyle> styleDict = new Dictionary<short, ICellStyle>();
    private IWorkbook sWorkbook = null;
    private IWorkbook dWorkbook = null;

    /// <summary>
    /// Convert the source file to the format of the destination file
    /// </summary>
    /// <param name="sFileName">Source file to convert</param>
    /// <param name="dFileName">Destination file where the conversion is saved</param>
    public void ConvertFile(string sFileName, string dFileName)
    {
        styleDict.Clear();

        IWorkbook sWorkbook;
        using (FileStream sFileStream = new FileStream(sFileName, FileMode.Open, FileAccess.Read))
        {
            sWorkbook = WorkbookFactory.Create(sFileStream, ImportOption.All);
        }

        IWorkbook dWorkbook = null;

        string ext = Path.GetExtension(dFileName).ToLower().Replace(".", "");

        if (ext.Equals("xlsx"))
            dWorkbook = new XSSFWorkbook();
        else if (ext.Equals("xls"))
            dWorkbook = new HSSFWorkbook();
        else
            throw new Exception("Not Supported Format");

        this.sWorkbook = sWorkbook;
        this.dWorkbook = dWorkbook;

        if (sWorkbook is HSSFWorkbook)
        {
            try { HSSFOptimiser.OptimiseCellStyles((HSSFWorkbook)sWorkbook); }
            catch { }
        }

        for (short s = 0; s < sWorkbook.NumCellStyles; s++)
        {
            ICellStyle sCellStyle = sWorkbook.GetCellStyleAt(s);
            ICellStyle dCellStyle = dWorkbook.CreateCellStyle();

            //DebugCellStyle(Path.GetDirectoryName(sFileName) + @"\debug", sCellStyle);

            ConvertCellStyle(sCellStyle, dCellStyle);
        }

        if(dWorkbook is HSSFWorkbook)
        {
            try { HSSFOptimiser.OptimiseCellStyles((HSSFWorkbook)dWorkbook); }
            catch { }
        }

        ConvertWorkBook(sWorkbook, dWorkbook);
        using (FileStream dFileStream = new FileStream(dFileName, FileMode.CreateNew, FileAccess.ReadWrite))
        {
            dWorkbook.Write(dFileStream);
        }

        this.dWorkbook = null;
        this.sWorkbook = null;

    }

    /// <summary>
    /// Used to debug
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="cellStyle"></param>
    private void DebugCellStyle(string fileName, ICellStyle cellStyle)
    {
        string text = "---------------------------------------------------------------------\r\n";
        text += "Alignment : " + cellStyle.Alignment.ToString() + "\r\n";
        text += "BorderBottom : " + cellStyle.BorderBottom.ToString() + "\r\n";
        text += "BorderDiagonal : " + cellStyle.BorderDiagonal.ToString() + "\r\n";
        text += "BorderDiagonalColor : " + cellStyle.BorderDiagonalColor.ToString() + "\r\n";
        text += "BorderDiagonalLineStyle : " + cellStyle.BorderDiagonalLineStyle.ToString() + "\r\n";
        text += "BorderLeft : " + cellStyle.BorderLeft.ToString() + "\r\n";
        text += "BorderRight : " + cellStyle.BorderRight.ToString() + "\r\n";
        text += "BorderTop : " + cellStyle.BorderTop.ToString() + "\r\n";
        text += "BottomBorderColor : " + cellStyle.BottomBorderColor.ToString() + "\r\n";
        text += "DataFormat : " + cellStyle.DataFormat.ToString() + "\r\n";
        text += "FillBackgroundColor : " + cellStyle.FillBackgroundColor.ToString() + "\r\n";
        text += "FillBackgroundColorColor : " + (cellStyle.FillBackgroundColorColor == null ? "null" : cellStyle.FillBackgroundColorColor.ToString()) + "\r\n";
        text += "FillForegroundColor : " + cellStyle.FillForegroundColor.ToString() + "\r\n";
        text += "FillForegroundColorColor : " + (cellStyle.FillForegroundColorColor == null ? "null" : cellStyle.FillForegroundColorColor.ToString()) + "\r\n";
        text += "FillPattern : " + cellStyle.FillPattern.ToString() + "\r\n";
        text += "FontIndex : " + cellStyle.FontIndex.ToString() + "\r\n";
        text += "Indention : " + cellStyle.Indention.ToString() + "\r\n";
        text += "Index : " + cellStyle.Index.ToString() + "\r\n";
        text += "IsHidden : " + cellStyle.IsHidden.ToString() + "\r\n";
        text += "IsLocked : " + cellStyle.IsLocked.ToString() + "\r\n";
        text += "LeftBorderColor : " + cellStyle.LeftBorderColor.ToString() + "\r\n";
        text += "RightBorderColor : " + cellStyle.RightBorderColor.ToString() + "\r\n";
        text += "Rotation : " + cellStyle.Rotation.ToString() + "\r\n";
        text += "ShrinkToFit : " + (cellStyle is XSSFCellStyle ? "Not implemented" : cellStyle.ShrinkToFit.ToString()) + "\r\n";
        text += "TopBorderColor : " + cellStyle.TopBorderColor.ToString() + "\r\n";
        text += "VerticalAlignment : " + cellStyle.VerticalAlignment.ToString() + "\r\n";
        text += "WrapText : " + cellStyle.WrapText.ToString() + "\r\n";

        File.AppendAllText(fileName, text);
    }

    private void ConvertCellStyle(ICellStyle sCellStyle, ICellStyle dCellStyle)
    {
        try { dCellStyle.Alignment = sCellStyle.Alignment; }
        catch { }
        try { dCellStyle.BorderBottom = sCellStyle.BorderBottom; }
        catch { }
        try { dCellStyle.BorderDiagonal = sCellStyle.BorderDiagonal; }
        catch { }
        try { dCellStyle.BorderDiagonalColor = sCellStyle.BorderDiagonalColor; }
        catch { }
        try { dCellStyle.BorderDiagonalLineStyle = sCellStyle.BorderDiagonalLineStyle; }
        catch { }
        try { dCellStyle.BorderLeft = sCellStyle.BorderLeft; }
        catch { }
        try { dCellStyle.BorderRight = sCellStyle.BorderRight; }
        catch { }
        try { dCellStyle.BorderTop = sCellStyle.BorderTop; }
        catch { }
        try { dCellStyle.BottomBorderColor = sCellStyle.BottomBorderColor; }
        catch { }
        try { dCellStyle.DataFormat = ConvertFormat(sCellStyle.DataFormat); }
        catch { }
        try { dCellStyle.FillBackgroundColor = sCellStyle.FillBackgroundColor; }
        catch { }
        try { dCellStyle.FillForegroundColor = sCellStyle.FillForegroundColor; }
        catch { }
        try { dCellStyle.FillPattern = sCellStyle.FillPattern; }
        catch { }
        try { dCellStyle.SetFont(ConvertFont(sCellStyle.GetFont(this.sWorkbook))); }
        catch { }
        try { dCellStyle.Indention = sCellStyle.Indention; }
        catch { }
        try { dCellStyle.IsHidden = sCellStyle.IsHidden; }
        catch { }
        try { dCellStyle.IsLocked = sCellStyle.IsLocked; }
        catch { }
        try { dCellStyle.LeftBorderColor = sCellStyle.LeftBorderColor; }
        catch { }
        try { dCellStyle.RightBorderColor = sCellStyle.RightBorderColor; }
        catch { }
        try { dCellStyle.Rotation = sCellStyle.Rotation; }
        catch { }
        try { dCellStyle.TopBorderColor = sCellStyle.TopBorderColor; }
        catch { }
        try { dCellStyle.VerticalAlignment = sCellStyle.VerticalAlignment; }
        catch { }
        try { dCellStyle.WrapText = sCellStyle.WrapText; }
        catch { }
    }

    private short ConvertFormat(short index)
    {
        IDataFormat sFormat = this.sWorkbook.CreateDataFormat();
        IDataFormat dFormat = this.dWorkbook.CreateDataFormat();
        return dFormat.GetFormat(sFormat.GetFormat(index));
    }

    private IFont ConvertFont(IFont sFont)
    {
        IFont dFont = this.dWorkbook.CreateFont();
        try { dFont.Boldweight = sFont.Boldweight; }
        catch { }
        try { dFont.Charset = sFont.Charset; }
        catch { }
        try { dFont.FontName = sFont.FontName; }
        catch { }
        try { dFont.FontHeightInPoints = sFont.FontHeightInPoints; }
        catch { }
        try { dFont.IsItalic = sFont.IsItalic; }
        catch { }
        try { dFont.IsStrikeout = sFont.IsStrikeout; }
        catch { }
        try { dFont.TypeOffset = sFont.TypeOffset; }
        catch { }

        try
        {

            byte[] rgb = null;

            if (sWorkbook is HSSFWorkbook)
            {
                if (((HSSFWorkbook)sWorkbook).GetCustomPalette().GetColor(sFont.Color) == null)
                {
                    rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                }
                else
                {
                    rgb = ((HSSFWorkbook)sWorkbook).GetCustomPalette().GetColor(sFont.Color).RGB;

                    if (dWorkbook is XSSFWorkbook)
                    {
                        if (rgb[0] == 0 && rgb[1] == 0 && rgb[2] == 0)
                            rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                        else if (rgb[0] == 255 && rgb[1] == 255 && rgb[2] == 255)
                            rgb = new byte[] { (byte)0, (byte)0, (byte)0 };
                    }
                }

            }
            else if (sWorkbook is XSSFWorkbook)
            {
                if (sFont == null)
                    rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                else
                {
                    XSSFColor xColor = ((XSSFFont)sFont).GetXSSFColor();

                    if (xColor == null)
                        rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                    else
                        rgb = xColor.GetRgb();
                }
            }

            if (dWorkbook is HSSFWorkbook)
            {
                // Bug in NPOI black and white colors are reversed
                if (sWorkbook is XSSFWorkbook)
                {
                    if (rgb[0] == 0 && rgb[1] == 0 && rgb[2] == 0)
                        rgb = new byte[] { (byte)255, (byte)255, (byte)255 };
                    else if (rgb[0] == 255 && rgb[1] == 255 && rgb[2] == 255)
                        rgb = new byte[] { (byte)0, (byte)0, (byte)0 };
                }

                dFont.Underline = sFont.Underline;
                HSSFColor color = ((HSSFWorkbook)dWorkbook).GetCustomPalette().FindColor(rgb[0], rgb[1], rgb[2]);

                if (color == null)
                    color = ((HSSFWorkbook)sWorkbook).GetCustomPalette().AddColor(rgb[0], rgb[1], rgb[2]);

                dFont.Color = color.Indexed;
            }
            else if (dWorkbook is XSSFWorkbook)
            {
                // Conditional Underline because of a bug in NPOI
                if (sFont.Underline != FontUnderlineType.None)
                    ((XSSFFont)dFont).Underline = sFont.Underline;

                ((XSSFFont)dFont).SetColor(new XSSFColor(rgb));
            }
        }
        catch { }

        return dFont;
    }

    /// <summary>
    /// Convert the source work book to the format of the destination workbook
    /// </summary>
    /// <param name="sWorkbook">workbook source</param>
    /// <param name="dWorkbook">workbook destination</param>
    public void ConvertWorkBook(IWorkbook sWorkbook, IWorkbook dWorkbook)
    {
        try { dWorkbook.MissingCellPolicy = sWorkbook.MissingCellPolicy; }
        catch { }

        for (int i = 0; i < sWorkbook.NumberOfSheets; i++)
        {
            ISheet sSheet = sWorkbook.GetSheetAt(i);
            ISheet dSheet = dWorkbook.CreateSheet(sSheet.SheetName);

            try { dSheet.ForceFormulaRecalculation = sSheet.ForceFormulaRecalculation; }
            catch { }

            ConvertSheet(sSheet, dSheet);

            SheetState state = SheetState.Visible;

            try
            {
                if (sWorkbook.IsSheetHidden(i))
                    state = SheetState.Hidden;
                if (sWorkbook.IsSheetVeryHidden(i))
                    state = SheetState.VeryHidden;

                dWorkbook.SetSheetHidden(i, state);
            }
            catch { }
        }

        try { dWorkbook.SetActiveSheet(sWorkbook.ActiveSheetIndex); }
        catch { }
    }

    protected void ConvertSheet(ISheet sSheet, ISheet dSheet)
    {
        int numberOfColumns = 0;

        for(int i = sSheet.FirstRowNum; i <= sSheet.LastRowNum; i++)
        {
            try { ConvertRow(sSheet.GetRow(i), dSheet.CreateRow(i)); }
            catch { }

            try
            {
                if (sSheet.GetRow(i) != null && numberOfColumns < sSheet.GetRow(i).LastCellNum)
                    numberOfColumns = sSheet.GetRow(i).LastCellNum;
            }
            catch { }
        }

        for(int i = 1; i <= numberOfColumns;i++)
        {
            try { dSheet.SetColumnWidth(i, sSheet.GetColumnWidth(i)); }
            catch { }

            try
            {
                if (sSheet.IsColumnHidden(i))
                    dSheet.SetColumnHidden(i, true);
            }
            catch { }
        }

        for (int i = 0; i < sSheet.NumMergedRegions; i++)
        {
            try { dSheet.AddMergedRegion(sSheet.GetMergedRegion(i)); }
            catch { }
        }

        try { dSheet.DisplayFormulas = sSheet.DisplayFormulas; }
        catch { }
        try { dSheet.DisplayGridlines = sSheet.DisplayGridlines; }
        catch { }
        try { dSheet.DisplayGuts = sSheet.DisplayGuts; }
        catch { }
        try { dSheet.DisplayRowColHeadings = sSheet.DisplayRowColHeadings; }
        catch { }
        try { dSheet.DisplayZeros = sSheet.DisplayZeros; }
        catch { }
        try { dSheet.FitToPage = sSheet.FitToPage; }
        catch { }

        try { dSheet.HorizontallyCenter = sSheet.HorizontallyCenter; }
        catch { }

        try { dSheet.SetMargin(MarginType.BottomMargin, sSheet.GetMargin(MarginType.BottomMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.FooterMargin, sSheet.GetMargin(MarginType.FooterMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.HeaderMargin, sSheet.GetMargin(MarginType.HeaderMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.LeftMargin, sSheet.GetMargin(MarginType.LeftMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.RightMargin, sSheet.GetMargin(MarginType.RightMargin)); }
        catch { }
        try { dSheet.SetMargin(MarginType.TopMargin, sSheet.GetMargin(MarginType.TopMargin)); }
        catch { }

    }

    protected void ConvertRow(IRow sRow, IRow dRow)
    {
        if (sRow != null && dRow != null)
        {
            try
            {
                if (sRow.RowStyle != null)
                    dRow.RowStyle = styleDict[sRow.RowStyle.Index];
            }
            catch { }

            if (sRow.FirstCellNum >= 0)
            {
                for (int i = sRow.FirstCellNum; i <= sRow.LastCellNum; i++)
                {
                    if (sRow.GetCell(i) != null)
                    {
                        try { ConvertCell(sRow.GetCell(i), dRow.CreateCell(i)); }
                        catch { }
                        try { dRow.Height = sRow.Height; }
                        catch { }
                    }
                }
            }
        }
    }

    protected void ConvertCell(ICell sCell, ICell dCell)
    {
        if (sCell != null && dCell != null)
        {
            try { dCell.CellComment = sCell.CellComment; }
            catch { }

            try
            {
                if (sCell.CellStyle != null)
                    dCell.CellStyle = styleDict[sCell.CellStyle.Index];
            }
            catch { }

            switch (sCell.CellType)
            {
                case CellType.String:
                    try { dCell.SetCellValue(sCell.StringCellValue); }
                    catch { }
                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(sCell))
                    {
                        try { dCell.SetCellValue(sCell.DateCellValue); }
                        catch { }

                        //short format = dWorkbook.CreateDataFormat().GetFormat("MM-dd-yy HH:mm:ss.fff");

                        try { dCell.CellStyle.CloneStyleFrom(sCell.CellStyle); }
                        catch { }
                    }
                    else
                    {
                        try { dCell.SetCellValue(sCell.NumericCellValue); }
                        catch { }
                    }

                    break;
                case CellType.Boolean:
                    try { dCell.SetCellValue(sCell.BooleanCellValue); }
                    catch { }
                    break;
                case CellType.Error:
                    try { dCell.SetCellErrorValue(sCell.ErrorCellValue); }
                    catch { }
                    break;
                case CellType.Formula:
                    try { dCell.SetCellFormula(sCell.CellFormula); }
                    catch { }
                    break;
                default:
                    try { dCell.SetCellType(CellType.Blank); }
                    catch { }
                    break;
            }
        }
    }
}

The conversion can take time depending of the size of the excel file.
And it doesn't work for excel files with charts or macros.
But it's handy for styled datasheets. I made it based on NPOI version 2.3.0
I don't know if it works with newer version.

Feel free to use it or modify it.


来源: stackexchange

答:
```<code>select sum(quantity * price),
       (select sum(fee)
        from (select distinct(id_test), fee
        from sales) as fees)
from sales;
</code>```

I created a SQLFiddle to demonstrate.

ContentLicense: CC BY-SA 4.0

Title: SQL Query SUM with conditions

Body:
I'm having some difficulties finding the good query I need, looking for help.
After multiple JOIN and a lot of WHERE, I have this columns and values :

+---------+----------+--------+-------+
| id_test | quantity | price  |  fee  |
+---------+----------+--------+-------+
| "1-3"   |        1 | 33.52  | 29.00 |
| "1-5"   |        1 | 33.52  | 29.00 |
| "1-6"   |        1 | 33.52  | 29.00 |
| "1-8"   |        1 | 86.49  | 29.00 |
| "19-1"  |        1 | 176.54 | 29.00 |
| "19-4"  |        1 | 176.54 | 29.00 |
| "19-5"  |        4 | 176.54 | 29.00 |
| "19-6"  |        1 | 199.47 | 29.00 |
| "19-6"  |        1 | 176.54 | 29.00 |
| "20-10" |        2 | 72.67  | 29.00 |
| "20-11" |        2 | 18.95  | 29.00 |
| "20-9"  |        1 | 22.13  | 29.00 |
+---------+----------+--------+-------+

Each id_test object as a quantity, a price and a fee. I would like to SUM everything so to have a global price : (quantity*price)+fee
The thing is, and that's where I'm stuck, the fee has to be added only once by id_test. Yet, I have here two id_test "19-6".

If I SUM everything I have 1827.67 (price*quantity) + 348 (fee).

Since I have two "19-6", I need 1827.67 (price*quantity) + 319 (fee).

How would you do in SQL ?

Thanks

Body:
select sum(quantity * price),
       (select sum(fee)
        from (select distinct(id_test), fee
        from sales) as fees)
from sales;

I created a SQLFiddle to demonstrate.


问:
ASP.Net MVC RedirectToAction with anchor
I have the following problem:
For example I have route like this:

```<code>routes.Add(new Route("forums/thread/{threadOid}/last", new MvcRouteHandler())
           Defaults = new RouteValueDictionary(
             new { controller = "Thread", action ="ShowThreadLastPostPage"}),
        Constraints = new RouteValueDictionary(new { threadOid = @"^\d+$" })
    }
);
</code>```

Is there a way using RedirectToAction method navigate to the URL like this:

forums/thread/{threadOid}/last#postOid

答:
I think you should use the Redirect method along with Url.RouteUrl to accomplish it.

```<code>return Redirect(Url.RouteUrl(new { controller = "Thread", action = "ShowThreadLastPostPage", threadOid = threadId }) + "#" + postOid);
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I have the following problem:
For example I have route like this:

routes.Add(new Route("forums/thread/{threadOid}/last", new MvcRouteHandler())
           Defaults = new RouteValueDictionary(
             new { controller = "Thread", action ="ShowThreadLastPostPage"}),
        Constraints = new RouteValueDictionary(new { threadOid = @"^\d+$" })
    }
);

Is there a way using RedirectToAction method navigate to the URL like this:

forums/thread/{threadOid}/last#postOid

Body:
I think you should use the Redirect method along with Url.RouteUrl to accomplish it.

return Redirect(Url.RouteUrl(new { controller = "Thread", action = "ShowThreadLastPostPage", threadOid = threadId }) + "#" + postOid);

OwnerDisplayName: Mehrdad Afshari

ContentLicense: CC BY-SA 2.5


问:
How to list all the names and ages associated with each activity
I have a dataframe that contains information about which people are taking part in each activity. Unfortunately there are up to a number of different places each piece of information can be put. That is one row can give information for many different people. The column names are as follows:

```<code>['Name', 'Activity', 'Name.0', 'Age.0', 'Activity.0', 'Name.1', 'Age.1', 'Activity.1', 'Name.2', 'Age.2', 'Activity.2', 'Name.3', 'Age.3',  'Activity.3', 'Name.4', 'Age.4', 'Activity.4']
</code>```

For each Activity, I want to list all the people who are associated with it and their ages (if that information is provided). Ultimately I want to make one table per Activity. How can I do that?

It feels like I need to first need to deal with all the .* column names and then groupby Activity somehow but I am not sure how to do it.

Here is some anonymized data outputted using df.sample(5).to_dict().

```<code>{'Activity': {0: 'Audi', 1: 'Carn', 2: 'Pop-', 3: 'Samb', 4: 'Pre-'},
 'Activity.0': {0: 'Samb', 1: 'Pre-', 2: 'nan', 3: 'Samb', 4: 'Pre-'},
 'Activity.1': {0: 'Samb', 1: 'Audi', 2: 'nan', 3: 'Samb', 4: 'nan'},
 'Activity.2': {0: 'Pre-', 1: 'Audi', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Activity.3': {0: 'nan', 1: 'Carn', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Activity.4': {0: 'nan', 1: 'Carn', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Age.0': {0: '18+', 1: 'Under 5', 2: '11', 3: '11', 4: 'Under 5'},
 'Age.1': {0: '11', 1: 'Under 5', 2: '11', 3: 17, 4: '18+'},
 'Age.2': {0: '11', 1: '18+', 2: 'nan', 3: '18+', 4: 'nan'},
 'Age.3': {0: 'Under 5', 1: '18+', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Age.4': {0: 'nan', 1: '18+', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Name': {0: 'Jess', 1: 'Tama', 2: 'Beki', 3: 'Havi', 4: 'Dror'},
 'Name.0': {0: 'Sam ', 1: 'Lila', 2: 'nan', 3: 'Joel', 4: 'Emil'},
 'Name.1': {0: 'Zipp', 1: 'Marg', 2: 'nan', 3: 'Solo', 4: 'Ari '},
 'Name.2': {0: 'Reub', 1: 'Joan', 2: 'nan', 3: 'Sami', 4: 'nan'},
 'Name.3': {0: 'Shev', 1: 'John', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Name.4': {0: 'nan', 1: 'Edwa', 2: 'nan', 3: 'nan', 4: 'nan'}}
</code>```

For this example, let's take the activity called 'Audi'.  The only people doing this are 'Jess' (no age), 'Marge' (under 5) and 'Joan' (18+).  For the activity 'Samb', the people associated with it are 'Havi' (no age), 'Sam' (18+), 'Joel' (11),'Zipp' (11) and 'Solo' (17).   I would like the same thing for each of the activities.

答:
This will work for you:

```<code>activities = {}
# function for Activity, Name
def add_details_for_activity(row):
    global activities
    if pd.notna(row[1]) and pd.notna(row[0]) and row[1] != 'nan' and row[0] != 'nan':
        if activities.get(row[0]) is not None:
            activities[row[0]].append(row[1])
        else:
            activities[row[0]] = [row[1]]

# function for Activity.#, Name.#, age.#
def add_details_for_activities(row):
    global activities
    if pd.notna(row[1]) and pd.notna(row[0]) and row[1] != 'nan' and row[0] != 'nan':
        if activities.get(row[0]) is not None:
            if pd.notna(row[2]) and row[2] != 'nan':
                activities[row[0]].append((row[1], row[2]))
            else:
                activities[row[0]].append(row[1])
        else:
            print(row[0])
            if pd.notna(row[2]) and row[2] != 'nan':
                activities[row[0]] = [(row[1], row[2])]
            else:
                activities[row[0]] = [row[1]]

df[['Activity', 'Name']].apply(add_details_for_activity, axis = 1)
for i in range(5):
    df[['Activity.'+str(i), 'Name.'+str(i), 'Age.'+str(i)]].apply(add_details_for_activities, axis = 1)
</code>```

Sample output is:

```<code>{'Audi': ['Jess', ('Marg', 'Under 5'), ('Joan', '18+')],
 'Carn': ['Tama', ('John', '18+'), ('Edwa', '18+')],
 'Pop-': ['Beki'],
 'Samb': ['Havi',
  ('Sam ', '18+'),
  ('Joel', '11'),
  ('Zipp', '11'),
  ('Solo', 17)],
 'Pre-': ['Dror', ('Lila', 'Under 5'), ('Emil', 'Under 5'), ('Reub', '11')]}
</code>```

Body:
I have a dataframe that contains information about which people are taking part in each activity. Unfortunately there are up to a number of different places each piece of information can be put. That is one row can give information for many different people. The column names are as follows:

['Name', 'Activity', 'Name.0', 'Age.0', 'Activity.0', 'Name.1', 'Age.1', 'Activity.1', 'Name.2', 'Age.2', 'Activity.2', 'Name.3', 'Age.3',  'Activity.3', 'Name.4', 'Age.4', 'Activity.4']

For each Activity, I want to list all the people who are associated with it and their ages (if that information is provided). Ultimately I want to make one table per Activity. How can I do that?

It feels like I need to first need to deal with all the .* column names and then groupby Activity somehow but I am not sure how to do it.

Here is some anonymized data outputted using df.sample(5).to_dict().

{'Activity': {0: 'Audi', 1: 'Carn', 2: 'Pop-', 3: 'Samb', 4: 'Pre-'},
 'Activity.0': {0: 'Samb', 1: 'Pre-', 2: 'nan', 3: 'Samb', 4: 'Pre-'},
 'Activity.1': {0: 'Samb', 1: 'Audi', 2: 'nan', 3: 'Samb', 4: 'nan'},
 'Activity.2': {0: 'Pre-', 1: 'Audi', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Activity.3': {0: 'nan', 1: 'Carn', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Activity.4': {0: 'nan', 1: 'Carn', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Age.0': {0: '18+', 1: 'Under 5', 2: '11', 3: '11', 4: 'Under 5'},
 'Age.1': {0: '11', 1: 'Under 5', 2: '11', 3: 17, 4: '18+'},
 'Age.2': {0: '11', 1: '18+', 2: 'nan', 3: '18+', 4: 'nan'},
 'Age.3': {0: 'Under 5', 1: '18+', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Age.4': {0: 'nan', 1: '18+', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Name': {0: 'Jess', 1: 'Tama', 2: 'Beki', 3: 'Havi', 4: 'Dror'},
 'Name.0': {0: 'Sam ', 1: 'Lila', 2: 'nan', 3: 'Joel', 4: 'Emil'},
 'Name.1': {0: 'Zipp', 1: 'Marg', 2: 'nan', 3: 'Solo', 4: 'Ari '},
 'Name.2': {0: 'Reub', 1: 'Joan', 2: 'nan', 3: 'Sami', 4: 'nan'},
 'Name.3': {0: 'Shev', 1: 'John', 2: 'nan', 3: 'nan', 4: 'nan'},
 'Name.4': {0: 'nan', 1: 'Edwa', 2: 'nan', 3: 'nan', 4: 'nan'}}

For this example, let's take the activity called 'Audi'.  The only people doing this are 'Jess' (no age), 'Marge' (under 5) and 'Joan' (18+).  For the activity 'Samb', the people associated with it are 'Havi' (no age), 'Sam' (18+), 'Joel' (11),'Zipp' (11) and 'Solo' (17).   I would like the same thing for each of the activities.

Body:
This will work for you:

activities = {}
# function for Activity, Name
def add_details_for_activity(row):
    global activities
    if pd.notna(row[1]) and pd.notna(row[0]) and row[1] != 'nan' and row[0] != 'nan':
        if activities.get(row[0]) is not None:
            activities[row[0]].append(row[1])
        else:
            activities[row[0]] = [row[1]]

# function for Activity.#, Name.#, age.#
def add_details_for_activities(row):
    global activities
    if pd.notna(row[1]) and pd.notna(row[0]) and row[1] != 'nan' and row[0] != 'nan':
        if activities.get(row[0]) is not None:
            if pd.notna(row[2]) and row[2] != 'nan':
                activities[row[0]].append((row[1], row[2]))
            else:
                activities[row[0]].append(row[1])
        else:
            print(row[0])
            if pd.notna(row[2]) and row[2] != 'nan':
                activities[row[0]] = [(row[1], row[2])]
            else:
                activities[row[0]] = [row[1]]

df[['Activity', 'Name']].apply(add_details_for_activity, axis = 1)
for i in range(5):
    df[['Activity.'+str(i), 'Name.'+str(i), 'Age.'+str(i)]].apply(add_details_for_activities, axis = 1)

Sample output is:

{'Audi': ['Jess', ('Marg', 'Under 5'), ('Joan', '18+')],
 'Carn': ['Tama', ('John', '18+'), ('Edwa', '18+')],
 'Pop-': ['Beki'],
 'Samb': ['Havi',
  ('Sam ', '18+'),
  ('Joel', '11'),
  ('Zipp', '11'),
  ('Solo', 17)],
 'Pre-': ['Dror', ('Lila', 'Under 5'), ('Emil', 'Under 5'), ('Reub', '11')]}


问:
From a Java Stream of Map group them by keys and find the maxium value not working as expected
From an input Stream<Map<String,Float> I am trying to find the unique analytes and which has the maxium value. I tried the below but not getting the expected result.
I am making some mistake in collecting the value.

Sample Input Data: List<Map<String,Float>

```<code>{HNO3=2.005}
{HNO3=2.009}
{HCl=10.2}
{F-=0.0, HNO3=50.0}

Actual result   : {HCl=Optional[HCl=10.2], F-=Optional[F-=0.0], HNO3=Optional[HNO3=50.0]}
Expected result : {HCl=10.2, F-=0.0, HNO3=50.0}
</code>```

Java code I tried:-

```<code>finalResponse.getChosenStartingMaterials()
    .stream()
    .map(Analyte::getMatrixUtilisationMap)  // Stream<Map<String,Float>>
    .flatMap(it -> it.entrySet().stream())
    .collect(Collectors.groupingBy(Map.Entry::getKey, Collectors.maxBy((m1, m2) -> m1.getValue().compareTo(m2.getValue()))))
</code>```

答:
Collectors.maxBy() produces an Optional, since if maxBy is applied on an empty Stream, it has to return an empty Optional.

You can use Collectors.toMap instead of Collectors.groupingBy to avoid the Optionals:

```<code>finalResponse.getChosenStartingMaterials()
    .stream()
    .map(Analyte::getMatrixUtilisationMap)  // Stream<Map<String,Float>>
    .flatMap(it -> it.entrySet().stream())
    .collect(Collectors.toMap(Map.Entry::getKey,
                              Map.Entry::getValue,
                              (v1, v2) -> v1.compareTo(v2) >= 0 ? v1 : v2));
</code>```

or

```<code>finalResponse.getChosenStartingMaterials()
    .stream()
    .map(Analyte::getMatrixUtilisationMap)  // Stream<Map<String,Float>>
    .flatMap(it -> it.entrySet().stream())
    .collect(Collectors.toMap(Map.Entry::getKey,
                              Map.Entry::getValue,
                              Float::max));
</code>```

Body:
From an input Stream<Map<String,Float> I am trying to find the unique analytes and which has the maxium value. I tried the below but not getting the expected result.
I am making some mistake in collecting the value.

Sample Input Data: List<Map<String,Float>

{HNO3=2.005}
{HNO3=2.009}
{HCl=10.2}
{F-=0.0, HNO3=50.0}

Actual result   : {HCl=Optional[HCl=10.2], F-=Optional[F-=0.0], HNO3=Optional[HNO3=50.0]}
Expected result : {HCl=10.2, F-=0.0, HNO3=50.0}

Java code I tried:-

finalResponse.getChosenStartingMaterials()
    .stream()
    .map(Analyte::getMatrixUtilisationMap)  // Stream<Map<String,Float>>
    .flatMap(it -> it.entrySet().stream())
    .collect(Collectors.groupingBy(Map.Entry::getKey, Collectors.maxBy((m1, m2) -> m1.getValue().compareTo(m2.getValue()))))

Body:
Collectors.maxBy() produces an Optional, since if maxBy is applied on an empty Stream, it has to return an empty Optional.

You can use Collectors.toMap instead of Collectors.groupingBy to avoid the Optionals:

finalResponse.getChosenStartingMaterials()
    .stream()
    .map(Analyte::getMatrixUtilisationMap)  // Stream<Map<String,Float>>
    .flatMap(it -> it.entrySet().stream())
    .collect(Collectors.toMap(Map.Entry::getKey,
                              Map.Entry::getValue,
                              (v1, v2) -> v1.compareTo(v2) >= 0 ? v1 : v2));

or

finalResponse.getChosenStartingMaterials()
    .stream()
    .map(Analyte::getMatrixUtilisationMap)  // Stream<Map<String,Float>>
    .flatMap(it -> it.entrySet().stream())
    .collect(Collectors.toMap(Map.Entry::getKey,
                              Map.Entry::getValue,
                              Float::max));


问:
Displaying dictionary values each in a UITableViewCell
My overall objective here is to create a UITableView that has alphabetical section titles based on the values in a dictionary.

I have a basic object which includes the fruit's name and description. I would like the names of the fruit to each be displayed on a UITableCell, under the cooresponding letter for the section title.

Ultimately, the tableview will look something like this: (where C P S are the section titles)

```<code>C------------
Clementine
Cherry
Coconut
P------------
Pear
Peach
S------------
Strawberry
</code>```

Here is my data:

```<code>var fruitModel = [String:Any]()

fruitModel = [
    "C" : [
    FruitModel(name: "Clementine", description: "Clementines are super tasty!"),
    FruitModel(name: "Cherry", description: "Cherry's are red."),
    FruitModel(name: "Coconut", description: "Nuts for coconuts!!!"),
    ],
    "P" : [
    FruitModel(name: "Pear", description: "Pears rock!"),
    FruitModel(name: "Peach", description: "Mmmm, Peach."),
    ],
    "S" : [
    FruitModel(name: "Strawberry", description: "A widely grown hybrid species of the genus Fragaria. It is cultivated worldwide for its fruit.")
    ]
]
</code>```

The part that's throwing me off is accessing the name string, inside the dictionary's value, and displaying it in cellForRowAt. The code below is where I am stuck:

cellForRowAt:

```<code>var allValues = Array(fruitModel.values)
cell.fruitNameLabel.text = (allValues[indexPath.row] as AnyObject).name // Cannot subscript a value of type 'inout Array<Any>'
</code>```

Once I figure out how to display the fruit names in each cell, I'll be able to get the section titles working. I just need assistance with my error that i'm getting. Thanks.

答:
here full example for your query</p>

```<code>class ViewController: UIViewController, UITableViewDelegate, UITableViewDataSource {

struct FruitModel {
    var name: String
    var description: String
}
var fruitModel = [String: [FruitModel]]()
var dataKeys : Array = [String]()

@IBOutlet weak var tableView: UITableView!
override func viewDidLoad() {
    super.viewDidLoad()
    // Do any additional setup after loading the view, typically from a nib.
    tableView.delegate = self
    tableView.dataSource = self
    fruitModel = [
        "C" : [
        FruitModel(name: "Clementine", description: "Clementines are super tasty!"),
        FruitModel(name: "Cherry", description: "Cherry's are red."),
        FruitModel(name: "Coconut", description: "Nuts for coconuts!!!"),
        ],
        "P" : [
        FruitModel(name: "Pear", description: "Pears rock!"),
        FruitModel(name: "Peach", description: "Mmmm, Peach."),
        ],
        "S" : [
        FruitModel(name: "Strawberry", description: "A widely grown hybrid species of the genus Fragaria. It is cultivated worldwide for its fruit.")
        ]
    ]

    dataKeys = (fruitModel as NSDictionary).allKeys as! [String]
    print(dataKeys)
    tableView.reloadData()

}

func numberOfSections(in tableView: UITableView) -> Int {
    return dataKeys.count
}
func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
    let sec = dataKeys[section]
    return fruitModel[sec]?.count ?? 0
}
func tableView(_ tableView: UITableView, titleForHeaderInSection section: Int) -> String? {
    return dataKeys[section]
}
func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
    let cell = tableView.dequeueReusableCell(withIdentifier: "cell")!
    let sec = dataKeys[indexPath.section]
    cell.textLabel?.text = fruitModel[sec]?[indexPath.row].name

    return cell;
}
}
</code>```

Body:
My overall objective here is to create a UITableView that has alphabetical section titles based on the values in a dictionary.

I have a basic object which includes the fruit's name and description. I would like the names of the fruit to each be displayed on a UITableCell, under the cooresponding letter for the section title.

Ultimately, the tableview will look something like this: (where C P S are the section titles)

C------------
Clementine
Cherry
Coconut
P------------
Pear
Peach
S------------
Strawberry

Here is my data:

var fruitModel = [String:Any]()

fruitModel = [
    "C" : [
    FruitModel(name: "Clementine", description: "Clementines are super tasty!"),
    FruitModel(name: "Cherry", description: "Cherry's are red."),
    FruitModel(name: "Coconut", description: "Nuts for coconuts!!!"),
    ],
    "P" : [
    FruitModel(name: "Pear", description: "Pears rock!"),
    FruitModel(name: "Peach", description: "Mmmm, Peach."),
    ],
    "S" : [
    FruitModel(name: "Strawberry", description: "A widely grown hybrid species of the genus Fragaria. It is cultivated worldwide for its fruit.")
    ]
]

The part that's throwing me off is accessing the name string, inside the dictionary's value, and displaying it in cellForRowAt. The code below is where I am stuck:

cellForRowAt:

var allValues = Array(fruitModel.values)
cell.fruitNameLabel.text = (allValues[indexPath.row] as AnyObject).name // Cannot subscript a value of type 'inout Array<Any>'

Once I figure out how to display the fruit names in each cell, I'll be able to get the section titles working. I just need assistance with my error that i'm getting. Thanks.

Body:
here full example for your query

class ViewController: UIViewController, UITableViewDelegate, UITableViewDataSource {

struct FruitModel {
    var name: String
    var description: String
}
var fruitModel = [String: [FruitModel]]()
var dataKeys : Array = [String]()

@IBOutlet weak var tableView: UITableView!
override func viewDidLoad() {
    super.viewDidLoad()
    // Do any additional setup after loading the view, typically from a nib.
    tableView.delegate = self
    tableView.dataSource = self
    fruitModel = [
        "C" : [
        FruitModel(name: "Clementine", description: "Clementines are super tasty!"),
        FruitModel(name: "Cherry", description: "Cherry's are red."),
        FruitModel(name: "Coconut", description: "Nuts for coconuts!!!"),
        ],
        "P" : [
        FruitModel(name: "Pear", description: "Pears rock!"),
        FruitModel(name: "Peach", description: "Mmmm, Peach."),
        ],
        "S" : [
        FruitModel(name: "Strawberry", description: "A widely grown hybrid species of the genus Fragaria. It is cultivated worldwide for its fruit.")
        ]
    ]

    dataKeys = (fruitModel as NSDictionary).allKeys as! [String]
    print(dataKeys)
    tableView.reloadData()

}

func numberOfSections(in tableView: UITableView) -> Int {
    return dataKeys.count
}
func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
    let sec = dataKeys[section]
    return fruitModel[sec]?.count ?? 0
}
func tableView(_ tableView: UITableView, titleForHeaderInSection section: Int) -> String? {
    return dataKeys[section]
}
func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
    let cell = tableView.dequeueReusableCell(withIdentifier: "cell")!
    let sec = dataKeys[indexPath.section]
    cell.textLabel?.text = fruitModel[sec]?[indexPath.row].name

    return cell;
}
}


问:
How to avoid requesting user on login page while using context_processors.py in Django?
I wanted to pass variables with the User information, that is linked to a Clients model through a OneToOneField to the django.contrib.auth.models, onto the base.html file.

So, I created a context_processors.py with the following code

```<code>from django.contrib.auth.models import User

    def userData(request):
        user = request.user
        u = User.objects.get(username=user)
        us = u.clients.first_name
        uv = u.clients.avatar
        return {
            'u': u,
            'us': us,
            'uv': uv
        }
</code>```

Everything was working fine, until I logged out.

When I try to login again, I get the accounts/login url and get a

```<code>DoesNotExist at /accounts/login/
User matching query does not exist.
</code>```

答:
Well your request.user is already a User, so it makes not much sense to fetch it again.

You can simply check if the user is logged in:

```<code>def userData(request):
    if <b>request.user.is_authenticated</b>:
        return {
            'u': request.user,
            'us': request.user.clients.first_name,
            'uv': request.user.clients.avatar,
        }
    return {}</code>```

Note: the name clients is plular, so that suggests a collection of Clients. Normally model names are singular (so Client instead of <s>Clients</s>), and the related name of a OneToOneField should, if you specify it yourself, be singular as well.

Body:
I wanted to pass variables with the User information, that is linked to a Clients model through a OneToOneField to the django.contrib.auth.models, onto the base.html file.

So, I created a context_processors.py with the following code

from django.contrib.auth.models import User

    def userData(request):
        user = request.user
        u = User.objects.get(username=user)
        us = u.clients.first_name
        uv = u.clients.avatar
        return {
            'u': u,
            'us': us,
            'uv': uv
        }

Everything was working fine, until I logged out.

When I try to login again, I get the accounts/login url and get a

DoesNotExist at /accounts/login/
User matching query does not exist.

Body:
Well your request.user is already a User, so it makes not much sense to fetch it again.

You can simply check if the user is logged in:

def userData(request):
    if request.user.is_authenticated:
        return {
            'u': request.user,
            'us': request.user.clients.first_name,
            'uv': request.user.clients.avatar,
        }
    return {}

Note: the name clients is plular, so that suggests a collection of Clients. Normally model names are singular (so Client instead of <s>Clients</s>), and the related name of a OneToOneField should, if you specify it yourself, be singular as well.


问:
How do I update state (open,complete) in a silverlight/odata project for Dynamics CRM 2011
I have a silverlight app hosted in CRM 2011 online. I have functionality where I am updating a phone call and display two fields in a small form. I have the requirement to mark as complete but I seem to be failing here.

I have:

```<code>phoneCall.StateCode.Value = 1;

phoneCall.Subject = activity.Subject;
phoneCall.Description = activity.Description;

_context.UpdateObject(phoneCall);
_context.BeginSaveChanges(OnChangesSaved, phoneCall);
</code>```

The subject works and saves as well as the description but the statecode does not. StateCode is not null this is an existing object and it's currently set to 0 (open). The save does not affect the StateCode. I have this in a try-catch and no error is being reported.

答:
The status of a record cannot be changed with an Update message. In order to change the statecode or status code, you have to issue a SetStateRequest.

```<code>var setStateRequest = new SetStateRequest
{
  EntityMoniker = new EntityReference({LogicalName}, {Id}),
  State = new OptionSetValue(1),
  Status = new OptionSetValue(1)
};

_context.Execute(setStateRequest);
</code>```

Update

The REST endpoint has some limitations.

The REST endpoint provides an alternative to the WCF SOAP endpoint, but there are currently some limitations.

Only Create, Retrieve, Update and Delete actions can be performed on entity records
  Messages that require the Execute method cannot be performed.

This means for you: you cannot alter the state with the REST endpoint. You have to use the WCF SOAP endpoint for this task.

Body:
I have a silverlight app hosted in CRM 2011 online. I have functionality where I am updating a phone call and display two fields in a small form. I have the requirement to mark as complete but I seem to be failing here.

I have:

phoneCall.StateCode.Value = 1;

phoneCall.Subject = activity.Subject;
phoneCall.Description = activity.Description;

_context.UpdateObject(phoneCall);
_context.BeginSaveChanges(OnChangesSaved, phoneCall);

The subject works and saves as well as the description but the statecode does not. StateCode is not null this is an existing object and it's currently set to 0 (open). The save does not affect the StateCode. I have this in a try-catch and no error is being reported.

Body:
The status of a record cannot be changed with an Update message. In order to change the statecode or status code, you have to issue a SetStateRequest.

var setStateRequest = new SetStateRequest
{
  EntityMoniker = new EntityReference({LogicalName}, {Id}),
  State = new OptionSetValue(1),
  Status = new OptionSetValue(1)
};

_context.Execute(setStateRequest);

Update

The REST endpoint has some limitations.

The REST endpoint provides an alternative to the WCF SOAP endpoint, but there are currently some limitations.

Only Create, Retrieve, Update and Delete actions can be performed on entity records
  Messages that require the Execute method cannot be performed.

This means for you: you cannot alter the state with the REST endpoint. You have to use the WCF SOAP endpoint for this task.


问:
Is there anyway to clear signal mask set in bash script
I'm trying look for any way to clear the process signal mask set in one bash script. Here's the problem:

My bash script is invoked by one C++ binary program which blocks some signals via sigprocmask function, thus, my bash script becomes one child process of the C++ process and inherits its signal mask set. Then, the bash script cannot receive the blocked signals any more.

So, is there anyway to clear the signal mask set in bash script?

答: If you have the GNU coreutils 8.31 installed, you could try to use the "env"  command with --default-signal[=sig] (maybe together with a wrapper script that traps and forwards the signals), see e.g. the env man page or GNU core utils manual.


问:
Jest looping through dynamic test cases
How do I loop through dynamic test cases in Jest?

I have test cases like the following how do I dynamically create jest test case using it/test methods.

Here is what I have tried , However it just passes  without excuting the test cases in the loop.

```<code>    const mymodule = require('mymodule');

    const testCases = [
        {q: [2, 3],r: 5},
        {q: [1, 2],r: 3},
        {q: [7, 0],r: 7},
        {q: [4, 4],r: 8}
    ];

    describe("Test my Math module", () => {
        test("test add method", () => {
            for (let i = 0; i < testCases.length; i++) {
                const { q,r } = testCases[i];
                it(`should  add ${q[0]},${q[1]} to ${expected}`, () => {
                    const actual = mymodule.add(q[0] + q[1]);
                    expect(actual).toBe(expected);
                });
            }
        });

    });
</code>```

答:
If anyone wondering how this can work for a single input function here is an example

```<code>const testData = [
      ['input1', 'output1'],
      ['input2', 'output2'],
      ['input3', 'output3'],
]

test.each(testData)('myFunc work correctly for %s',(input, output) =>{
        expect(yourFunc(input)).toBe(output)
})
</code>```

https://jestjs.io/docs/en/api#testeachtablename-fn-timeout

Title: Jest looping through dynamic test cases

Body:
How do I loop through dynamic test cases in Jest?

I have test cases like the following how do I dynamically create jest test case using it/test methods.

Here is what I have tried , However it just passes  without excuting the test cases in the loop.

    const mymodule = require('mymodule');

    const testCases = [
        {q: [2, 3],r: 5},
        {q: [1, 2],r: 3},
        {q: [7, 0],r: 7},
        {q: [4, 4],r: 8}
    ];

    describe("Test my Math module", () => {
        test("test add method", () => {
            for (let i = 0; i < testCases.length; i++) {
                const { q,r } = testCases[i];
                it(`should  add ${q[0]},${q[1]} to ${expected}`, () => {
                    const actual = mymodule.add(q[0] + q[1]);
                    expect(actual).toBe(expected);
                });
            }
        });

    });

Body:
If anyone wondering how this can work for a single input function here is an example

const testData = [
      ['input1', 'output1'],
      ['input2', 'output2'],
      ['input3', 'output3'],
]

test.each(testData)('myFunc work correctly for %s',(input, output) =>{
        expect(yourFunc(input)).toBe(output)
})

https://jestjs.io/docs/en/api#testeachtablename-fn-timeout


问:
oauth2-proxy authentication calls slow on kubernetes cluster with auth annotations for nginx ingress
We have secured some of our services on the K8S cluster using the approach described on this page. Concretely, we have:

```<code>  nginx.ingress.kubernetes.io/auth-url: "https://oauth2.${var.hosted_zone}/oauth2/auth"
  nginx.ingress.kubernetes.io/auth-signin: "https://oauth2.${var.hosted_zone}/oauth2/start?rd=/redirect/$http_host$escaped_request_uri"
</code>```

set on the service to be secured and we have followed this tutorial to only have one deployment of oauth2_proxy per cluster. We have 2 proxies set up, both with affinity to be placed on the same node as the nginx ingress.

```<code>$ kubectl get pods -o wide -A | egrep "nginx|oauth"
infra-system   wer-exp-nginx-ingress-exp-controller-696f5fbd8c-bm5ld        1/1     Running   0          3h24m   10.76.11.65    ip-10-76-9-52.eu-central-1.compute.internal     <none>           <none>
infra-system   wer-exp-nginx-ingress-exp-controller-696f5fbd8c-ldwb8        1/1     Running   0          3h24m   10.76.14.42    ip-10-76-15-164.eu-central-1.compute.internal   <none>           <none>
infra-system   wer-exp-nginx-ingress-exp-default-backend-7d69cc6868-wttss   1/1     Running   0          3h24m   10.76.15.52    ip-10-76-15-164.eu-central-1.compute.internal   <none>           <none>
infra-system   wer-exp-nginx-ingress-exp-default-backend-7d69cc6868-z998v   1/1     Running   0          3h24m   10.76.11.213   ip-10-76-9-52.eu-central-1.compute.internal     <none>           <none>
infra-system   oauth2-proxy-68bf786866-vcdns                                 2/2     Running   0          14s     10.76.10.106   ip-10-76-9-52.eu-central-1.compute.internal     <none>           <none>
infra-system   oauth2-proxy-68bf786866-wx62c                                 2/2     Running   0          14s     10.76.12.107   ip-10-76-15-164.eu-central-1.compute.internal   <none>           <none>
</code>```

However, a simple website load usually takes around 10 seconds, compared to 2-3 seconds with the proxy annotations not being present on the secured service.

We added a proxy_cache to the auth.domain.com service which hosts our proxy by adding

```<code>        "nginx.ingress.kubernetes.io/server-snippet": <<EOF
          proxy_cache auth_cache;
          proxy_cache_lock on;
          proxy_ignore_headers Cache-Control;
          proxy_cache_valid any 30m;
          add_header X-Cache-Status $upstream_cache_status;
        EOF
</code>```

but this didn't improve the latency either. We still see all HTTP requests triggering a log line in our proxy. Oddly, only some of the requests take 5 seconds.

We are unsure if:
- the proxy forwards each request to the oauth provider (github) or
- caches the authentications

We use cookie authentication, therefore, in theory, the oauth2_proxy should just decrypt the cookie and then return a 200 to the nginx ingress. Since they are both on the same node it should be fast. But it's not. Any ideas?

Edit 1

I have analyzed the situation further. Visiting my auth server with https://oauth2.domain.com/auth in the browser and copying the request copy for curl I found that:

running 10.000 queries against my oauth server from my local machine (via curl) is very fast

running 100 requests on the nginx ingress with the same curl is slow

replacing the host name in the curl with the cluster IP of the auth service makes the performance increase drastically

setting the annotation to nginx.ingress.kubernetes.io/auth-url: http://172.20.95.17/oauth2/auth (e.g. setting the host == cluster IP) makes the GUI load as expected (fast)

it doesn't matter if the curl is run on the nginx-ingress or on any other pod (e.g. a test debian), the result is the same

Edit 2

A better fix I found was to set the annotation to the following

```<code>  nginx.ingress.kubernetes.io/auth-url: "http://oauth2.infra-system.svc.cluster.local/oauth2/auth"
  nginx.ingress.kubernetes.io/auth-signin: "https://oauth2.domain.com/oauth2/start?rd=/redirect/$http_host$escaped_request_uri"
</code>```

The auth-url is what the ingress queries with the cookie of the user. Hence, a local DNS of the oauth2 service is the same as the external dns name, but without the SSL communication and since it's DNS, it's permanent (while the cluster IP is not)

答:
In my opinion you observe the increased latency in response time in case of:

 nginx.ingress.kubernetes.io/auth-url: "https://oauth2.${var.hosted_zone}/oauth2/auth"
 setting
due to the fact, that auth server URL resolves to the external service (in this case VIP of Load Balancer seating in front of Ingress Controller).

Practically it means, that you go out with the traffic outside of the cluster (so called hairpin mode), and goes back via External IP of Ingress that routes to internal ClusterIP Service (which adds extra hops), instead going directly with ClusterIP/Service DNS name (you stay within
 Kubernetes cluster):

```<code>nginx.ingress.kubernetes.io/auth-url: "http://oauth2.infra-system.svc.cluster.local/oauth2/auth"
</code>```

Body:
We have secured some of our services on the K8S cluster using the approach described on this page. Concretely, we have:

  nginx.ingress.kubernetes.io/auth-url: "https://oauth2.${var.hosted_zone}/oauth2/auth"
  nginx.ingress.kubernetes.io/auth-signin: "https://oauth2.${var.hosted_zone}/oauth2/start?rd=/redirect/$http_host$escaped_request_uri"

set on the service to be secured and we have followed this tutorial to only have one deployment of oauth2_proxy per cluster. We have 2 proxies set up, both with affinity to be placed on the same node as the nginx ingress.

$ kubectl get pods -o wide -A | egrep "nginx|oauth"
infra-system   wer-exp-nginx-ingress-exp-controller-696f5fbd8c-bm5ld        1/1     Running   0          3h24m   10.76.11.65    ip-10-76-9-52.eu-central-1.compute.internal     <none>           <none>
infra-system   wer-exp-nginx-ingress-exp-controller-696f5fbd8c-ldwb8        1/1     Running   0          3h24m   10.76.14.42    ip-10-76-15-164.eu-central-1.compute.internal   <none>           <none>
infra-system   wer-exp-nginx-ingress-exp-default-backend-7d69cc6868-wttss   1/1     Running   0          3h24m   10.76.15.52    ip-10-76-15-164.eu-central-1.compute.internal   <none>           <none>
infra-system   wer-exp-nginx-ingress-exp-default-backend-7d69cc6868-z998v   1/1     Running   0          3h24m   10.76.11.213   ip-10-76-9-52.eu-central-1.compute.internal     <none>           <none>
infra-system   oauth2-proxy-68bf786866-vcdns                                 2/2     Running   0          14s     10.76.10.106   ip-10-76-9-52.eu-central-1.compute.internal     <none>           <none>
infra-system   oauth2-proxy-68bf786866-wx62c                                 2/2     Running   0          14s     10.76.12.107   ip-10-76-15-164.eu-central-1.compute.internal   <none>           <none>

However, a simple website load usually takes around 10 seconds, compared to 2-3 seconds with the proxy annotations not being present on the secured service.

We added a proxy_cache to the auth.domain.com service which hosts our proxy by adding

        "nginx.ingress.kubernetes.io/server-snippet": <<EOF
          proxy_cache auth_cache;
          proxy_cache_lock on;
          proxy_ignore_headers Cache-Control;
          proxy_cache_valid any 30m;
          add_header X-Cache-Status $upstream_cache_status;
        EOF

but this didn't improve the latency either. We still see all HTTP requests triggering a log line in our proxy. Oddly, only some of the requests take 5 seconds.

We are unsure if:
- the proxy forwards each request to the oauth provider (github) or
- caches the authentications

We use cookie authentication, therefore, in theory, the oauth2_proxy should just decrypt the cookie and then return a 200 to the nginx ingress. Since they are both on the same node it should be fast. But it's not. Any ideas?

Edit 1

I have analyzed the situation further. Visiting my auth server with https://oauth2.domain.com/auth in the browser and copying the request copy for curl I found that:

running 10.000 queries against my oauth server from my local machine (via curl) is very fast

running 100 requests on the nginx ingress with the same curl is slow

replacing the host name in the curl with the cluster IP of the auth service makes the performance increase drastically

setting the annotation to nginx.ingress.kubernetes.io/auth-url: http://172.20.95.17/oauth2/auth (e.g. setting the host == cluster IP) makes the GUI load as expected (fast)

it doesn't matter if the curl is run on the nginx-ingress or on any other pod (e.g. a test debian), the result is the same

Edit 2

A better fix I found was to set the annotation to the following

  nginx.ingress.kubernetes.io/auth-url: "http://oauth2.infra-system.svc.cluster.local/oauth2/auth"
  nginx.ingress.kubernetes.io/auth-signin: "https://oauth2.domain.com/oauth2/start?rd=/redirect/$http_host$escaped_request_uri"

The auth-url is what the ingress queries with the cookie of the user. Hence, a local DNS of the oauth2 service is the same as the external dns name, but without the SSL communication and since it's DNS, it's permanent (while the cluster IP is not)

Body:
In my opinion you observe the increased latency in response time in case of:

 nginx.ingress.kubernetes.io/auth-url: "https://oauth2.${var.hosted_zone}/oauth2/auth"
 setting
due to the fact, that auth server URL resolves to the external service (in this case VIP of Load Balancer seating in front of Ingress Controller).

Practically it means, that you go out with the traffic outside of the cluster (so called hairpin mode), and goes back via External IP of Ingress that routes to internal ClusterIP Service (which adds extra hops), instead going directly with ClusterIP/Service DNS name (you stay within
 Kubernetes cluster):

nginx.ingress.kubernetes.io/auth-url: "http://oauth2.infra-system.svc.cluster.local/oauth2/auth"


问:
get permission denied when trying to k6 run a js file
I am trying to learn k6 and I started with its official documents. I tried the sample code in run document with both docker image and command-line. In the docker version it works fine:

```<code>zeinab@ZiZi:~$ docker run -i loadimpact/k6 run - <k6_test.js

      /\      |‾‾|  /‾‾/  /‾/
     /\  /  \     |  |_/  /  / /
    /  \/    \    |      |  /  ‾‾\
   /          \   |  |‾\  \ | (_) |
  / __________ \  |__|  \__\ \___/ .io

  execution: local--------------------------------------------------]   servertor
     output: -
     script: -

    duration: -, iterations: 1
     vus: 1, max: 1

time="2020-02-18T11:14:26Z" level=info msg=Running i=0 t=971.124809ms starting
...
time="2020-02-18T11:14:55Z" level=info msg=Running i=0 t=29.971104099s
time="2020-02-18T11:14:55Z" level=warning msg="Request Failed" error="Get http://test.loadimpact.com: proxyconnect tcp: dial tcp 192.168.14.109:1087: i/o timeout"
time="2020-02-18T11:14:56Z" level=info msg=Running i=0 t=30.971060995s
time="2020-02-18T11:14:56Z" level=info msg="Test finished" i=1 t=31.000143344s

    data_received..............: 0 B 0 B/s
    data_sent..................: 0 B 0 B/s
    http_req_blocked...........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_connecting........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_duration..........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_receiving.........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_sending...........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_tls_handshaking...: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_waiting...........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_reqs..................: 1   0.032258/s
    iteration_duration.........: avg=31s min=31s med=31s max=31s p(90)=31s p(95)=31s
    iterations.................: 1   0.032258/s
    vus........................: 1   min=1 max=1
    vus_max....................: 1   min=1 max=1
</code>```

But when running from command-line, I get:

```<code>zeinab@ZiZi:~$ sudo k6 run ./k6_test.js

          /\      |‾‾|  /‾‾/  /‾/
     /\  /  \     |  |_/  /  / /
    /  \/    \    |      |  /  ‾‾\
   /          \   |  |‾\  \ | (_) |
  / __________ \  |__|  \__\ \___/ .io

ERRO[0000] open /home/zeinab/k6_test.js: permission denied
</code>```

EDIT 1:

I get the same error when running with current user:

```<code>k6 run ./k6_test.js
</code>```

EDIT 2:

I installed k6 using snap:

```<code>zeinab@ZiZi:~$ snap install k6
k6 v0.23.1 from Null Box (nullboxorg) installed
</code>```

答:
Given the fact you use snap to install it, this means that the k6 from inside snap doesn't have access to your whole fs as well (just as docker doesn't).

I don't understand snap all that much but given that whoever builds k6 for snap hasn't updated it in the last year I would recommend just getting the binary and running it ;).

Original answer:

It is exactly what it says: your root user can't open /home/zeinab/k6_test.js because they don't have permissions. I would guess they either can't read your home directory (/home/zeinab) or the file itself.

You don't really need to use sudo so either remove it or fix the permissions for the script, although again running with root is not necessary and is bad practice either way :D

As a side note, the docker works because whatever shell (probably bash) you run is the one that reads the file and send it to stdin of the docker process. So even if docker is alias for sudo docker it will still work.

Body:
I am trying to learn k6 and I started with its official documents. I tried the sample code in run document with both docker image and command-line. In the docker version it works fine:

zeinab@ZiZi:~$ docker run -i loadimpact/k6 run - <k6_test.js

      /\      |‾‾|  /‾‾/  /‾/
     /\  /  \     |  |_/  /  / /
    /  \/    \    |      |  /  ‾‾\
   /          \   |  |‾\  \ | (_) |
  / __________ \  |__|  \__\ \___/ .io

  execution: local--------------------------------------------------]   servertor
     output: -
     script: -

    duration: -, iterations: 1
     vus: 1, max: 1

time="2020-02-18T11:14:26Z" level=info msg=Running i=0 t=971.124809ms starting
...
time="2020-02-18T11:14:55Z" level=info msg=Running i=0 t=29.971104099s
time="2020-02-18T11:14:55Z" level=warning msg="Request Failed" error="Get http://test.loadimpact.com: proxyconnect tcp: dial tcp 192.168.14.109:1087: i/o timeout"
time="2020-02-18T11:14:56Z" level=info msg=Running i=0 t=30.971060995s
time="2020-02-18T11:14:56Z" level=info msg="Test finished" i=1 t=31.000143344s

    data_received..............: 0 B 0 B/s
    data_sent..................: 0 B 0 B/s
    http_req_blocked...........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_connecting........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_duration..........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_receiving.........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_sending...........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_tls_handshaking...: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_req_waiting...........: avg=0s  min=0s  med=0s  max=0s  p(90)=0s  p(95)=0s
    http_reqs..................: 1   0.032258/s
    iteration_duration.........: avg=31s min=31s med=31s max=31s p(90)=31s p(95)=31s
    iterations.................: 1   0.032258/s
    vus........................: 1   min=1 max=1
    vus_max....................: 1   min=1 max=1

But when running from command-line, I get:

zeinab@ZiZi:~$ sudo k6 run ./k6_test.js

          /\      |‾‾|  /‾‾/  /‾/
     /\  /  \     |  |_/  /  / /
    /  \/    \    |      |  /  ‾‾\
   /          \   |  |‾\  \ | (_) |
  / __________ \  |__|  \__\ \___/ .io

ERRO[0000] open /home/zeinab/k6_test.js: permission denied

EDIT 1:

I get the same error when running with current user:

k6 run ./k6_test.js

EDIT 2:

I installed k6 using snap:

zeinab@ZiZi:~$ snap install k6
k6 v0.23.1 from Null Box (nullboxorg) installed


问:
Want to implement this type(posted image) of side menu in swift ios
Want to implement this type(bellow image link provided) of  side menu/ drawer menu  in swift ios  https://dribbble.com/shots/5405926-Navigation-drawer-design-Dbotica-Healthcare/attachments</p>

答:
I suggest you to try AKSideMenu or InteractiveSideMenu

https://github.com/dogo/AKSideMenu

https://github.com/handsomecode/InteractiveSideMenu


问:
S.M.A.R.T HDD and WMI
I try to get the SMART data from my HDD using WMI (this is the only way I found).
Using the namespace root\WMI with class MSStorageDriver_ATAPISmartData I can get the VendorSpecific. I just wonder how can I choose for which HDD I want to check SMART. This give me only one the system drive what about other ones also installed in my computer?</p>

答:
You can filter on the other properties of the returned object.

Eg. MSStorageDriver_ATAPISmartData.InstanceName will match Win32_DiskDrive.PNPDeviceID and thus allowing access to more details about the drive and associations through the WMI object graph.

Title: S.M.A.R.T HDD and WMI


问:
How to define NOT is sanity.io structure builder?
I'm trying to filter all products without categories (which is array of references) and can't find how to do it.

```<code>S.listItem()
  .title('Without category')
  .id('productsWithoutCategories')
    .child(
      S.documentList()
        .title('Without categories')
        .menuItems(S.documentTypeList('product').getMenuItems())
        .filter('_type == $type && !category')
        .params({ type: 'product' })
      )
</code>```

!category not working.
 I will be grateful for the help.

答:
As you've already found out yourself, you can use the defined function to check if something is set (or not set by using the NOT operator !). You can find some documentation regarding the defined function here:

https://www.sanity.io/docs/how-queries-work#more-ways-to-filter-99fd60e475f2

https://www.sanity.io/docs/query-cheat-sheet#functions-a1da4a3b2adc

Just want to quote from the documentation as well:

You'll never see a null value on a document stored in Sanity. If you update a value to null, that key will disappear. Thus, the is null constraint you may know from SQL does not bear any meaning here. A value is either defined or !defined.

And from the How Queries Work page:

Filtering by the presence of a field, e.g. *[defined(status)] which only match document that have the status property set to any value.

Body:
I'm trying to filter all products without categories (which is array of references) and can't find how to do it.

S.listItem()
  .title('Without category')
  .id('productsWithoutCategories')
    .child(
      S.documentList()
        .title('Without categories')
        .menuItems(S.documentTypeList('product').getMenuItems())
        .filter('_type == $type && !category')
        .params({ type: 'product' })
      )

!category not working.
 I will be grateful for the help.


问:
distanceTo() integer overflow?
This is my method to determine the distance between two points:

```<code>
    // Euclidean distance between this point and that point
    public int distanceTo(Point that) {
        int distanceX = this.x - that.x;
        int distanceY = this.y - that.y;
        return (int) Math.sqrt(distanceX * distanceX + distanceY * distanceY);
    }

</code>```

Is it possible that an integer overflow occurs, and if so, how can I prevent it?

EDIT:

答:
First you can use the hypotenuse function.

Then integer overflow may happen at a distance (-).

The solution is to use double, as the the final result is calculated using a floating point function.

The result could be as large as sqrt(2)*2*Integer.MAX_VALUE, also overflowing.

So:

```<code>public int distanceTo(Point that) {
    double distanceX = ((double)this.x) - that.x;
    double distanceY = ((double)this.y) - that.y;
    double distance = Math.hypot(distanceX, distanceY);
    if (distance + 1 >= Integer.MAX_VALUE) {
        throw new ArithmeticException("Integer overflow");
    }
    return (int) distance; // (int) Math.round?
}
</code>```

Or neater (as did Andreas):

```<code>public int distanceTo(Point that) {
    double distanceX = ((double)this.x) - that.x;
    double distanceY = ((double)this.y) - that.y;
    double distance = Math.hypot(distanceX, distanceY);
    return Math.toIntExact((long)distance); // (int) Math.round?
}
</code>```

Title: distanceTo() integer overflow?

Body:
This is my method to determine the distance between two points:

    // Euclidean distance between this point and that point
    public int distanceTo(Point that) {
        int distanceX = this.x - that.x;
        int distanceY = this.y - that.y;
        return (int) Math.sqrt(distanceX * distanceX + distanceY * distanceY);
    }

Is it possible that an integer overflow occurs, and if so, how can I prevent it?

EDIT:

Body:
First you can use the hypotenuse function.

Then integer overflow may happen at a distance (-).

The solution is to use double, as the the final result is calculated using a floating point function.

The result could be as large as sqrt(2)*2*Integer.MAX_VALUE, also overflowing.

So:

public int distanceTo(Point that) {
    double distanceX = ((double)this.x) - that.x;
    double distanceY = ((double)this.y) - that.y;
    double distance = Math.hypot(distanceX, distanceY);
    if (distance + 1 >= Integer.MAX_VALUE) {
        throw new ArithmeticException("Integer overflow");
    }
    return (int) distance; // (int) Math.round?
}

Or neater (as did Andreas):

public int distanceTo(Point that) {
    double distanceX = ((double)this.x) - that.x;
    double distanceY = ((double)this.y) - that.y;
    double distance = Math.hypot(distanceX, distanceY);
    return Math.toIntExact((long)distance); // (int) Math.round?
}


问:
Available height and width for a web component?
I am writing a web component for which I need to know the available height and width when a user drops it on their web page. The web component wraps everything in a Shadow DOM and includes potentially scrollable part at least in the height direction. Beside that it includes things like ....

I am trying to find a strategy for this. "Available height and width" may not be very well defined in this scenario.

Any suggestion for what I can use? Any suggestion for clarifying my question?

答:
I am sorry but your description is quite confusing to be honest. If you do not provide an image or a coding example it will be hard for people to help.

That being said, I have a feeling you are trying to solve a problem that should not be done by the web-components implementation and should rather be solved by the user. For instance, if you need the component to have a minimum height in order to display the image and the details then give it a min-height (which is maybe set dynamically based on data) and it should be up to the user to use it correctly (i.e. put in a place that has enough space).

Or if you would like the component to somehow be responsive, I think you could maybe use the ResizeObserver API which would allow you to observe changes to the components width and height. Note that it is currently not available in Safari or IE. If you need to support those it would need a polyfill like this one.


问:
For C# logging, how do I obtain the call stack depth with minimal overhead?
I have created a wrapper for Log4net (which I may be dropping in favor of NLog; I haven't decided yet), and I indent the logged messages result to give an idea of calling structure. For example:

```<code>2011-04-03 00:20:30,271 [CT] DEBUG  -     Merlinia.ProcessManager.CentralThread.ProcessAdminCommand - ProcStart - User Info Repository
2011-04-03 00:20:30,271 [CT] DEBUG  -      Merlinia.ProcessManager.CentralThread.StartOneProcess - User Info Repository
2011-04-03 00:20:30,411 [CT] DEBUG  -       Merlinia.ProcessManager.CentralThread.SetProcessStatus - Process = User Info Repository, status = ProcStarting
2011-04-03 00:20:30,411 [CT] DEBUG  -        Merlinia.ProcessManager.CentralThread.SendProcessStatusInfo
2011-04-03 00:20:30,411 [CT] DEBUG  -         Merlinia.CommonClasses.MhlAdminLayer.SendToAllAdministrators - ProcessTable
2011-04-03 00:20:30,411 [CT] DEBUG  -          Merlinia.CommonClasses.MReflection.CopyToBinary
2011-04-03 00:20:30,411 [CT] DEBUG  -           Merlinia.CommonClasses.MReflection.CopyToBinary - False
2011-04-03 00:20:30,411 [CT] DEBUG  -          Merlinia.CommonClasses.MhlBasicLayer.SendToAllConnections - 228 - True - False
2011-04-03 00:20:30,411 [CT] DEBUG  -           Merlinia.CommonClasses.MmlNonThreaded.SendObject - 228
2011-04-03 00:20:30,411 [CT] DEBUG  -            Merlinia.CommonClasses.MllTcpSocket.SendMessage - 228 - True
2011-04-03 00:20:32,174 [10] DEBUG  -    Merlinia.CommonClasses.MReflection.CreateFromBinary
2011-04-03 00:20:32,174 [10] DEBUG  -     Merlinia.CommonClasses.MReflection.CopyFromBinary - Bytes = 71
2011-04-03 00:20:32,174 [CT] DEBUG  - Merlinia.ProcessManager.CentralThread.MessagingCallback - User Info Repository - ProcessInfoAndRequests
2011-04-03 00:20:32,174 [CT] DEBUG  -  Merlinia.ProcessManager.CentralThread.ProcessProcessInfoAndRequests - User Info Repository
</code>```

I do this using System.Diagnostics.StackTrace and counting StackFrames.

Now here's the question: Is there any more efficient way of doing this? I only need to determine the (relative) call stack depth, i.e., is the current depth plus or minus what it was the last time my logging wrapper was called. (Note that I do not actually use the StackFrame objects - I get the method names otherwise.)

I'm hoping for some simple high-performance way of querying the call stack depth or stack usage.

Body:
I have created a wrapper for Log4net (which I may be dropping in favor of NLog; I haven't decided yet), and I indent the logged messages result to give an idea of calling structure. For example:

2011-04-03 00:20:30,271 [CT] DEBUG  -     Merlinia.ProcessManager.CentralThread.ProcessAdminCommand - ProcStart - User Info Repository
2011-04-03 00:20:30,271 [CT] DEBUG  -      Merlinia.ProcessManager.CentralThread.StartOneProcess - User Info Repository
2011-04-03 00:20:30,411 [CT] DEBUG  -       Merlinia.ProcessManager.CentralThread.SetProcessStatus - Process = User Info Repository, status = ProcStarting
2011-04-03 00:20:30,411 [CT] DEBUG  -        Merlinia.ProcessManager.CentralThread.SendProcessStatusInfo
2011-04-03 00:20:30,411 [CT] DEBUG  -         Merlinia.CommonClasses.MhlAdminLayer.SendToAllAdministrators - ProcessTable
2011-04-03 00:20:30,411 [CT] DEBUG  -          Merlinia.CommonClasses.MReflection.CopyToBinary
2011-04-03 00:20:30,411 [CT] DEBUG  -           Merlinia.CommonClasses.MReflection.CopyToBinary - False
2011-04-03 00:20:30,411 [CT] DEBUG  -          Merlinia.CommonClasses.MhlBasicLayer.SendToAllConnections - 228 - True - False
2011-04-03 00:20:30,411 [CT] DEBUG  -           Merlinia.CommonClasses.MmlNonThreaded.SendObject - 228
2011-04-03 00:20:30,411 [CT] DEBUG  -            Merlinia.CommonClasses.MllTcpSocket.SendMessage - 228 - True
2011-04-03 00:20:32,174 [10] DEBUG  -    Merlinia.CommonClasses.MReflection.CreateFromBinary
2011-04-03 00:20:32,174 [10] DEBUG  -     Merlinia.CommonClasses.MReflection.CopyFromBinary - Bytes = 71
2011-04-03 00:20:32,174 [CT] DEBUG  - Merlinia.ProcessManager.CentralThread.MessagingCallback - User Info Repository - ProcessInfoAndRequests
2011-04-03 00:20:32,174 [CT] DEBUG  -  Merlinia.ProcessManager.CentralThread.ProcessProcessInfoAndRequests - User Info Repository

I do this using System.Diagnostics.StackTrace and counting StackFrames.

Now here's the question: Is there any more efficient way of doing this? I only need to determine the (relative) call stack depth, i.e., is the current depth plus or minus what it was the last time my logging wrapper was called. (Note that I do not actually use the StackFrame objects - I get the method names otherwise.)

I'm hoping for some simple high-performance way of querying the call stack depth or stack usage.

Body: Environment.StackTrace.Split(Environment.NewLine).Length


问:
Is there a fast way in resharper to switch between VS/IntelliJ keyboard shorts layout
I would like to know if there is a keyboard shortcut in resharper to switch between visual studio keyboard layout and intellij keyboard layout</p>

答: In Resharper options, under visual studio integration, you can set which keyboard layout you like and apply it by pressing "Apply Scheme"</p>