Rupert  Beatty

Rupert Beatty

1666801740

HGRippleRadarView: A Beautiful Radar View to Show Nearby Items

HGRippleRadarView

Example

radar_example_1.gif radar_example_2.gif ripple_example.gif

To run the example project, clone the repo, and run pod install from the Example directory first.
This project is inspired by: https://dribbble.com/shots/2242921-Find-Nearby-Users-Concept

Requirements

  • iOS 8.0+
  • Xcode 9.2

You also may like

  • HGCircularSlider - A custom reusable circular slider control for iOS application.
  • HGPlaceholders - Nice library to show placeholders and Empty States for any UITableView/UICollectionView in your project Edit

Installation

HGRippleRadarView is available through CocoaPods. To install it, simply add the following line to your Podfile:

pod 'HGRippleRadarView'

HGRippleRadarView is also available through Carthage. To install it, simply add the following line to your Cartfile:

github "HamzaGhazouani/HGRippleRadarView"

Usage

  1. Change the class of a view from UIView to RippleView or RadarView
  2. Programmatically:
let rippleView = RippleView(frame: myFrame)
let radarView = RadarView(frame: myFrame)

Customization

RippleView

diskRadius

The radius of the central disk in the view, if you would like to hide it, you can set the radius to 0

diskColor

The color of the central disk in the view, the default color is ripplePink color

minimumCircleRadius

This property make distance between the first circle and the central disk

numberOfCircles

The number of circles to draw around the disk, the default value is 3

paddingBetweenCircles

The padding between circles, circles could be drawn outside the frame

circleOffColor

The color of the off status of the circle, used for animation

circleOnColor

The color of the on status of the circle, used for animation

animationDuration

The duration of the animation, the default value is 0.9


You can start/ stop the animation at any time by calling `startAnimation()` & `stopAnimation()`

RadarView

paddingBetweenItems

The padding between items, the default value is 10

Add items

If you would like to add one item, use the method add(item:using:) If you would like to add multiple items, it's recommended to use the method add(items:using:)

remove item

If you would like to remove an item, use the method remove(item:)

Custom item

If you would like to customize items, use the protocol RadarViewDataSource and implement:

radarView?.dataSource = self 
...
func radarView(radarView: RadarView, viewFor item: Item, preferredSize: CGSize) -> UIView {
        let myCustomItemView = UIView(frame: CGRect(x: 0, y: 0, width: preferredSize.width, height: preferredSize.height))
        return myCustomItemView
}

CallBack

If you would like to receive action on items, use the protocol RadarViewDelegate and implement:

radarView?.delegate = self 
...
 func radarView(radarView: RadarView, didSelect item: Item) {
        print(item.uniqueKey)
}

Documentation

Full documentation is available on CocoaDocs.
You can also install documentation locally using jazzy.

Download Details:

Author: HamzaGhazouani
Source Code: https://github.com/HamzaGhazouani/HGRippleRadarView 
License: MIT license

#swift #animation #location 

HGRippleRadarView: A Beautiful Radar View to Show Nearby Items

Flutter Background Location Tracker Plugin (Android / IOS)

location_track

Plugin for tracking location in background.

Getting Started

Please see example for more details.

IOS

  1. Add below code in AppDelegate.swift

func registerPlugins(registry: FlutterPluginRegistry) {
    GeneratedPluginRegistrant.register(with: registry)
}


  UCLocationPlugin.setPluginRegistrantCallback(registerPlugins)
  
  1. Add below changes in Capabilities

1

2

3

Use this package as a library

Depend on it

Run this command:

With Flutter:

 $ flutter pub add uclocation

This will add a line like this to your package's pubspec.yaml (and run an implicit flutter pub get):

dependencies:
  uclocation: ^0.0.2

Alternatively, your editor might support flutter pub get. Check the docs for your editor to learn more.

Import it

Now in your Dart code, you can use:

import 'package:uclocation/uclocation.dart'; 

example/lib/main.dart

import 'dart:async';
import 'dart:ffi';
import 'dart:isolate';
import 'dart:ui';
import 'package:uclocation/uclocation.dart';
import 'package:uclocation/location_dto.dart';
import 'package:uclocation/settings/android_settings.dart';
import 'package:uclocation/settings/ios_settings.dart';
import 'package:uclocation/settings/locator_settings.dart';
import 'package:flutter/material.dart';
import 'package:permission_handler/permission_handler.dart';


import 'file_manager.dart';
import 'location_callback_handler.dart';
import 'location_service_repository.dart';

void main() {
  /// Before init Location Plugin
  WidgetsFlutterBinding.ensureInitialized();
  runApp(MyApp());
}

class MyApp extends StatefulWidget {
  @override
  _MyAppState createState() => _MyAppState();
}

class _MyAppState extends State<MyApp> {
  ReceivePort port = ReceivePort();

    String?logStr = '';
    bool isRunning = false;
    LocationDto? lastLocation;

  @override
  void initState() {
    super.initState();

    if (IsolateNameServer.lookupPortByName(
            LocationServiceRepository.isolateName) !=
        null) {
      IsolateNameServer.removePortNameMapping(
          LocationServiceRepository.isolateName);
    }

    IsolateNameServer.registerPortWithName(
        port.sendPort, LocationServiceRepository.isolateName);

    port.listen(
      (dynamic data) async {
        if(data != null && data != 'Null') {
          await updateUI(data);
        }
      },
    );
    initPlatformState();
  }

  @override
  void dispose() {
    super.dispose();
  }

  Future<void> updateUI(LocationDto data) async {
    final log = await FileManager.readLogFile();

    await _updateNotificationText(data);

    setState(() {
      if (data != null) {
        lastLocation = data;
      }
      logStr = log;
    });
  }

  Future<void> _updateNotificationText(LocationDto data) async {
    if (data == null) {
      return;
    }

    await UClocation.updateNotificationText(
        title: "new location received",
        msg: "${DateTime.now()}",
        bigMsg: "${data.latitude}, ${data.longitude}");
  }

  Future<void> initPlatformState() async {
    print('Initializing...');
    await UClocation.initialize();
    logStr = await FileManager.readLogFile();
    print('Initialization done');
    final _isRunning = await UClocation.isServiceRunning();
    setState(() {
      isRunning = _isRunning;
    });
    print('Running ${isRunning.toString()}');
  }

  @override
  Widget build(BuildContext context) {
    final start = SizedBox(
      width: double.maxFinite,
      child: ElevatedButton(
        child: Text('Start'),
        onPressed: () {
          _onStart();
        },
      ),
    );
    final stop = SizedBox(
      width: double.maxFinite,
      child: ElevatedButton(
        child: Text('Stop'),
        onPressed: () {
          onStop();
        },
      ),
    );
    final clear = SizedBox(
      width: double.maxFinite,
      child: ElevatedButton(
        child: Text('Clear Log'),
        onPressed: () {
          FileManager.clearLogFile();
          setState(() {
            logStr = '';
          });
        },
      ),
    );
    String msgStatus = "-";
    if (isRunning != null) {
      if (isRunning) {
        msgStatus = 'Is running';
      } else {
        msgStatus = 'Is not running';
      }
    }
    final status = Text("Status: $msgStatus");

    final log = Text(
      logStr!,
    );

    return MaterialApp(
      home: Scaffold(
        appBar: AppBar(
          title: const Text('Flutter background Locator'),
        ),
        body: Container(
          width: double.maxFinite,
          padding: const EdgeInsets.all(22),
          child: SingleChildScrollView(
            child: Column(
              crossAxisAlignment: CrossAxisAlignment.center,
              children: <Widget>[start, stop, clear, status, log],
            ),
          ),
        ),
      ),
    );
  }

  void onStop() async {
    await UClocation.unRegisterLocationUpdate();
    final _isRunning = await UClocation.isServiceRunning();
    setState(() {
      isRunning = _isRunning;
    });
  }

  void _onStart() async {
    if (await _checkLocationPermission()) {
      await _startLocator();
      final _isRunning = await UClocation.isServiceRunning();

      setState(() {
        isRunning = _isRunning;
        lastLocation = null;
      });
    } else {
      // show error
    }
  }

