Tag: Excel

How to iterate through data and write to excel file quicker with C# and openXML

I have a pretty simple method that grabs data from an sql query and then puts it inside of a list and finally iterates through two for each loops onto an excel file. It works fine with small amounts of data, but not at all with larger amounts. What would be the best way to iterate and also write to my workbook the quickest? I am using OpenXML.

Here is the code

  private UInt32 AddSheets(SpreadsheetDocument document, WorkbookPart workbookPart, Sheets sheets, string sheetName, UInt32 sheetNumber, List<RentalFeeReportingRecord> currentRecordList)
    {
        Dictionary<int, string> listofCoreBankingIds = new Dictionary<int, string>();
        listofCoreBankingIds = currentRecordList.Select(x => new { id = x.CoreBankingId, regionName = x.RegionName }).Distinct().ToDictionary(y => y.id, y => y.regionName);
        foreach (var coreBankingId in listofCoreBankingIds.OrderBy(x => x.Value))
        {

            WorksheetPart worksheetPart1 = workbookPart.AddNewPart<WorksheetPart>();
            string currentCurrency = null;
            string regionName = null;

            Worksheet worksheet1 = new Worksheet();
            SheetData sheetData1 = new SheetData();

            Row rowInSheet1 = new Row();
            Row firstRow = new Row();
            Row lastRow = new Row();

            rowInSheet1.Append(
              excelController.ConstructCell("Region", CellValues.String, 3), excelController.ConstructCell("Merchant - Terminal", CellValues.String, 3),
              excelController.ConstructCell("Fee Charged", CellValues.String, 3),
              excelController.ConstructCell("Currency", CellValues.String, 3),
              excelController.ConstructCell("Processing Date", CellValues.String, 3),
              excelController.ConstructCell("Description", CellValues.String, 3)
            );

            sheetData1.Append(rowInSheet1);


            foreach (var currentRecord in currentRecordList.OrderBy(x => x.RegionName).ThenBy(z => z.Currency).ThenBy(y => y.DateProcessed))
            {
                if (coreBankingId.Key == currentRecord.CoreBankingId)
                {

                    regionName = currentRecord.RegionName;
                    currentCurrency = currentRecord.Currency;
                    double currentTotal = currentRecord.TotalFeeAmount;
                    string merchant = currentRecord.MerchantRecord.MerchantID + " - " + currentRecord.MerchantTerminalRecord.terminalID;

                    if (currentRecord.IsPinPad)
                    {
                        merchant = merchant + " - PIN Pad";
                    }

                    string description = currentRecord.TerminalRecord.description;
                    if (currentRecord.IsProRated)
                    {
                        int days = DateTime.DaysInMonth(currentRecord.DeployedDate.Year, currentRecord.DeployedDate.Month);

                        description = currentRecord.TerminalRecord.description + " (" + currentRecord.MonthlyFee + " Fee \ " + days.ToString() + " Days) * " + currentRecord.DaysActive.ToString() + " Days Active";
                    }

                    rowInSheet1 = new Row();
                    firstRow = new Row();
                    lastRow = new Row();

                    rowInSheet1.Append(
                      excelController.ConstructCell(regionName, CellValues.String, 2),
                      excelController.ConstructCell(merchant, CellValues.String, 2),
                      excelController.ConstructCell(currentRecord.TotalFee.ToString("N2"), CellValues.String, 1),
                      excelController.ConstructCell(currentRecord.Currency, CellValues.String, 2),
                      excelController.ConstructCell(currentRecord.DateProcessed.ToString("MMM dd, yyyy"), CellValues.String, 2),
                      excelController.ConstructCell(description, CellValues.String, 2)
                    );

                    sheetData1.Append(rowInSheet1);

                }

            }


            Columns columns = excelController.AutoSize(sheetData1);

            worksheet1.AppendChild(columns);
            worksheet1.AppendChild(sheetData1);
            worksheetPart1.Worksheet = worksheet1;


            Sheet sheet1 = new Sheet()
            {

                Id = document.WorkbookPart.GetIdOfPart(worksheetPart1),
                SheetId = sheetNumber,
                Name = regionName
            };

            sheets.Append(sheet1);
            sheetNumber++;
        }
        return sheetNumber;

    }

