How to Write Some Data to Excel File(.Xlsx)

How to write some data to excel file(.xlsx)

Try this code

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
    //Start Excel and get Application object.
    oXL = new Microsoft.Office.Interop.Excel.Application();
    oXL.Visible = true;

    //Get a new workbook.
    oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
    oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

    //Add table headers going cell by cell.
    oSheet.Cells[1, 1] = "First Name";
    oSheet.Cells[1, 2] = "Last Name";
    oSheet.Cells[1, 3] = "Full Name";
    oSheet.Cells[1, 4] = "Salary";

    //Format A1:D1 as bold, vertical alignment = center.
    oSheet.get_Range("A1", "D1").Font.Bold = true;
    oSheet.get_Range("A1", "D1").VerticalAlignment =
        Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

    // Create an array to multiple values at once.
    string[,] saNames = new string[5, 2];

    saNames[0, 0] = "John";
    saNames[0, 1] = "Smith";
    saNames[1, 0] = "Tom";

    saNames[4, 1] = "Johnson";

    //Fill A2:B6 with an array of values (First and Last Names).
    oSheet.get_Range("A2", "B6").Value2 = saNames;

    //Fill C2:C6 with a relative formula (=A2 & " " & B2).
    oRng = oSheet.get_Range("C2", "C6");
    oRng.Formula = "=A2 & \" \" & B2";

    //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
    oRng = oSheet.get_Range("D2", "D6");
    oRng.Formula = "=RAND()*100000";
    oRng.NumberFormat = "$0.00";

    //AutoFit columns A:D.
    oRng = oSheet.get_Range("A1", "D1");
    oRng.EntireColumn.AutoFit();

    oXL.Visible = false;
    oXL.UserControl = false;
    oWB.SaveAs("c:\\test\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
        false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    oWB.Close();
    oXL.Quit();

    //...

How to write this dataframe to excel (xlsx)?

Here is one way -

• Split the data by name in list of dataframes.
• Get the max number of rows from the list.
• Append NA's to the dataframe with less number of rows and combine them together.
• Write to Excel with no column names.

df <- data.frame(name = c('car', 'car', 'van', 'bus', 'bus', 'bus'), 
                 index = c(1, 2, 1, -1, 0, 1), 
                 value = c(10, 20, 15, 20, 25, 25))

tmp <- split(df, df$name)
n <- 1:max(sapply(tmp, nrow))

writexl::write_xlsx(do.call(cbind, lapply(tmp, `[`, n, )), 
                    'result.xlsx', col_names = FALSE)

This is how it looks in Excel.

How to write a data into an exist sheet of excel file by Python?

This is minmal example:

import openpyxl

wbkName = 'New.xlsx'
wbk = openpyxl.load_workbook(wbkName)
wks = wbk['test1']
someValue = 1337
wks.cell(row=10, column=1).value = someValue
wbk.save(wbkName)
wbk.close

The saving with the explicit name of the workbook seems to be quite important - wbk.save(wbkName), because only wbk.save does not do the job completely, but does not throw an error.

C# Export Data Table to .xlsx file with three worksheets

Using EPPlus library

                 using (ExcelPackage excel = new ExcelPackage())
                {
                    excel.Workbook.Worksheets.Add("Tab1"); // Create first tab
                    excel.Workbook.Worksheets.Add("Tab2");//Create second tab
                    excel.Workbook.Worksheets.Add("Tab3");//Create third tab
                    var excelWorksheet = excel.Workbook.Worksheets["Tab2"];
                    //Set value for 1 cell in 1 row in Tab2
                    excelWorksheet.Cells[1, 1].Value = "Some text";
                    //Simple aligment and fond for this cell
                    excelWorksheet.Cells[1, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    excelWorksheet.Cells[1, 1].Style.Font.Bold = true;
                    //adding data to cells from dataTable in the loop
                    foreach (DataRow row in dataTable)
                    {
                        excelWorksheet.Cells[position, 1].Value = row["*column_name*"].ToString();
                    }

                }

Or instead of seting data in loop you may just load all dataTable by calling LoadFromDataTable method

excelWorksheet.Cells[1, 1].LoadFromDataTable(dataTable, true);

At the end call excel.GetAsByteArray() to get your file as byte array or call
excel.SaveAs(...) to save it psychically on your hdd

Not able to write data in xlsx file using java?

ArrayList<String> head = new ArrayList<String>();

head.add("Register Number");
head.add(subject1);
head.add(subject2);  // subject1 and subject2 are variable i created

System.out.println(head.get(0));  // To check if my list has value
System.out.println(head.get(1));  // To check if my list has value
System.out.println(head.get(2));  // To check if my list has value

XSSFWorkbook workbook = new XSSFWorkbook();                               
XSSFSheet sheet1 = wb.createSheet("Sheet1");

for (int r = 0; r < head.size(); r++) 
{

     XSSFRow row  = sheet1.createRow(r);
     XSSFCell cell = row.createCell(0);                          
     cell.setCellValue(head.get(r));                             
     sheet1.autoSizeColumn(0);
}

// Write this workbook to a FileOutputStream.
FileOutputStream fileOut = new FileOutputStream("/home/st.xlsx"); 
workbook.write(fileOut);
fileOut.flush();
fileOut.close();

More info here:
https://gist.github.com/madan712/3912272

---

Related Topics