  Future<bool> _checkLocationPermission() async {
    var permission =await Permission.location.status;
    if(permission.isDenied){
      permission = await Permission.location.request();
    }
     if(permission.isGranted){
      return true;
    }
    else{
      return false;
    }
  }

  Future<void> _startLocator() async{
    Map<String, dynamic> data = {'countInit': 1};
    return await UClocation.registerLocationUpdate(LocationCallbackHandler.callback,
        initCallback: LocationCallbackHandler.initCallback,
        initDataCallback: data,
        disposeCallback: LocationCallbackHandler.disposeCallback,
        iosSettings: const IOSSettings(
            accuracy: LocationAccuracy.NAVIGATION, distanceFilter: 0),
        autoStop: false,
        androidSettings: const AndroidSettings(
            accuracy: LocationAccuracy.NAVIGATION,
            interval: 5,
            distanceFilter: 0,
            client: LocationClient.google,
            androidNotificationSettings: AndroidNotificationSettings(
                notificationChannelName: 'Location tracking',
                notificationTitle: 'Start Location Tracking',
                notificationMsg: 'Track location in background',
                notificationBigMsg:
                    'Background location is on to keep the app up-tp-date with your location. This is required for main features to work properly when the app is not running.',
                notificationIconColor: Colors.grey,
                notificationTapCallback:
                    LocationCallbackHandler.notificationCallback)));
  }
} 

Download Details:

Author: Ankursohal007

Source Code: https://github.com/Ankursohal007/location_track

#flutter #location #android #ios 

Flutter Background Location Tracker Plugin (Android / IOS)

A Map Location Plugin for Flutter

高德地图定位flutter组件。

目前实现获取定位和监听定位功能。

1、申请一个key http://lbs.amap.com/api/ios-sdk/guide/create-project/get-key

直接在dart文件中设置key

ios

  1. 在info.plist中增加:
<key>NSLocationWhenInUseUsageDescription</key>
<string>要用定位</string>
  1. iOS 9及以上版本使用后台定位功能, 需要保证"Background Modes"中的"Location updates"处于选中状态

3.使用地理围栏

iOS14及以上版本使用地理围栏功能,需要在plist中配置NSLocationTemporaryUsageDescriptionDictionary字典描述, 且添加自定义Key描述地理围栏的使用场景,此描述会在申请临时精确定位权限的弹窗中展示。 该回调触发条件:拥有定位权限,但是没有获得精确定位权限的情况下,会触发该回调。此方法实现调用申请临时精确定位权限API即可;

** 需要注意,在iOS9及之后版本的系统中,如果您希望程序在后台持续检测围栏触发行为,需要保证manager的 allowsBackgroundLocationUpdates 为 YES, 设置为YES的时候必须保证 Background Modes 中的 Location updates 处于选中状态,否则会抛出异常。

开始使用

高德定位功能

  • 设置key

Future<void> main() async {
  WidgetsFlutterBinding.ensureInitialized();

  final bool key = await setAMapKey(
      iosKey: 'ios key',
      androidKey: 'android key');

  if (key != null && key) print('高德地图ApiKey设置成功');

  runApp(MaterialApp(
      debugShowCheckedModeBanner: false, title: 'FlAMap', home: Home()));
}

  • 初始化定位参数
  Future<void> initialize() async {
    /// 获取权限
    if (getPermissions) return;

    /// 初始化AMap
    final bool data = await FlAMapLocation().initialize(AMapLocationOption());
    if (data) {
      show('初始化成功');
    }
  }

  • 单次获取定位
  Future<void> getLocation() async {
     /// 务必先初始化 并获取权限
    if (getPermissions) return;
    AMapLocation location =  await FlAMapLocation().getLocation(true);
  }

  • 开启定位变化监听
  Future<void> startLocationChange() async {
     /// 务必先初始化 并获取权限
    if (getPermissions) return;
    final bool data =
        await FlAMapLocation().startLocationChanged(onLocationChanged: 
            (AMapLocation location) {
      locationState.value = location;
      text.value = '位置更新$i次';
    });
   print(!data ? '开启成功' : '开启失败');
  }

  • 关闭定位变化监听
  void stopLocation(){
  FlAMapLocation().stopLocation();
  }
  • 关闭定位服务
  void dispose() {
    super.dispose();
    FlAMapLocation().dispose();
  }

高德地理围栏功能

  • 初始化地理围栏

  Future<void> get initialize async {
    final bool data = await FlAMapGeoFence().initialize(GeoFenceActivateAction.stayed);
    if (data) {
     show('初始化地理围栏:$data');
    }
  }

  • 关闭围栏服务
  void dispose() {
    super.dispose();
    FlAMapGeoFence().dispose();
  }
  • 根据POI添加围栏
  Future<void> addPOI() async {
  final AMapPoiModel model = AMapPoiModel(
                              keyword: '首开广场',
                              poiType: '写字楼',
                              city: '北京',
                              size: 1,
                              customId: '000FATE23(考勤打卡)');
  final bool state = await FlAMapGeoFence().addPOI(model);
  }
  • 根据坐标关键字添加围栏
  Future<void> addLatLong() async {
  final LatLong latLong = LatLong(39.933921, 116.372927);
                          final AMapLatLongModel model = AMapLatLongModel(
                              latLong: latLong,
                              keyword: '首开广场',
                              poiType: '',
                              customId: '000FATE23(考勤打卡)',
                              size: 20,
                              aroundRadius: 1000);
    final bool state = await FlAMapGeoFence().addLatLong(model);
  }
  • 添加行政区划围栏
  Future<void> addDistrict() async {
  final bool state = await FlAMapGeoFence().addDistrict(
                               keyword: '海淀区', customId: '000FATE23(考勤打卡)');
  }
  • 添加圆形围栏
  Future<void> addCircle() async {
  final LatLong latLong = LatLong(30.651411, 103.998638);
  final bool state = await FlAMapGeoFence().addCircle(
                              latLong: latLong,
                              radius: 10,
                              customId: '000FATE23(考勤打卡)');
  }
  • 添加多边形围栏
  Future<void> addCustom() async {
  final bool state = await FlAMapGeoFence().addCustom(latLongs: <LatLong>[
                            LatLong(39.933921, 116.372927),
                            LatLong(39.907261, 116.376532),
                            LatLong(39.900611, 116.418161),
                            LatLong(39.941949, 116.435497),
                          ], customId: '000FATE23(考勤打卡)');
  }
  • 获取所有围栏信息
  Future<void> getAll() async {
  /// 传入 customID 获取指定标识的围栏信息 仅支持ios
  final List<AMapGeoFenceModel> data = await FlAMapGeoFence().getAll();
  }
  • 删除地理围栏
  Future<void> remove() async {
  /// 传入 customID 删除指定标识的围栏
  /// 不传 删除所有围栏
  final bool state = await FlAMapGeoFence().remove();
  }
  • 暂停监听围栏
  Future<void> pause() async {
  /// 传入 customID 暂停指定标识的围栏
  /// 不传 暂停所有围栏
  final bool state = await FlAMapGeoFence().pause();
  }
  • 开始监听围栏
  Future<void> start() async {
  /// 传入 customID 开始指定标识的围栏
  /// 不传 开始所有围栏
  final bool state = await FlAMapGeoFence().start();
  }

Use this package as a library

Depend on it

Run this command:

With Flutter:

 $ flutter pub add fl_amap

This will add a line like this to your package's pubspec.yaml (and run an implicit flutter pub get):

dependencies:
  fl_amap: ^2.3.0

Alternatively, your editor might support flutter pub get. Check the docs for your editor to learn more.

Import it

Now in your Dart code, you can use:

import 'package:fl_amap/fl_amap.dart'; 

example/lib/main.dart

import 'package:example/geo_fence_page.dart';
import 'package:example/loaction_page.dart';
import 'package:fl_amap/fl_amap.dart';
import 'package:flutter/cupertino.dart';
import 'package:flutter/material.dart';
import 'package:flutter_waya/flutter_waya.dart';
import 'package:permission_handler/permission_handler.dart';