Any help would be appreciated.

worksheet function – Excel data query

I’m trying to build out a budget monitoring spreadsheet. I want to pull sum of entries by category and based on the month. The data is in rows as follows and will grow each month as I download our spend.

Date Category Amount
03-15-21 Auto 500.00
02-12-21 House 375.00

Ultimately I’ll have it displayed with months across columns and categories in rows so we can monitor our budget.

How can I pull out the data by category and month?

I’ve looked at sumifs, sumproduct, index match, haven’t found a solution. I appreciate any guidance.

excel – Best way to copy data to a new sheet and reorganize it (VBA)

I’m writing a VBA program which copies and organizes data from one master sheet into numerous other sheets. One of the recipient sheets unifies all the data from the master sheet which holds the same id number into a single row. For this operation, I am looping through the master sheet for each id number, copying each row which holds the current id number into a new sheet purely used for calculations and organizing, and rearranging the data in this sheet into the new row. The resultant row is copied into the recipient sheet. This process of organizing data for every id number takes a long time to process, especially given the very large size of this sheet and the processing time of the other recipient sheets. I’m wondering if there is a better way to organize and copy data without using an intermediate calculation sheet.

The below code is the main sub, which calls another sub OrganizeAndCopyToPal, which organizes the data in the calculation sheet and copies the result into the recipient sheet.

Sub PalletAssemblyLog()

    Dim allidNum As Range
    Dim curridNum As Range
    Dim rowCount As Long
    Dim idNum
    Dim I As Long
    Dim j As Long
    Dim machineLoc As String
    
    Dim calc As Worksheet
    Dim full As Worksheet
    Dim pal As Worksheet
    Set calc = Sheet3
    Set full = Sheet4
    Set pal = Sheet1
    
    For I = 2 To rowCount
        idNum = full.Cells(I, 17).Value
        For j = 2 To rowCount
            If full.Cells(j, 17).Value = idNum Then
                If allidNum Is Nothing Then
                    Set allidNum = full.Cells(j, 17)
                Else
                    Set allidNum = Union(allidNum, full.Cells(j, 17))
                End If
            End If
        Next j
            
        Set curridNum = allidNum.EntireRow
        
        calc.Activate
        calc.Cells.Clear
        
        full.Activate
        curridNum.Copy calc.Range("A1")
        
        OrganizeAndCopyToPal curridNum
    Next I
End Sub

The below sub organizes and copies the data for each id number. The final sub to copy the data isn’t related to the matter of simplifying this task so I’m not including it.

Sub OrganizeAndCopyToPal(curridNum)
    
    Dim calc As Worksheet
    Dim pal As Worksheet
    Set calc = Sheet3
    Set pal = Sheet1
    
    calc.Activate
    
    Dim rowCount As Long
    rowCount = calc.Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim palRow As Long
    palRow = rowCount + 2
    Dim partRow As Long
    partRow = palRow + 2
    
    Dim currPartCount As Range
    
    Dim assembly As String
    Dim id As String
    Dim location As String
    Dim machType As String
    Dim machLoc As String
    Dim currPart As String
    Dim link As String
    Dim tot As Long
    tot = 0
    
    With calc
        .Cells(1, 1).Copy .Cells(palRow, 2)
        assembly = .Cells(1, 1).Value
        
        .Cells(1, 2).Copy .Cells(palRow, 5)
        
        id = .Cells(1, 17).Value
        
        asArray = SplitMultiDelims(id, "|-")
        'MsgBox asArray(0) & " " & asArray(1) & " " & asArray(2)
        machArray = Split(.Cells(1, 8), "-")
        machType = machArray(0)
        .Cells(palRow, 3) = machType
        
        machLoc = .Cells(1, 8).Value
        .Cells(palRow, 4) = machLoc
        
        .Cells(1, 17).Copy .Cells(palRow, 10)

        location = Cells(1, 9)
        .Cells(palRow, 1) = location
        
        For I = 1 To rowCount
            partArray = Split(.Cells(I, 16).Value, ",")
            For j = 0 To UBound(partArray)
                partArray2 = Split(partArray(0), "-")
                partPrefix = partArray2(0)
                If j = 0 Then
                    currPart = partArray(j)
                Else
                    currPart = partPrefix & "-" & CStr(partArray(j))
                End If
                tf = 1
                For k = 0 To tot
                    If Cells(partRow + k, 1).Value = currPart Then
                        tf = 0
                        Exit For
                    End If
                Next k
                If tf = 1 Then
                    .Cells(partRow + tot, 1).Value = currPart
                    tot = tot + 1
                End If
            Next j
        Next I
        
        For I = 1 To tot
            Cells(palRow, 10 + I).Value = Cells(partRow + I - 1, 1)
        Next I
        
    End With
    
    CopyToPal curridNum, palRow
    
