Coder Social home page Coder Social logo

ericxin1982 / importexcel Goto Github PK

View Code? Open in Web Editor NEW

This project forked from dfinke/importexcel

0.0 0.0 0.0 75.81 MB

PowerShell module to import/export Excel spreadsheets, without Excel

Home Page: https://www.powershellgallery.com/packages/ImportExcel/

License: Apache License 2.0

PowerShell 100.00%

importexcel's Introduction

PowerShell and Excel


Has the ImportExcel module helped you?

  • Made you look good to the boss?
  • Saved you time?
  • Made you more productive?

Consider donating. Thank you!

Donate



Build Status Donate

Overview

Automate Excel with PowerShell without having Excel installed. Works on Windows, Linux and Mac. Creating Tables, Pivot Tables, Charts and much more just got a lot easier.

Examples ✨

Check out the more than 100 examples on ways to create amazing reports as well as make you more productive with PowerShell and Excel.

Basic Usage

Installation

Install-Module -Name ImportExcel

Create a spreadsheet

Here is a quick example that will create spreadsheet file from CSV data. Works with JSON, Databases, and more.

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$data | Export-Excel .\salesData.xlsx

Read a spreadsheet

Quickly read a spreadsheet document into a PowerShell array.

$data = Import-Excel .\salesData.xlsx

$data
Region State        Units Price
------ -----        ----- -----
West   Texas        927   923.71
North  Tennessee    466   770.67
East   Florida      520   458.68
East   Maine        828   661.24
West   Virginia     465   053.58
North  Missouri     436   235.67
South  Kansas       214   992.47
North  North Dakota 789   640.72
South  Delaware     712   508.55

Add a chart to spreadsheet

Chart generation is as easy as 123. Building charts based on data in your worksheet doesn't get any easier.

Plus, it is automated and repeatable.

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$chart = New-ExcelChartDefinition -XRange State -YRange Units -Title "Units by State" -NoLegend

$data | Export-Excel .\salesData.xlsx -AutoNameRange -ExcelChartDefinition $chart -Show

Add a pivot table to spreadsheet

Categorize, sort, filter, and summarize any amount data with pivot tables. Then add charts.

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$data | Export-Excel .\salesData.xlsx -AutoNameRange -Show -PivotRows Region -PivotData @{'Units'='sum'} -PivotChartType PieExploded3D

Convert Excel data to other formats

Create a separate CSV file for each Excel sheet

Do you have an Excel file with multiple sheets and you need to convert each sheet to CSV file?

Problem Solved

The yearlyRetailSales.xlsx has 12 sheets of retail data for the year.

This single line of PowerShell converts any number of sheets in an Excel workbook to separate CSV files.

(Import-Excel .\yearlyRetailSales.xlsx *).GetEnumerator() |
ForEach-Object { $_.Value | Export-Csv ($_.key + '.csv') }

Additional Resources

Videos

More Videos

Articles

Title Author Twitter
More tricks with PowerShell and Excel James O'Neill @jamesoneill
Using the Import-Excel module: Part 1 Importing James O'Neill @jamesoneill
Using the Import Excel module part 2: putting data into .XLSx files James O'Neill @jamesoneill
Using the import Excel Module: Part 3, Pivots and charts, data and calculations James O'Neill @jamesoneill
Export AdventureWorksDW2017 to Excel for a Power BI Demo with Export-Excel Aaron Nelson @sqlvariant
Creating beautiful Powershell Reports in Excel Doug Finke @dfinke
PowerShell Excel and Conditional Formatting Doug Finke @dfinke
Learn to Automate Excel like a Pro with PowerShell Doug Finke @dfinke

Contributing

Contributions are welcome! Open a pull request to fix a bug, or open an issue to discuss a new feature or change.

Original README.md

importexcel's People

Contributors

dfinke avatar jhoneill avatar darklite1 avatar royashbrook avatar ili101 avatar davishenckel avatar thkn-hofa avatar stahler avatar kkazala avatar mikeybronowski avatar steve-daedilus avatar muschebubusche avatar francoislg avatar joshooaj avatar scrthq avatar uslackr avatar sqlvariant avatar attilamihalicz avatar headsphere avatar dbrennand avatar pauldalewilliams avatar redoz avatar nzubair avatar justingrote avatar zippy1981 avatar jamesmmueller avatar conradagramont avatar clsmith70 avatar briantist avatar sporkabob avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.