Future<void> main() async {
  WidgetsFlutterBinding.ensureInitialized();
  final bool key = await setAMapKey(
      iosKey: 'e0e98395277890e48caa0c4bed423ead',
      androidKey: '77418e726d0eefc0ac79a8619b5f4d97',
      isAgree: true,
      isContains: true,
      isShow: true);
  debugPrint('高德地图ApiKey设置$key');
  runApp(const ExtendedWidgetsApp(title: 'FlAMap', home: App()));
}

class App extends StatelessWidget {
  const App({Key? key}) : super(key: key);

  @override
  Widget build(BuildContext context) {
    return ExtendedScaffold(
        appBar: AppBar(title: const Text('高德地图')),
        mainAxisAlignment: MainAxisAlignment.center,
        crossAxisAlignment: CrossAxisAlignment.center,
        children: <Widget>[
          ElevatedText(
              onPressed: () => showCupertinoModalPopup<dynamic>(
                  context: context, builder: (_) => const AMapLocationPage()),
              text: '高德定位功能'),
          ElevatedText(
              onPressed: () => showCupertinoModalPopup<dynamic>(
                  context: context, builder: (_) => const AMapGeoFencePage()),
              text: '高德地理围栏功能'),
        ]);
  }
}

class ElevatedText extends StatelessWidget {
  const ElevatedText({Key? key, required this.text, required this.onPressed})
      : super(key: key);

  final String text;
  final VoidCallback onPressed;

  @override
  Widget build(BuildContext context) =>
      ElevatedButton(onPressed: onPressed, child: Text(text));
}

Future<bool> getPermission(Permission permission) async {
  final PermissionStatus status = await permission.request();
  if (!status.isGranted) {
    await openAppSettings();
    return await permission.request().isGranted;
  }
  return status.isGranted;
} 

Download Details:

Author: Wayaer

Source Code: https://github.com/Wayaer/fl_amap

#map #location #flutter 

A Map Location Plugin for Flutter

Configure la automatización de Python Excel en 5 sencillos pasos

Excel es una aplicación de hoja de cálculo desarrollada por Microsoft en el año 1987. Es compatible oficialmente con casi todos los sistemas operativos como Windows, Macintosh, Android, etc. Viene preinstalado con el sistema operativo Windows y se puede integrar fácilmente con otras plataformas de SO. Microsoft Excel es la mejor y más accesible herramienta cuando se trata de trabajar con datos estructurados.

En el tutorial de hoy, aprenderemos los pasos para configurar Python Excel Automation. Puede seguir los pasos a continuación para configurar Python Excel Automation:

Tabla de contenido

  • Paso 1: analizar el conjunto de datos de Excel
  • Paso 2: hacer tablas dinámicas usando Pandas
  • Paso 3: Diseñando los Reportes usando Openpyxl
  • Paso 4: Automatización del informe con Python
  • Paso 5: programar el script de Python

Paso 1: analizar el conjunto de datos de Excel

El primer paso en Python Excel Automation es analizar el conjunto de datos. El conjunto de datos utilizado en este tutorial es un conjunto de datos de ventas. Este conjunto de datos también está disponible en Kaggle . Como este conjunto de datos está en formato .csv , debe cambiarlo al formato .xslx . Los datos se utilizarán para crear el siguiente informe después de configurar nuestra Automatización de Python Excel.


Paso 2: hacer tablas dinámicas usando Pandas

El siguiente paso en Python Excel Automation es diseñar tablas dinámicas. Antes de hacer eso, necesita importar las siguientes bibliotecas:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas se utiliza para leer el archivo de Excel, crear la tabla dinámica y exportarla a Excel. Luego puede usar la biblioteca Openpyxl en Python para escribir fórmulas de Excel, hacer gráficos y hojas de cálculo en Python.

Para leer su archivo de Excel, asegúrese de que el archivo esté en el mismo lugar donde se encuentra su secuencia de comandos de Python y ejecute el siguiente código en Excel:

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

Para crear la tabla dinámica, debe acceder al marco de datos excel_file que creó anteriormente. Puede usar " .pivot_table() " para crear la tabla. Si desea crear una tabla dinámica para mostrar el dinero total dividido entre hombres y mujeres, puede ejecutar el siguiente código:

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Finalmente, para exportar la tabla dinámica, usaremos el método " .to_excel() " como se muestra a continuación:

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

La carpeta de Excel ahora se exporta en la misma ubicación que sus scripts de Python.


Paso 3: Diseñando los Reportes usando Openpyxl

El siguiente paso en Python Excel Automation es diseñar los informes. Para hacer el informe, debe usar el método " load_workbook" , que se importa de Openpyxl y guardarlo usando el método " .save ()" . Esto se muestra a continuación:

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation le permite crear gráficos de Excel utilizando tablas dinámicas. Para crear un gráfico de Excel usando una tabla dinámica, necesita usar el Módulo de gráfico de barras y para identificar la posición de los datos y los valores de categoría, puede usar el Módulo de referencia. Ambos se importaron antes en el Paso 1. Puede escribir fórmulas basadas en Excel en Python, de la misma manera que las escribe en Excel. Un ejemplo de esto se muestra a continuación:

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Paso 4: Automatización del informe con Python

El siguiente paso en Python Excel Automation es automatizar su informe. Puede escribir todo el código en una función para que sea fácil automatizar el informe. Ese código se muestra a continuación:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Paso 5: programar el script de Python

El paso final en Python Excel Automation es ejecutar el script de Python en diferentes horarios según los requisitos de datos. Solo necesita usar el programador de tareas o cron en Windows y Mac respectivamente.

¡Eso es todo! ¡Ha configurado con éxito Python Excel Automation en 5 sencillos pasos!

Configurer Python Excel Automation en 5 étapes faciles

Excel est un tableur développé par Microsoft en 1987. Il est officiellement pris en charge par presque tous les systèmes d'exploitation tels que Windows, Macintosh, Android, etc. Il est préinstallé avec le système d'exploitation Windows et peut être facilement intégré à d'autres plates-formes de système d'exploitation. Microsoft Excel est l'outil le meilleur et le plus accessible lorsqu'il s'agit de travailler avec des données structurées.

Dans le tutoriel d'aujourd'hui, nous allons apprendre les étapes pour configurer Python Excel Automation. Vous pouvez suivre les étapes ci-dessous pour configurer Python Excel Automation :

Table des matières

  • Étape 1 : Analyser l'ensemble de données Excel
  • Étape 2 : Création de tableaux croisés dynamiques à l'aide de Pandas
  • Étape 3 : Conception des rapports à l'aide d'Openpyxl
  • Étape 4 : Automatiser le rapport avec Python
  • Étape 5 : Planification du script Python

Étape 1 : Analyser l'ensemble de données Excel

La première étape de Python Excel Automation consiste à analyser l'ensemble de données. L'ensemble de données utilisé dans ce didacticiel est un ensemble de données de ventes. Cet ensemble de données est également disponible sur Kaggle . Comme ce jeu de données est au format .csv , vous devez le changer au format .xslx . Les données seront utilisées pour créer le rapport ci-dessous après la configuration de notre Python Excel Automation.


Étape 2 : Création de tableaux croisés dynamiques à l'aide de Pandas

La prochaine étape de Python Excel Automation consiste à concevoir des tableaux croisés dynamiques. Avant cela, vous devez importer les bibliothèques suivantes :

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas est utilisé pour lire le fichier Excel, créer le tableau croisé dynamique et l'exporter vers Excel. Vous pouvez ensuite utiliser la bibliothèque Openpyxl en Python pour écrire des formules Excel, créer des graphiques et des feuilles de calcul en Python.

Pour lire votre fichier Excel, assurez-vous que le fichier se trouve au même endroit où se trouve votre script Python et exécutez le code suivant dans Excel :

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