End Sub

Thank you for any tips or help that you can offer.

MACRO COPIA E COLA VBA EXCEL – AJUDA

Bom dia amigos, antes de vir pedir a ajuda eu posso dizer que literalmente quase quebrei a cabeça

Estou aprendendo VBA para agilizar meu serviço na empresa, mas estou travado na seguinte parte:

Estava desenvolvendo uma macro que na teoria faria o seguinte caminho:
1° Moveria o mouse até a célula A2
2° Copiaria o conteudo da celular A2
3° Colaria esse conteúdo em um bloco de notas especifico
4°Fim
5°Como ele está na celula A2, a posição do mouse se moveria para barra e daria 1 clique, movendo para Célula A3 e fazendo o loop no processo.

Consegui faze o processo de mouse, tecla de atalho própria. Porém quando chega na parte de copiar, ele esta selecionando 2 celulas e consequentemente dando erro por nao conseguir fazer 2 celulas ao memso tempo… Além do CTRL V que não está funcionando…

Enfim, se alguem puder me ajudar ou desenvolver para mim seria muito grato. Me ajudaria a ver onde estaria errando e me ajudaria no aprendizado.
inserir a descrição da imagem aqui

microsoft excel – Line graph of a single Cell that is dynamically changing

I have one cell in Excel that is simply the subtraction result of two other cells. The subtraction number is dynamic i.e. it is continually changing throughout the day based on the values of the two original cells.
I want to plot a line graph that keeps track on the chart of every single number change in the subtraction cell. How do I do that.
Currently I can create a simple line chart but it plots ONLY the number currently showing in the cell. i.e when the number changes, the graph doesnt keep the previous number and so on…

excel – MS Flow stops working when I update the file in SharePoint

I have a MS Flow that updates a list in SharePoint from an Excel file that I manually upload.
Whenever I upload a new file, the MS Flow stops working, see, inside the Excel file I have a table, that I pull from another system, then the file is uploaded to a site inside SharePoint. Every time I upload a new file I have to update the MS Flow to grab the “new” file, the file is always named the same as the previous one.

This is the error that shows every time I update the file:

No table was found with the name '{235AC59A-72C7-4A62-B137-00E0B394AB3E}'.
clientRequestId: xxx-xx-xxx-xxx-xxx
serviceRequestId: xxx-xx-xx-xxx-xxx

The table is named “Table 1” before the file is updated, after that, it is shown as below.

enter image description here

do excel data entry, web research, convert pdf to word excel for $5

do excel data entry, web research, convert pdf to word excel

Welcome To my Gig

Hello, My name is samiul and I am Certified with Microsoft workd Specialist. I am an expert data entry worker. I provide you a quality services in this field.

If you need to collect data from any site OR any web directory OR any

document like PDF, word or scanned file, You can choose me.

My services includes

✔ Excel data entry

✔ Typing work

✔ Copy typing

✔ Punjabi typing

✔ Excel Spreadsheet

✔ Excel VBA

✔ PowerPoint Presentation design
EXPERTISE

✔ Data Entry

✔ Document formatting

✔ Microsoft office

✔ Document editing

✔ Typing

✔ Microsoft excel

✔ Data mining

✔ Virtual assistant

✔ Data collection

✔ Word to pdf
✔✔✔Available 24/7

I can deliver your order within the deadline with 100% accuracy.

I would love to hear about your project’s requirements.

Just leave me a message.

.