Pour créer le tableau croisé dynamique, vous devez accéder au bloc de données excel_file que vous avez créé précédemment. Vous pouvez utiliser le " .pivot_table() " pour créer la table. Si vous souhaitez créer un tableau croisé dynamique pour afficher l'argent total réparti entre les hommes et les femmes, vous pouvez exécuter le code ci-dessous :

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Enfin, pour exporter le tableau croisé dynamique, nous utiliserons la méthode « .to_excel() » comme indiqué ci-dessous :

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

Le dossier Excel est maintenant exporté au même emplacement que vos scripts Python.


Étape 3 : Conception des rapports à l'aide d'Openpyxl

La prochaine étape de Python Excel Automation consiste à concevoir les rapports. Pour créer le rapport, vous devez utiliser la méthode " load_workbook" , qui est importée d'Openpyxl et l'enregistrer à l'aide de la méthode " .save()" . Ceci est illustré ci-dessous :

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation vous permet de créer des graphiques Excel à l'aide de tableaux croisés dynamiques. Pour créer un graphique Excel à l'aide d'un tableau croisé dynamique, vous devez utiliser le module Barchart et pour identifier la position des données et des valeurs de catégorie, vous pouvez utiliser le module de référence. Ces deux éléments ont été importés auparavant à l'étape 1. Vous pouvez écrire des formules basées sur Excel en Python, de la même manière que vous les écrivez dans Excel. Un exemple de ceci est montré ci-dessous :

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Étape 4 : Automatiser le rapport avec Python

La prochaine étape de Python Excel Automation consiste à automatiser votre rapport. Vous pouvez écrire tout le code dans une seule fonction afin qu'il soit facile d'automatiser le rapport. Ce code est illustré ci-dessous :

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Étape 5 : Planification du script Python

La dernière étape de Python Excel Automation consiste à exécuter le script Python à différents horaires selon les exigences en matière de données. Il vous suffit d'utiliser le planificateur de tâches ou cron sous Windows et Mac respectivement.

C'est ça! Vous avez configuré avec succès Python Excel Automation en 5 étapes faciles !

中條 美冬

1665135121

5 つの簡単なステップで Python Excel Automation をセットアップする

Excel は、1987 年に Microsoft によって開発されたスプレッドシート アプリケーションです。Windows、Macintosh、Android などのほぼすべてのオペレーティング システムで公式にサポートされています。Windows OS にプリインストールされており、簡単に統合できます。他の OS プラットフォーム。Microsoft Excel は、構造化されたデータを操作する場合に最適で、最もアクセスしやすいツールです。

今日のチュートリアルでは、Python Excel Automation をセットアップする手順を学びます。以下の手順に従って、Python Excel Automation をセットアップできます。

目次

  • ステップ 1: Excel データセットの分析
  • ステップ 2: Pandas を使用してピボット テーブルを作成する
  • ステップ 3: Openpyxl を使用してレポートを設計する
  • ステップ 4: Python を使用してレポートを自動化する
  • ステップ 5: Python スクリプトのスケジューリング

ステップ 1: Excel データセットの分析

Python Excel Automation の最初のステップは、データセットを分析することです。このチュートリアルで使用されるデータセットは、Sales Dataset です。このデータセットはKaggleでも利用できます。このデータセットは.csv形式であるため、 .xslx形式に変更する必要があります。データは、Python Excel Automation をセットアップした後、以下のレポートを作成するために使用されます。


ステップ 2: Pandas を使用してピボット テーブルを作成する

Python Excel Automation の次のステップは、ピボット テーブルを設計することです。その前に、次のライブラリをインポートする必要があります。

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandasは、Excel ファイルの読み取り、ピボット テーブルの作成、Excel へのエクスポートに使用されます。その後、Python でOpenpyxlライブラリを使用して、Python で Excel の数式を記述したり、グラフやスプレッドシートを作成したりできます。

Excel ファイルを読み取るには、ファイルが Python スクリプトと同じ場所にあることを確認し、Excel で次のコードを実行します。

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

ピボット テーブルを作成するには、前に作成した Excel_file データ フレームにアクセスする必要があります。「.pivot_table()」を使用してテーブルを作成できます。男性と女性の合計金額を表示するピボット テーブルを作成する場合は、次のコードを実行できます。

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

最後に、ピボット テーブルをエクスポートするには、以下に示すように「 .to_excel() 」メソッドを使用します。

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

Excel フォルダーが Python スクリプトと同じ場所にエクスポートされます。


ステップ 3: Openpyxl を使用してレポートを設計する

Python Excel Automation の次のステップは、レポートを設計することです。レポートを作成するには、Openpyxl からインポートされた「load_workbook」メソッドを使用し、「.save ()」メソッドを使用して保存する必要があります。これを以下に示します。

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation を使用すると、ピボット テーブルを使用して Excel グラフを作成できます。ピボット テーブルを使用して Excel グラフを作成するには、Barchart モジュールを使用する必要があり、データとカテゴリ値の位置を特定するには、参照モジュールを使用できます。これらは両方とも、ステップ 1 で以前にインポートされました。Excel ベースの数式は、Excel で記述するのと同じ方法で Python で記述できます。この例を以下に示します。

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

ステップ 4: Python を使用してレポートを自動化する

Python Excel Automation の次のステップは、レポートを自動化することです。レポートを簡単に自動化できるように、すべてのコードを 1 つの関数に記述することができます。そのコードを以下に示します。

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

ステップ 5: Python スクリプトのスケジューリング

Python Excel Automation の最後のステップは、データ要件に従ってさまざまなスケジュールで Python スクリプトを実行することです。Windows と Mac でそれぞれタスク スケジューラまたは cron を使用するだけです。

それでおしまい!5 つの簡単な手順で Python Excel Automation を正常にセットアップできました。

Настройте автоматизацию Python Excel за 5 простых шагов

Excel — это приложение для работы с электронными таблицами, разработанное Microsoft в 1987 году. Оно официально поддерживается почти всеми операционными системами, такими как Windows, Macintosh, Android и т. д. Оно предустановлено в ОС Windows и может быть легко интегрировано с другие платформы ОС. Microsoft Excel — лучший и самый доступный инструмент для работы со структурированными данными.

В сегодняшнем уроке мы изучим шаги по настройке Python Excel Automation. Вы можете выполнить следующие шаги для настройки Python Excel Automation:

Оглавление

  • Шаг 1. Анализ набора данных Excel
  • Шаг 2: Создание сводных таблиц с помощью Pandas
  • Шаг 3: Разработка отчетов с использованием Openpyxl
  • Шаг 4: Автоматизация отчета с помощью Python
  • Шаг 5: Планирование сценария Python

Шаг 1. Анализ набора данных Excel

Первым шагом в Python Excel Automation является анализ набора данных. Набор данных, используемый в этом руководстве, представляет собой набор данных о продажах. Этот набор данных также доступен на Kaggle . Поскольку этот набор данных имеет формат .csv , вам необходимо изменить его на формат .xslx . Данные будут использованы для создания приведенного ниже отчета после настройки нашей автоматизации Python Excel.


Шаг 2: Создание сводных таблиц с помощью Pandas

Следующим шагом в Python Excel Automation является разработка сводных таблиц. Перед этим вам необходимо импортировать следующие библиотеки:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas используется для чтения файла Excel, создания сводной таблицы и экспорта ее в Excel. Затем вы можете использовать библиотеку Openpyxl на Python для написания формул Excel, создания диаграмм и электронных таблиц на Python.

Чтобы прочитать файл Excel, убедитесь, что файл находится в том же месте, где находится ваш скрипт Python, и запустите следующий код в Excel:

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

Чтобы создать сводную таблицу, вам необходимо получить доступ к фрейму данных excel_file, который вы создали ранее. Вы можете использовать « .pivot_table() » для создания таблицы. Если вы хотите создать сводную таблицу, чтобы показать общую сумму денег, разделенную на мужчин и женщин, вы можете запустить следующий код:

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Наконец, чтобы экспортировать сводную таблицу, мы будем использовать метод « .to_excel() », как показано ниже:

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

Папка Excel теперь экспортируется в то же место, что и ваши скрипты Python.


Шаг 3: Разработка отчетов с использованием Openpyxl

Следующим шагом в Python Excel Automation является разработка отчетов. Чтобы сделать отчет, вам нужно использовать метод « load_workbook» , который импортируется из Openpyxl, и сохранить его с помощью метода « .save()» . Это показано ниже:

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation позволяет создавать диаграммы Excel с помощью сводных таблиц. Чтобы создать диаграмму Excel с помощью сводной таблицы, вам необходимо использовать модуль Barchart, а для определения положения данных и значений категорий вы можете использовать справочный модуль. Оба они были импортированы ранее на шаге 1. Вы можете писать формулы на основе Excel в Python так же, как вы пишете их в Excel. Пример этого показан ниже:

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Шаг 4: Автоматизация отчета с помощью Python

Следующим шагом в Python Excel Automation является автоматизация вашего отчета. Вы можете написать весь код в одну функцию, чтобы было легко автоматизировать отчет. Этот код показан ниже:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Шаг 5: Планирование сценария Python

Последним шагом в Python Excel Automation является запуск скрипта Python по разным расписаниям в соответствии с требованиями к данным. Вам просто нужно использовать планировщик задач или cron в Windows и Mac соответственно.

Вот и все! Вы успешно настроили Python Excel Automation за 5 простых шагов!

Duck Hwan

1665127860

간단한 5단계로 Python Excel 자동화 설정

Excel은 1987년 Microsoft에서 개발한 스프레드시트 응용 프로그램입니다. Windows, Macintosh, Android 등과 같은 거의 모든 운영 체제에서 공식적으로 지원됩니다. Windows OS가 사전 설치되어 제공되며 다음과 쉽게 통합할 수 있습니다. 다른 OS 플랫폼. Microsoft Excel은 구조화된 데이터로 작업할 때 가장 쉽고 가장 접근하기 쉬운 도구입니다.

오늘의 자습서에서는 Python Excel 자동화를 설정하는 단계를 배웁니다. 아래 단계에 따라 Python Excel 자동화를 설정할 수 있습니다.

목차

  • 1단계: Excel 데이터 세트 분석
  • 2단계: Pandas를 사용하여 피벗 테이블 만들기
  • 3단계: Openpyxl을 사용하여 보고서 디자인하기
  • 4단계: Python으로 보고서 자동화
  • 5단계: Python 스크립트 예약

1단계: Excel 데이터 세트 분석

Python Excel 자동화의 첫 번째 단계는 데이터 집합을 분석하는 것입니다. 이 튜토리얼에서 사용하는 데이터 세트는 판매 데이터 세트입니다. 이 데이터 세트는 Kaggle 에서도 사용할 수 있습니다 . 이 Dataset은 .csv 형식이므로 .xslx 형식 으로 변경해야 합니다. 데이터는 Python Excel 자동화를 설정한 후 아래 보고서를 만드는 데 사용됩니다.


2단계: Pandas를 사용하여 피벗 테이블 만들기

Python Excel 자동화의 다음 단계는 피벗 테이블을 디자인하는 것입니다. 그렇게 하기 전에 다음 라이브러리를 가져와야 합니다.

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas 는 Excel 파일을 읽고 피벗 테이블을 만들고 Excel로 내보내는 데 사용됩니다. 그런 다음 Python의 Openpyxl 라이브러리를 사용하여 Excel 수식을 작성하고 Python에서 차트와 스프레드시트를 만들 수 있습니다.

Excel 파일을 읽으려면 파일이 Python 스크립트가 있는 동일한 위치에 있는지 확인하고 Excel에서 다음 코드를 실행합니다.

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

피벗 테이블을 생성하려면 이전에 생성한 excel_file 데이터 프레임에 접근해야 합니다. " .pivot_table() "을 사용하여 테이블을 만들 수 있습니다. 남성과 여성에 대해 분할된 총 금액을 표시하는 피벗 테이블을 만들려면 아래 코드를 실행할 수 있습니다.

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

마지막으로 피벗 테이블을 내보내기 위해 아래와 같이 " .to_excel() " 메서드를 사용합니다.

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

이제 Excel 폴더가 Python 스크립트와 동일한 위치로 내보내집니다.


3단계: Openpyxl을 사용하여 보고서 디자인하기

Python Excel 자동화의 다음 단계는 보고서를 디자인하는 것입니다. 보고서를 작성하려면 Openpyxl에서 가져온 " load_workbook" 메서드를 사용하고 " .save()" 메서드를 사용하여 저장해야 합니다. 이것은 아래와 같습니다.

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel 자동화를 사용하면 피벗 테이블을 사용하여 Excel 차트를 만들 수 있습니다. 피벗 테이블을 이용하여 엑셀 차트를 생성하기 위해서는 Barchart Module을 사용해야 하며, 데이터와 카테고리 값의 위치를 ​​파악하기 위해서는 Reference Module을 이용해야 합니다. 이 두 가지 모두 이전에 1단계에서 가져온 것입니다. Excel에서 작성하는 것과 동일한 방식으로 Python에서 Excel 기반 수식을 작성할 수 있습니다. 이에 대한 예는 다음과 같습니다.

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

4단계: Python으로 보고서 자동화

Python Excel 자동화의 다음 단계는 보고서를 자동화하는 것입니다. 보고서를 쉽게 자동화할 수 있도록 모든 코드를 하나의 함수에 작성할 수 있습니다. 해당 코드는 아래와 같습니다.

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

5단계: Python 스크립트 예약

Python Excel 자동화의 마지막 단계는 데이터 요구 사항에 따라 다른 일정으로 Python 스크립트를 실행하는 것입니다. Windows와 Mac에서 각각 작업 스케줄러 또는 cron을 사용하기만 하면 됩니다.

그게 다야! 간단한 5단계로 Python Excel 자동화를 성공적으로 설정했습니다!

Richten Sie die Python-Excel-Automatisierung in 5 einfachen Schritten

Excel ist eine Tabellenkalkulationsanwendung, die von Microsoft im Jahr 1987 entwickelt wurde. Sie wird offiziell von fast allen Betriebssystemen wie Windows, Macintosh, Android usw. unterstützt. Sie wird mit dem Windows-Betriebssystem vorinstalliert geliefert und kann problemlos integriert werden andere OS-Plattformen. Microsoft Excel ist das beste und zugänglichste Tool, wenn es um die Arbeit mit strukturierten Daten geht.

Im heutigen Tutorial lernen wir die Schritte zum Einrichten der Python-Excel-Automatisierung kennen. Sie können den folgenden Schritten folgen, um die Python Excel-Automatisierung einzurichten:

Inhaltsverzeichnis

  • Schritt 1: Analysieren des Excel-Datensatzes
  • Schritt 2: Erstellen von Pivot-Tabellen mit Pandas
  • Schritt 3: Entwerfen der Berichte mit Openpyxl
  • Schritt 4: Automatisieren des Berichts mit Python
  • Schritt 5: Planen des Python-Skripts

Schritt 1: Analysieren des Excel-Datensatzes

Der erste Schritt in der Python-Excel-Automatisierung besteht darin, das Dataset zu analysieren. Der in diesem Lernprogramm verwendete Datensatz ist ein Verkaufsdatensatz. Dieser Datensatz ist auch auf Kaggle verfügbar . Da dieses Dataset im .csv -Format vorliegt, müssen Sie es in das .xslx -Format ändern. Die Daten werden verwendet, um den folgenden Bericht zu erstellen, nachdem wir unsere Python-Excel-Automatisierung eingerichtet haben.


Schritt 2: Erstellen von Pivot-Tabellen mit Pandas

Der nächste Schritt in der Python-Excel-Automatisierung ist das Entwerfen von Pivot-Tabellen. Zuvor müssen Sie die folgenden Bibliotheken importieren:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas wird verwendet, um die Excel-Datei zu lesen, die Pivot-Tabelle zu erstellen und sie nach Excel zu exportieren. Sie können dann die Openpyxl- Bibliothek in Python verwenden, um Excel-Formeln zu schreiben, Diagramme und Tabellenkalkulationen in Python zu erstellen.

Stellen Sie zum Lesen Ihrer Excel-Datei sicher, dass sich die Datei an derselben Stelle befindet, an der sich Ihr Python-Skript befindet, und führen Sie den folgenden Code in Excel aus:

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

Um die Pivot-Tabelle zu erstellen, müssen Sie auf den Datenrahmen excel_file zugreifen, den Sie zuvor erstellt haben. Sie können „ .pivot_table() “ verwenden, um die Tabelle zu erstellen. Wenn Sie eine Pivot-Tabelle erstellen möchten, um das Gesamtgeld für Männer und Frauen aufzuteilen, können Sie den folgenden Code ausführen:

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Um schließlich die Pivot-Tabelle zu exportieren, verwenden wir die Methode „ .to_excel() “ wie unten gezeigt:

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

Der Excel-Ordner wird jetzt an denselben Speicherort wie Ihre Python-Skripts exportiert.


Schritt 3: Entwerfen der Berichte mit Openpyxl

Der nächste Schritt in der Python-Excel-Automatisierung besteht darin, die Berichte zu entwerfen. Um den Bericht zu erstellen, müssen Sie die Methode „ load_workbook“ verwenden, die aus Openpyxl importiert wird, und sie mit der Methode „ .save()“ speichern . Dies ist unten dargestellt:

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Mit Python Excel Automation können Sie Excel-Diagramme mithilfe von Pivot-Tabellen erstellen. Um ein Excel-Diagramm mit einer Pivot-Tabelle zu erstellen, müssen Sie das Barchart-Modul verwenden, und um die Position der Daten und Kategoriewerte zu identifizieren, können Sie das Referenzmodul verwenden. Beide wurden zuvor in Schritt 1 importiert. Sie können Excel-basierte Formeln in Python schreiben, genauso wie Sie sie in Excel schreiben. Ein Beispiel dafür ist unten dargestellt:

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Schritt 4: Automatisieren des Berichts mit Python

Der nächste Schritt in Python Excel Automation ist die Automatisierung Ihres Berichts. Sie können den gesamten Code in eine Funktion schreiben, sodass der Bericht einfach zu automatisieren ist. Dieser Code ist unten dargestellt:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Schritt 5: Planen des Python-Skripts

Der letzte Schritt in der Python-Excel-Automatisierung besteht darin, das Python-Skript gemäß den Datenanforderungen zu unterschiedlichen Zeitplänen auszuführen. Sie müssen nur den Taskplaner oder Cron unter Windows bzw. Mac verwenden.

Das ist es! Sie haben Python Excel Automation in 5 einfachen Schritten erfolgreich eingerichtet!

Configure o Python Excel Automation em 5 etapas fáceis

Excel é um aplicativo de planilha que foi desenvolvido pela Microsoft no ano de 1987. É oficialmente suportado por quase todos os sistemas operacionais como Windows, Macintosh, Android, etc. Ele vem pré-instalado com o sistema operacional Windows e pode ser facilmente integrado com outras plataformas de SO. O Microsoft Excel é a melhor e mais acessível ferramenta para trabalhar com dados estruturados.

No tutorial de hoje, aprenderemos as etapas para configurar o Python Excel Automation. Você pode seguir as etapas abaixo para configurar o Python Excel Automation:

Índice

  • Etapa 1: analisando o conjunto de dados do Excel
  • Etapa 2: criando tabelas dinâmicas usando Pandas
  • Etapa 3: projetando os relatórios usando Openpyxl
  • Etapa 4: automatizar o relatório com Python
  • Etapa 5: Agendando o script Python

Etapa 1: analisando o conjunto de dados do Excel

A primeira etapa na automação do Python Excel é analisar o conjunto de dados. O conjunto de dados usado neste tutorial é um conjunto de dados de vendas. Este conjunto de dados também está disponível no Kaggle . Como este Dataset está no formato .csv , você precisa alterá-lo para o formato .xslx . Os dados serão usados ​​para criar o relatório abaixo após configurar nosso Python Excel Automation.


Etapa 2: criando tabelas dinâmicas usando Pandas

A próxima etapa na automação do Python Excel é projetar tabelas dinâmicas. Antes de fazer isso, você precisa importar as seguintes bibliotecas:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas é usado para ler o arquivo do Excel, criar a tabela dinâmica e exportá-la para o Excel. Você pode então usar a biblioteca Openpyxl em Python para escrever fórmulas do Excel, fazer gráficos e planilhas em Python.

Para ler seu arquivo do Excel, verifique se o arquivo está no mesmo local em que o script Python está localizado e execute o seguinte código no Excel:

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

Para criar a tabela dinâmica, você precisa acessar o quadro de dados excel_file que você criou anteriormente. Você pode usar o “ .pivot_table() ” para criar a tabela. Se você quiser criar uma tabela dinâmica para mostrar o total de dinheiro dividido para homens e mulheres, você pode executar o código abaixo:

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Por fim, para exportar a Tabela Dinâmica, usaremos o método “ .to_excel() ” conforme mostrado abaixo:

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

A pasta do Excel agora é exportada no mesmo local que seus scripts Python.


Etapa 3: projetando os relatórios usando Openpyxl

A próxima etapa no Python Excel Automation é projetar os relatórios. Para fazer o relatório, você precisa usar o método “load_workbook” , que é importado do Openpyxl e salvá-lo usando o método “ .save()” . Isso é mostrado abaixo:

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation permite que você crie gráficos do Excel usando tabelas dinâmicas. Para criar um gráfico do Excel usando uma Tabela Dinâmica, você precisa usar o Módulo Barchart e para identificar a posição dos dados e valores de categoria, você pode usar o Módulo Referência. Ambos foram importados antes na Etapa 1. Você pode escrever fórmulas baseadas no Excel em Python, da mesma forma que as escreve no Excel. Um exemplo disso é mostrado abaixo:

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Etapa 4: automatizar o relatório com Python

A próxima etapa na automação do Python Excel é automatizar seu relatório. Você pode escrever todo o código em uma função para que seja fácil automatizar o relatório. Esse código é mostrado abaixo:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Etapa 5: Agendando o script Python

A etapa final na automação do Python Excel é executar o script Python em horários diferentes, conforme os requisitos de dados. Você só precisa usar o agendador de tarefas ou o cron no Windows e no Mac, respectivamente.

É isso! Você configurou com sucesso o Python Excel Automation em 5 etapas fáceis!

CODE VN

CODE VN

1665113344

Thiết lập tự động hóa Python Excel trong 5 bước đơn giản

Excel là một ứng dụng bảng tính được phát triển bởi Microsoft vào năm 1987. Nó được hỗ trợ chính thức bởi hầu hết các hệ điều hành như Windows, Macintosh, Android, v.v. Nó được cài đặt sẵn với hệ điều hành Windows và có thể dễ dàng tích hợp với các nền tảng hệ điều hành khác. Microsoft Excel là công cụ tốt nhất và dễ tiếp cận nhất khi làm việc với dữ liệu có cấu trúc.

Trong hướng dẫn hôm nay, chúng ta sẽ tìm hiểu các bước để thiết lập Python Excel Automation. Bạn có thể làm theo các bước dưới đây để thiết lập Tự động hóa Python Excel:

Mục lục

  • Bước 1: Phân tích Tập dữ liệu Excel
  • Bước 2: Tạo Pivot Table bằng Pandas
  • Bước 3: Thiết kế Báo cáo bằng Openpyxl
  • Bước 4: Tự động hóa Báo cáo bằng Python
  • Bước 5: Lập lịch tập lệnh Python

Bước 1: Phân tích Tập dữ liệu Excel

Bước đầu tiên trong Python Excel Automation là phân tích Tập dữ liệu. Tập dữ liệu được sử dụng trong hướng dẫn này là Tập dữ liệu bán hàng. Tập dữ liệu này cũng có sẵn trên Kaggle . Vì Tập dữ liệu này ở định dạng .csv , bạn cần thay đổi nó thành định dạng .xslx . Dữ liệu sẽ được sử dụng để tạo báo cáo dưới đây sau khi thiết lập Tự động hóa Python Excel của chúng tôi.


Bước 2: Tạo Pivot Table bằng Pandas

Bước tiếp theo trong Python Excel Automation là thiết kế Pivot Table. Trước khi làm điều đó, bạn cần nhập các thư viện sau:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas được sử dụng để đọc tệp Excel, tạo bảng Pivot và xuất nó sang Excel. Sau đó, bạn có thể sử dụng thư viện Openpyxl bằng Python để viết công thức Excel, tạo biểu đồ và bảng tính bằng Python.

Để đọc tệp Excel của bạn, hãy đảm bảo rằng tệp ở cùng nơi đặt tập lệnh Python của bạn và chạy mã sau trong Excel:

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

Để tạo bảng tổng hợp, bạn cần truy cập vào khung dữ liệu excel_file mà bạn đã tạo trước đó. Bạn có thể sử dụng “ .pivot_table () ” để tạo bảng. Nếu bạn muốn tạo một bảng tổng hợp để hiển thị tổng số tiền được chia cho nam và nữ, bạn có thể chạy mã dưới đây:

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Cuối cùng, để xuất Pivot Table, chúng tôi sẽ sử dụng phương thức “ .to_excel () ” như hình dưới đây:

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

Thư mục Excel hiện được xuất ở cùng vị trí với các tập lệnh Python của bạn.


Bước 3: Thiết kế Báo cáo bằng Openpyxl

Bước tiếp theo trong Python Excel Automation là thiết kế các báo cáo. Để tạo báo cáo, bạn cần sử dụng phương thức “ load_workbook” , được nhập từ Openpyxl và lưu nó bằng phương thức “ .save ()” . Điều này được hiển thị bên dưới:

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation cho phép bạn tạo biểu đồ Excel bằng Pivot Tables. Để tạo biểu đồ Excel bằng Pivot Table, bạn cần sử dụng Mô-đun Barchart và để xác định vị trí của dữ liệu và giá trị danh mục, bạn có thể sử dụng Mô-đun Tham chiếu. Cả hai công thức này đã được nhập trước đó trong Bước 1. Bạn có thể viết các công thức dựa trên Excel bằng Python, giống như cách bạn viết chúng trong Excel. Ví dụ về điều này được hiển thị bên dưới:

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Bước 4: Tự động hóa Báo cáo bằng Python

Bước tiếp theo trong Python Excel Automation là tự động hóa báo cáo của bạn. Bạn có thể viết tất cả mã vào một hàm để dễ dàng tự động hóa báo cáo. Mã đó được hiển thị bên dưới:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Bước 5: Lập lịch tập lệnh Python

Bước cuối cùng trong Python Excel Automation là chạy tập lệnh Python ở các lịch trình khác nhau theo yêu cầu dữ liệu. Bạn chỉ cần sử dụng công cụ lập lịch tác vụ hoặc cron tương ứng trên Windows và Mac.

Đó là nó! Bạn đã thiết lập thành công Tự động hóa Python Excel trong 5 bước đơn giản!

Set Up Python Excel Automation in 5 Easy Steps

Excel is a spreadsheet application that was developed by Microsoft in the Year 1987. It is officially supported by almost all of the operating systems like Windows, Macintosh, Android, etc. It comes pre-installed with the Windows OS and can be easily integrated with other OS platforms. Microsoft Excel is the best and the most accessible tool when it comes to working with structured data.

In today's tutorial we will learn the steps to set up Python Excel Automation. You can follow below steps to setup Python Excel Automation:

Table Of Contents

  • Step 1: Analyzing the Excel Dataset
  • Step 2: Making Pivot Tables using Pandas
  • Step 3: Designing the Reports using Openpyxl
  • Step 4: Automating the Report with Python
  • Step 5: Scheduling the Python Script

Step 1: Analyzing the Excel Dataset

The first step in Python Excel Automation is to analyze the Dataset. The Dataset used in this tutorial is a Sales Dataset. This Dataset is also available on Kaggle. As this Dataset is in .csv format, you need to change it to .xslx format. The data will be used to create the below report after setting up our Python Excel Automation.


Step 2: Making Pivot Tables using Pandas

The next step in Python Excel Automation is to design Pivot Tables. Before doing that, you need to import the following libraries:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas is used to read the Excel file, create the Pivot table and export it to Excel. You can then use the Openpyxl library in Python to write Excel formulas, make charts and spreadsheets in Python.

To read your Excel file, make sure that the file is in the same place where your Python script is located and run the following code in Excel:

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

To create the pivot table, you need to access the excel_file data frame that you created previously. You can use the “.pivot_table()” to create the table. If you want to create a pivot table to show the total money split up for males and females you can run the below code:

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Finally, to export the Pivot Table, we will use the “.to_excel()” method as shown below:

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

The Excel folder is now exported in the same location as your Python scripts.


Step 3: Designing the Reports using Openpyxl

The next step in Python Excel Automation is to design the reports. To make the report, you need to use the “load_workbook” method, that is imported from Openpyxl and save it using the “.save()” method. This is shown below:

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation allows you to create Excel charts using Pivot Tables. To create an Excel chart using a Pivot Table, you need to use the Barchart Module and to identify the position of the data and category values, you can use the Reference Module. Both of these were imported before in Step 1. You can write Excel-based formulas in Python, the same way you write them in Excel. An example of this is shown below:

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Step 4: Automating the Report with Python

The next step in Python Excel Automation is to automate your report. You can write all the code into one function so that it will be easy to automate the report. That code is shown below:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Step 5: Scheduling the Python Script

The final step in Python Excel Automation is to run the Python script at different schedules as per data requirements. You just need to use the task scheduler or cron on Windows and Mac respectively.

That’s it! You have successfully set up Python Excel Automation in 5 easy steps!

#python #excel 
 

Set Up Python Excel Automation in 5 Easy Steps
Noah Saunders

Noah Saunders

1663040998

How to Get User Location with HTML, CSS & JavaScript

Learn how to get user location with HTML, CSS and Javascript. We also make use of ‘Nominatim API’. 

HTML:

Let us begin by creating the project folder structure. We create a folder called – ‘Get User Location’. Inside this folder, we have four files. These files are index.html, style.css, script.js and location.svg. They are the HTML document, stylesheet, script file and image file respectively.

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Get User Location</title>
    <!-- Google Fonts -->
    <link
      href="https://fonts.googleapis.com/css2?family=Poppins:wght@500&display=swap"
      rel="stylesheet"
    />
    <!-- Stylesheet -->
    <link rel="stylesheet" href="style.css" />
  </head>
  <body>
    <div class="container">
      <img src="location.svg" />
      <div id="location-details">Click on the 'Get Location' Button</div>
      <button id="get-location">Get Location</button>
    </div>
    <!-- Script -->
    <script src="script.js"></script>
  </body>
</html>

CSS:

We start with the HTML section. This creates the structure necessary for our project. Now copy the code below and paste it into your HTML file. Next, we style our project using CSS. For this copy, the code provided to you below and paste it into your stylesheet.

* {
  padding: 0;
  margin: 0;
  box-sizing: border-box;
  font-family: "Poppins", sans-serif;
}
body {
  height: 100vh;
  background: linear-gradient(45deg, #0076ec, #42a1ff);
}
.container {
  width: 80vw;
  max-width: 37.5em;
  background-color: #ffffff;
  padding: 3em 1.8em;
  position: absolute;
  transform: translate(-50%, -50%);
  top: 50%;
  left: 50%;
  border-radius: 0.6em;
  box-shadow: 0 0.6em 2.5em rgba(0, 7, 70, 0.2);
}
.container img {
  width: 6.25em;
  display: block;
  margin: auto;
}
#location-details {
  font-size: 1.75em;
  text-align: center;
  margin: 1em 0 1.7em 0;
  color: #021d38;
  font-weight: 500;
}
.container button {
  display: block;
  margin: auto;
  background-color: #42a1ff;
  color: #ffffff;
  border: none;
  font-size: 1.25em;
  padding: 1em 2.5em;
  border-radius: 0.25em;
  cursor: pointer;
}

Javascript:

Lastly, all that is left is to add functionality to this project. For this we use javascript. I have added comments for each line of code to explain how it works. I hope that helps.

let locationButton = document.getElementById("get-location");
let locationDiv = document.getElementById("location-details");

locationButton.addEventListener("click", () => {
  //Geolocation APU is used to get geographical position of a user and is available inside the navigator object
  if (navigator.geolocation) {
    //returns position(latitude and longitude) or error
    navigator.geolocation.getCurrentPosition(showLocation, checkError);
  } else {
    //For old browser i.e IE
    locationDiv.innerText = "The browser does not support geolocation";
  }
});

//Error Checks
const checkError = (error) => {
  switch (error.code) {
    case error.PERMISSION_DENIED:
      locationDiv.innerText = "Please allow access to location";
      break;
    case error.POSITION_UNAVAILABLE:
      //usually fired for firefox
      locationDiv.innerText = "Location Information unavailable";
      break;
    case error.TIMEOUT:
      locationDiv.innerText = "The request to get user location timed out";
  }
};

const showLocation = async (position) => {
  //We user the NOminatim API for getting actual addres from latitude and longitude
  let response = await fetch(
    `https://nominatim.openstreetmap.org/reverse?lat=${position.coords.latitude}&lon=${position.coords.longitude}&format=json`
  );
  //store response object
  let data = await response.json();
  locationDiv.innerText = `${data.address.city}, ${data.address.country}`;
};

Related Videos

How to Get User Location in HTML CSS & JavaScript

Using the Geolocation API - JavaScript Tutorial

Get users location with Javascript geolocation

#html #css #javascript

How to Get User Location with HTML, CSS & JavaScript

A Julia Package to Estimate The Geographic Location Of IP Addresses

GeoIP

IP Geolocalization using the Geolite2 Database

Installation

The package is registered in the General registry and so can be installed at the REPL with

julia> using Pkg
julia> Pkg.add("GeoIP")

Usage

Data files

You can use MaxMind geolite2 csv files downloaded from the site. Due to the MaxMind policy, GeoLite.jl does not distribute GeoLite2 files and does not provide download utilities. For automated download it is recommended to use MaxMind GeoIP Update program. For proper functioning of GeoIP.jl you need to download GeoLite2 City datafile, usually it should have a name like GeoLite2-City-CSV_20191224.zip.

Files processing and loading provided with load() call. Directory where data is located should be located either in ENV["GEOIP_DATADIR"] or it can be passed as an argument to load function. Zip file location can be passed as an argument or it can be stored in ENV["GEOIP_ZIPFILE"]. For example

using GeoIP

geodata = load(zipfile = "GeoLite2-City-CSV_20191224.zip", datadir = "/data")

If ENV["GEOIP_DATADIR"] is set to "/data" and ENV["GEOIP_ZIPFILE"] is set to "GeoLite2-City-CSV_20191224.zip" then it is equivalent to

using GeoIP

geodata = load()

Example

You can get the ip data with the geolocate function or by using []

using GeoIP

geodata = load(zipfile = "GeoLite2-City-CSV_20191224.zip")
geolocate(geodata, ip"1.2.3.4")        # returns dictionary with all relevant information

# Equivalent to
geodata[ip"1.2.3.4"]

# Equivalent, but slower version
geodata["1.2.3.4"]

geolocate form is useful for broadcasting

geolocate.(geodata, [ip"1.2.3.4", ip"8.8.8.8"])  # returns vector of geo data.

Localization

It is possible to use localized version of geo files. To load localized data, one can use locales argument of the load function. To switch between different locales is possible with the help of setlocale function.

using GeoIP

geodata = load(zipfile = "GeoLite2-City-CSV_20191224.zip", locales = [:en, :fr])

geodata[ip"201.186.185.1"]
# Dict{String, Any} with 21 entries:
#   "time_zone"                     => "America/Santiago"
#   "subdivision_2_name"            => missing
#   "accuracy_radius"               => 100
#   "geoname_id"                    => 3874960
#   "continent_code"                => "SA"
#   "postal_code"                   => missing
#   "continent_name"                => "South America"
#   "locale_code"                   => "en"
#   "subdivision_2_iso_code"        => missing
#   "location"                      => Location(-72.9436, -41.4709, 0.0, "WGS84")
#   "v4net"                         => IPv4Net("201.186.185.0/24")
#   "subdivision_1_name"            => "Los Lagos Region"
#   "subdivision_1_iso_code"        => "LL"
#   "city_name"                     => "Port Montt"
#   "metro_code"                    => missing
#   "registered_country_geoname_id" => 3895114
#   "is_in_european_union"          => 0
#   "is_satellite_provider"         => 0
#   "is_anonymous_proxy"            => 0
#   "country_name"                  => "Chile"
#   "country_iso_code"              => "CL"

geodata_fr = setlocale(geodata, :fr)
geodata_fr[ip"201.186.185.1"]
# Dict{String, Any} with 21 entries:
#   "time_zone"                     => "America/Santiago"
#   "subdivision_2_name"            => missing
#   "accuracy_radius"               => 100
#   "geoname_id"                    => 3874960
#   "continent_code"                => "SA"
#   "postal_code"                   => missing
#   "continent_name"                => "Amérique du Sud"
#   "locale_code"                   => "fr"
#   "subdivision_2_iso_code"        => missing
#   "location"                      => Location(-72.9436, -41.4709, 0.0, "WGS84")
#   "v4net"                         => IPv4Net("201.186.185.0/24")
#   "subdivision_1_name"            => missing
#   "subdivision_1_iso_code"        => "LL"
#   "city_name"                     => "Puerto Montt"
#   "metro_code"                    => missing
#   "registered_country_geoname_id" => 3895114
#   "is_in_european_union"          => 0
#   "is_satellite_provider"         => 0
#   "is_anonymous_proxy"            => 0
#   "country_name"                  => "Chili"
#   "country_iso_code"              => "CL"

During load procedure, it is possible to use either Symbol notation, i.e. locales = [:en, :fr] or one can pass Vector of Pair, where first argument is the locale name and second argument is a regular expression, which defines the name of the CSV file, which contains necessary localization. For example locales = [:en => r"Locations-en.csv%", :fr => r"Locations-fr.csv"]. By default, following locales are supported :en, :de, :ru, :ja, :es, :fr, :pt_br, :zh_cn.

Default locale, which is used in getlocale response can be set with the help of deflocale argument of the load function. For example, to get :fr locale by default

geodata = load(zipfile = "GeoLite2-City-CSV_20191224.zip", locales = [:en, :fr], deflocale = :fr)

Acknowledgements

This product uses, but not include, GeoLite2 data created by MaxMind, available from http://www.maxmind.com.

Download Details:

Author: JuliaWeb
Source Code: https://github.com/JuliaWeb/GeoIP.jl 
License: View license

#julia #id #location 

A Julia Package to Estimate The Geographic Location Of IP Addresses

EarthSphere.jl: A Julia Library for Measuring Approximate Distance

EarthSphere.jl

A very simple Julia Library for measuring approximate distance and location given latitudes and longitudes

Usage:

mylocation = Earth_LatLong_Coord(25,45,"SW")
#I am located on earth, latitude 25 degrees south, longitude 45 west

yourlocation = Earth_LatLong_Coord(65,10,"NE")
#You are located on Earth, latitude 65 degrees north, longitude 10 degrees east

distance = get_distance(mylocation,yourlocation)

TODO:

  • Refactor API, make names cleaner and clearer
  • Allow choice of algorithm

Download Details:

Author: Cuevasclemente
Source Code: https://github.com/cuevasclemente/EarthSphere.jl 
License: View license

#julia #location 

EarthSphere.jl: A Julia Library for Measuring Approximate Distance