What Are the Best Ways to Protect Your CAD/CAM Data?

Computer-aided design (CAD) and computer-aided manufacturing (CAM) files are increasingly instrumental in planning and producing various products. However, you may initially overlook the possibility of cybercriminals targeting CAD and CAM files when orchestrating their attacks. A solid data security management plan can prevent data loss and unwanted outcomes. Here are some actionable suggestions.

Ensure Everyone Only Uses Authorized Software Versions

When people with limited budgets need expensive CAD/CAM software, some might turn to the internet to find “cracked” versions of the desired products. Creating a cracked version involves modifying the genuine title to remove a specific characteristic. For example, cracked software may no longer include the screen requiring a person to enter a software key to keep using the program after a free trial.

Removing JavaScript: How To Use HTML With Htmx and Reduce the Amount of Code

HTML as the Center of the Universe

The internet as we know it today is largely due to HTML and CSS. Javascript (JS) could act as a glue between them and make pages more dynamic and interactive, but the history of web programming developed differently. After the emergence of client-side rendering and other similar technologies, it became much more difficult to use JS to create web applications.

What Is Htmx?

Htmx is a library that allows you to create modern and powerful user interfaces with simple markup. Thanks to it, AJAX requests, triggering CSS transitions, calling WebSocket and server-sent events can be performed directly from HTML elements.

Test Plan and Test Strategy: Best Practices That Will Make Your Product Development a Success

Without a crystal clear understanding of the processes when a team works on a software product, it can be tempting to think that all the problems stem from under-qualified QA engineers who click around randomly and ruin the hard work of the whole team. However, the value and purpose of the quality assurance process are not transparent without documentation. That's where a test plan and test strategy can help.

Even for those who are well aware of the processes, there's still the problem of measuring the quality QAs provide. If you don't measure quality, you don't have any control over the testing process or any ability to anticipate the results. So how should you know what exactly you paid for?

Migrating Secrets Using HashiCorp Vault and Safe CLI

Vault and Safe

Vault is a secret management service by HashiCorp. It is a tool that will help you in storing secrets (API keys, passwords, etc.) and accessing them securely. You can use Vault with a user interface as well as through CLI. 

In this post, we will not be going deep into what Vault is, but instead, will look at how we can migrate secrets from one Vault to another. We can migrate secrets from Vault using Vault CLI, but it can get a little complicated to do so. Therefore, to make things easy, we will use Safe CLI, which is a wrapper around Vault. It will help us in managing and migrating our secrets by using simple commands. It also helps us connect to different vault instances very quickly for migration purposes.

Best Practices in Data Discovery: Building Search for a Data Discovery Platform

Image by flashmovie from freepik

The efficiency of data discovery depends on the user-friendliness of the UI and the features integrated into it to make it easier for users to look up the data they need. This article explores significant components of a user-friendly UI. In addition, it looks into specific measures that can be employed to increase the quality of search in the Open Data Discovery (ODD) Platform.

Proven Tips to Optimize the Performance of React Native App

For modern mobile app developers, React Native is the most promising and viable framework took for developing cross-platform applications. Ever since the framework launched it never faded from the limelight and surpasses all other frameworks of mobile app development.

However since React Native is a new app development framework, developers facing difficulties in improving its performance under certain scenarios. When an application requires more tabs, parallel processing, more controls, animations, navigations, and the inclusion of 3rd party libraries the framework becomes slow. However, the framework is on the verge of evolving according to the rising demands of developers, thanks to the continuous efforts of the React Native community people.

PostgreSQL EXPLAIN – What Are the Query Costs?

Understanding the Postgres EXPLAIN Cost

EXPLAIN is very useful for understanding the performance of a Postgres query. It returns the execution plan generated by the PostgreSQL query planner for a given statement. The EXPLAIN command specifies whether the tables referenced in a statement will be searched using an index scan or a sequential scan. When reviewing the output of  EXPLAIN the command, you'll notice the cost statistics, so it’s natural to wonder what they mean, how they’re calculated, and how they’re used. In short, the PostgreSQL query planner estimates how much time the query will take (in an arbitrary unit), with both a startup cost and a total cost for each operation. More on that later. When it has multiple options for executing a query, it uses these costs to choose the cheapest, and therefore hopefully fastest, option.

What Unit Are the Costs In?

The costs are in an arbitrary unit. A common misunderstanding is that they are in milliseconds or some other unit of time, but that’s not the case. The cost units are anchored (by default) to a single sequential page read costing 1.0 units (seq_page_cost). Each row processed adds 0.01 (cpu_tuple_cost), and each non-sequential page read adds 4.0 (random_page_cost). There are many more constants like this, all of which are configurable. That last one is a particularly common candidate, at least on modern hardware. We’ll look into that more in a bit.

Usage of Java Streams and Lambdas in Selenium WebDriver

Overview: Java Streams and Lambdas

Lambdas

A lambda expression is a microcode that takes in parameter(s) and returns a value. Lambda expressions are similar to methods, but they are anonymous and they can be implemented right in the body of a method.

Lambdas Syntax

Java
 
parameter -> expression //to use single parameter & expression
(parameter1, parameter2) -> expression //to use multiple parameters & expression
(parameter1, parameter2) -> {code block} //to use multiple parameters & conditions, loops and more complex logic


Syntax Highlighting (and More!) With Prism on a Static Site

So, you’ve decided to build a blog with Next.js. Like any dev blogger, you’d like to have code snippets in your posts that are formatted nicely with syntax highlighting. Perhaps you also want to display line numbers in the snippets, and maybe even have the ability to call out certain lines of code.

This post will show you how to get that set up, as well as some tips and tricks for getting these other features working. Some of it is tricker than you might expect.

Prerequisites

We’re using the Next.js blog starter as the base for our project, but the same principles should apply to other frameworks. That repo has clear (and simple) getting started instructions. Scaffold the blog, and let’s go!

Another thing we’re using here is Prism.js, a popular syntax highlighting library that’s even used right here on CSS-Tricks. The Next.js blog starter uses Remark to convert Markdown into markup, so we’ll use the remark-Prism.js plugin for formatting our code snippets.

Basic Prism.js integration

Let’s start by integrating Prism.js into our Next.js starter. Since we already know we’re using the remark-prism plugin, the first thing to do is install it with your favorite package manager:

npm i remark-prism

Now go into the markdownToHtml file, in the /lib folder, and switch on remark-prism:

import remarkPrism from "remark-prism";

// later ...

.use(remarkPrism, { plugins: ["line-numbers"] })

Depending on which version of the remark-html you’re using, you might also need to change its usage to .use(html, { sanitize: false }).

The whole module should now look like this:

import { remark } from "remark";
import html from "remark-html";
import remarkPrism from "remark-prism";

export default async function markdownToHtml(markdown) {
  const result = await remark()
    .use(html, { sanitize: false })
    .use(remarkPrism, { plugins: ["line-numbers"] })
    .process(markdown);

  return result.toString();
}

Adding Prism.js styles and theme

Now let’s import the CSS that Prism.js needs to style the code snippets. In the pages/_app.js file, import the main Prism.js stylesheet, and the stylesheet for whichever theme you’d like to use. I’m using Prism.js’s “Tomorrow Night” theme, so my imports look like this:

import "prismjs/themes/prism-tomorrow.css";
import "prismjs/plugins/line-numbers/prism-line-numbers.css";
import "../styles/prism-overrides.css";

Notice I’ve also started a prism-overrides.css stylesheet where we can tweak some defaults. This will become useful later. For now, it can remain empty.

And with that, we now have some basic styles. The following code in Markdown:

```js
class Shape {
  draw() {
    console.log("Uhhh maybe override me");
  }
}

class Circle {
  draw() {
    console.log("I'm a circle! :D");
  }
}
```

…should format nicely:

Adding line numbers

You might have noticed that the code snippet we generated does not display line numbers even though we imported the plugin that supports it when we imported remark-prism. The solution is hidden in plain sight in the remark-prism README:

Don’t forget to include the appropriate css in your stylesheets.

In other words, we need to force a .line-numbers CSS class onto the generated <pre> tag, which we can do like this:

And with that, we now have line numbers!

Note that, based on the version of Prism.js I have and the “Tomorrow Night” theme I chose, I needed to add this to the prism-overrides.css file we started above:

.line-numbers span.line-numbers-rows {
  margin-top: -1px;
}

You may not need that, but there you have it. We have line numbers!

Highlighting lines

Our next feature will be a bit more work. This is where we want the ability to highlight, or call out certain lines of code in the snippet.

There’s a Prism.js line-highlight plugin; unfortunately, it is not integrated with remark-prism. The plugin works by analyzing the formatted code’s position in the DOM, and manually highlights lines based on that information. That’s impossible with the remark-prism plugin since there is no DOM at the time the plugin runs. This is, after all, static site generation. Next.js is running our Markdown through a build step and generating HTML to render the blog. All of this Prism.js code runs during this static site generation, when there is no DOM.

But fear not! There’s a fun workaround that fits right in with CSS-Tricks: we can use plain CSS (and a dash of JavaScript) to highlight lines of code.

Let me be clear that this is a non-trivial amount of work. If you don’t need line highlighting, then feel free to skip to the next section. But if nothing else, it can be a fun demonstration of what’s possible.

Our base CSS

Let’s start by adding the following CSS to our prism-overrides.css stylesheet:

:root {
  --highlight-background: rgb(0 0 0 / 0);
  --highlight-width: 0;
}

.line-numbers span.line-numbers-rows > span {
  position: relative;
}

.line-numbers span.line-numbers-rows > span::after {
  content: " ";
  background: var(--highlight-background);
  width: var(--highlight-width);
  position: absolute;
  top: 0;
}

We’re defining some CSS custom properties up front: a background color and a highlight width. We’re setting them to empty values for now. Later, though, we’ll set meaningful values in JavaScript for the lines we want highlighted.

We’re then setting the line number <span> to position: relative, so that we can add a ::after pseudo element with absolute positioning. It’s this pseudo element that we’ll use to highlight our lines.

Declaring the highlighted lines

Now, let’s manually add a data attribute to the <pre> tag that’s generated, then read that in code, and use JavaScript to tweak the styles above to highlight specific lines of code. We can do this the same way that we added line numbers before:

This will cause our <pre> element to be rendered with a data-line="3,8-10" attribute, where line 3 and lines 8-10 are highlighted in the code snippet. We can comma-separate line numbers, or provide ranges.

Let’s look at how we can parse that in JavaScript, and get highlighting working.

Reading the highlighted lines

Head over to components/post-body.tsx. If this file is JavaScript for you, feel free to either convert it to TypeScript (.tsx), or just ignore all my typings.

First, we’ll need some imports:

import { useEffect, useRef } from "react";

And we need to add a ref to this component:

const rootRef = useRef<HTMLDivElement>(null);

Then, we apply it to the root element:

<div ref={rootRef} className="max-w-2xl mx-auto">

The next piece of code is a little long, but it’s not doing anything crazy. I’ll show it, then walk through it.

useEffect(() => {
  const allPres = rootRef.current.querySelectorAll("pre");
  const cleanup: (() => void)[] = [];

  for (const pre of allPres) {
    const code = pre.firstElementChild;
    if (!code || !/code/i.test(code.tagName)) {
      continue;
    }

    const highlightRanges = pre.dataset.line;
    const lineNumbersContainer = pre.querySelector(".line-numbers-rows");

    if (!highlightRanges || !lineNumbersContainer) {
      continue;
    }

    const runHighlight = () =>
      highlightCode(pre, highlightRanges, lineNumbersContainer);
    runHighlight();

    const ro = new ResizeObserver(runHighlight);
    ro.observe(pre);

    cleanup.push(() => ro.disconnect());
  }

  return () => cleanup.forEach(f => f());
}, []);

We’re running an effect once, when the content has all been rendered to the screen. We’re using querySelectorAll to grab all the <pre> elements under this root element; in other words, whatever blog post the user is viewing.

For each one, we make sure there’s a <code> element under it, and we check for both the line numbers container and the data-line attribute. That’s what dataset.line checks. See the docs for more info.

If we make it past the second continue, then highlightRanges is the set of highlights we declared earlier which, in our case, is "3,8-10", where lineNumbersContainer is the container with the .line-numbers-rows CSS class.

Lastly, we declare a runHighlight function that calls a highlightCode function that I’m about to show you. Then, we set up a ResizeObserver to run that same function anytime our blog post changes size, i.e., if the user resizes the browser window.

The highlightCode function

Finally, let’s see our highlightCode function:

function highlightCode(pre, highlightRanges, lineNumberRowsContainer) {
  const ranges = highlightRanges.split(",").filter(val => val);
  const preWidth = pre.scrollWidth;

  for (const range of ranges) {
    let [start, end] = range.split("-");
    if (!start || !end) {
      start = range;
      end = range;
    }

    for (let i = +start; i <= +end; i++) {
      const lineNumberSpan: HTMLSpanElement = lineNumberRowsContainer.querySelector(
        `span:nth-child(${i})`
      );
      lineNumberSpan.style.setProperty(
        "--highlight-background",
        "rgb(100 100 100 / 0.5)"
      );
      lineNumberSpan.style.setProperty("--highlight-width", `${preWidth}px`);
    }
  }
}

We get each range and read the width of the <pre> element. Then we loop through each range, find the relevant line number <span>, and set the CSS custom property values for them. We set whatever highlight color we want, and we set the width to the total scrollWidth value of the <pre> element. I kept it simple and used "rgb(100 100 100 / 0.5)" but feel free to use whatever color you think looks best for your blog.

Here’s what it looks like:

Syntax highlighting for a block of Markdown code.

Line highlighting without line numbers

You may have noticed that all of this so far depends on line numbers being present. But what if we want to highlight lines, but without line numbers?

One way to implement this would be to keep everything the same and add a new option to simply hide those line numbers with CSS. First, we’ll add a new CSS class, .hide-numbers:

```js[class="line-numbers"][class="hide-numbers"][data-line="3,8-10"]
class Shape {
  draw() {
    console.log("Uhhh maybe override me");
  }
}

class Circle {
  draw() {
    console.log("I'm a circle! :D");
  }
}
```

Now let’s add CSS rules to hide the line numbers when the .hide-numbers class is applied:

.line-numbers.hide-numbers {
  padding: 1em !important;
}
.hide-numbers .line-numbers-rows {
  width: 0;
}
.hide-numbers .line-numbers-rows > span::before {
  content: " ";
}
.hide-numbers .line-numbers-rows > span {
  padding-left: 2.8em;
}

The first rule undoes the shift to the right from our base code in order to make room for the line numbers. By default, the padding of the Prism.js theme I chose is 1em. The line-numbers plugin increases it to 3.8em, then inserts the line numbers with absolute positioning. What we did reverts the padding back to the 1em default.

The second rule takes the container of line numbers, and squishes it to have no width. The third rule erases all of the line numbers themselves (they’re generated with ::before pseudo elements).

The last rule simply shifts the now-empty line number <span> elements back to where they would have been so that the highlighting can be positioned how we want it. Again, for my theme, the line numbers normally adds 3.8em worth of left padding, which we reverted back to the default 1em. These new styles add the other 2.8em so things are back to where they should be, but with the line numbers hidden. If you’re using different plugins, you might need slightly different values.

Here’s what the result looks like:

Syntax highlighting for a block of Markdown code.

Copy-to-Clipboard feature

Before we wrap up, let’s add one finishing touch: a button allowing our dear reader to copy the code from our snippet. It’s a nice little enhancement that spares people from having to manually select and copy the code snippets.

It’s actually somewhat straightforward. There’s a navigator.clipboard.writeText API for this. We pass that method the text we’d like to copy, and that’s that. We can inject a button next to every one of our <code> elements to send the code’s text to that API call to copy it. We’re already messing with those <code> elements in order to highlight lines, so let’s integrate our copy-to-clipboard button in the same place.

First, from the useEffect code above, let’s add one line:

useEffect(() => {
  const allPres = rootRef.current.querySelectorAll("pre");
  const cleanup: (() => void)[] = [];

  for (const pre of allPres) {
    const code = pre.firstElementChild;
    if (!code || !/code/i.test(code.tagName)) {
      continue;
    }

    pre.appendChild(createCopyButton(code));

Note the last line. We’re going to append our button right into the DOM underneath our <pre> element, which is already position: relative, allowing us to position the button more easily.

Let’s see what the createCopyButton function looks like:

function createCopyButton(codeEl) {
  const button = document.createElement("button");
  button.classList.add("prism-copy-button");
  button.textContent = "Copy";

  button.addEventListener("click", () => {
    if (button.textContent === "Copied") {
      return;
    }
    navigator.clipboard.writeText(codeEl.textContent || "");
    button.textContent = "Copied";
    button.disabled = true;
    setTimeout(() => {
      button.textContent = "Copy";
      button.disabled = false;
    }, 3000);
  });

  return button;
}

Lots of code, but it’s mostly boilerplate. We create our button then give it a CSS class and some text. And then, of course, we create a click handler to do the copying. After the copy is done, we change the button’s text and disable it for a few seconds to help give the user feedback that it worked.

The real work is on this line:

navigator.clipboard.writeText(codeEl.textContent || "");

We’re passing codeEl.textContent rather than innerHTML since we want only the actual text that’s rendered, rather than all the markup Prism.js adds in order to format our code nicely.

Now let’s see how we might style this button. I’m no designer, but this is what I came up with:

.prism-copy-button {
  position: absolute;
  top: 5px;
  right: 5px;
  width: 10ch;
  background-color: rgb(100 100 100 / 0.5);
  border-width: 0;
  color: rgb(0, 0, 0);
  cursor: pointer;
}

.prism-copy-button[disabled] {
  cursor: default;
}

Which looks like this:

Syntax highlighting for a block of Markdown code.

And it works! It copies our code, and even preserves the formatting (i.e. new lines and indentation)!

Wrapping up

I hope this has been useful to you. Prism.js is a wonderful library, but it wasn’t originally written for static sites. This post walked you through some tips and tricks for bridging that gap, and getting it to work well with a Next.js site.


Syntax Highlighting (and More!) With Prism on a Static Site originally published on CSS-Tricks. You should get the newsletter.

Portfolio Architecture Examples: Retail Collection

This article is a continuation of a series of posts about our project named Portfolio Architectures. The previous post, Portfolio Architecture Examples: Healthcare Collection, begins with a project overview, introduction, and examples of tooling and workshops available for the project.  You may want to refer back to that post to gain insight into the background of Portfolio Architectures before reading further.  

Retail Collection

The collection featured today is centered around architectures in the retail industry. There are currently seven architectures in this collection. We'll provide a short overview of each, leaving the in-depth exploration as an exercise for the reader.

Fintech and AI: Ways Artificial Intelligence Is Used in Finance

The impact and the innovation of AI can be seen everywhere, and fintech is no exception. The disruptive power of financial industries to shape the traditional financial institution is growing because of the advances in artificial intelligence.

AI-powered and machine learning technologies in fintech will help analyze large data sets in real-time and have the ability to make improvements. As the demand for such services increases, AI and ML become the key to sustainability and growth in the industry.

Testing Schema Registry: Spring Boot and Apache Kafka With JSON Schema

Introduction

Apache Kafka is the most widely used reliable and scalable eventing platform. It helps in providing the base for a fast, scalable, fault-tolerant, event-driven microservice architecture. Any type of data can be published into a Kafka topic and can be read from. Kafka does not provide an out-of-the-box schema validation system and hence is prone to junk data being fed into a topic.

Confluent has devised a concept of a schema registry that can be used to implement type checking of messages in any Kafka installation. The Schema Registry needs the message schema to be registered against a topic and it enforces that only messages conforming to the schema are sent to the topic.

How to Easily Add a Download Link in WordPress (3 Ways)

Do you want to learn how to add a download link in WordPress for your PDFs and other files?

Adding a download link to WordPress makes it easy for your readers to download guides, videos, images, and even paid products.

In this article, we’ll show you how to add a download link to your WordPress site, step by step.

How to easily add a download link in WordPress (2 ways)

Why Add a Download Link for Files in WordPress?

By adding a download link for your WordPress files, you make it easy for your visitors to download eBooks, videos, images, plugins, and other media and documents you upload to your site.

This makes it easy to create exclusive content for your visitors to help build your email list.

You can also offer some of these downloads as a bonus for your most valued readers to help improve your relationship and strengthen your brand.

Plus, you can even create a simple membership site, where you can put your members-only guides and other content behind a paywall.

That being said, let’s show you how to easily add a download link in WordPress. Simply use the quick links below to jump straight to the method you want to use.

The easiest way to add a download link in WordPress is by using the block editor. Simply open up a page or post where you want to add a download link and then click the ‘Plus’ add block button.

After that, search for ‘File’ and then click the block.

Add file block in posts or pages

Then, you can upload a file by clicking the ‘Upload’ button or choose one you’ve already uploaded to your media library.

Upload file or select from media library

Once you add the file, it will automatically display with the download link. When your users click this, it will download the file.

To change the name of the link, simply click on the text and edit it. 

Edit download link text

There are different display options that you can customize in the right hand panel depending on the type of file you added.

For example, if you upload a PDF file, then you can choose for the link to go directly to the media file, or open it on a separate attachment page.

You can also make the download open in a new tab by clicking the toggle.

Below that, you can also add a download button next to your link by clicking the ‘Show download button’ toggle.

Change download link display settings

Once you’re finished making changes, click the ‘Save’ or ‘Publish’ button to make your download link live.

Now, you can visit your WordPress website to see your download link in action.

Download link plus button example

Add a Simple Download Link by Uploading Files to Media Library

Another way to add a download link to a file is to first upload it to your media library by navigating to Media » Add New and then upload your file.

Add new media files

Next, you need to click on the file to open it.

Then, copy the URL in the ‘File URL’ box.

Copy file URL

Now, you can navigate to the post or page where you want to add the download link.

Then, add some text, click the ‘Link’ icon and paste the URL you just copied into the box.

Add link for download

You can also make the link open in the new tab by clicking the ‘Open in new tab’ toggle.

The right-hand side of the screen lets you change the link color, size, typography, and more.

Edit download link display settings

When you’re finished making changes, click the ‘Update’ or ‘Publish’ button to make your download link live.

Now, you can go to your WordPress blog to see your new download link.

Download link example

Another way you can add download links to your site is by using the OptinMonster plugin. It’s one of the best popup plugins and best lead generation plugins for WordPress used by over 1.2 million websites.

OptinMonster

It has a unique technology that lets you create on-click popups that open when a user clicks a link. These can be used to deliver content upgrades, digital downloads, email required downloads, and more. 

This style of popup relies on the powerful Zeigarnik effect. This psychological principle states that users who start an action are more likely to finish it.

Since your users are already showing interest, they’re more likely to use your download or enter their email addresses. 

Link click popup download example

To learn more, see our guide on how to open a WordPress popup form on click of link or image.

You can also manage and track your file downloads to see what your users click on and download the most. To learn more, see our guide on how to manage, track, and control file downloads in WordPress.

If you’re looking to sell downloadable files on your WordPress site or looking to create a members only area, then you have few different options.

For creating a membership site or selling courses online, you can use the MemberPress plugin. It’s the best WordPress membership plugin that lets you setup membership levels, add members-only protected downloads, create courses, add lessons, and more.

Alternatively, you can use the popular Easy Digital Downloads plugin. This plugin is focused on selling premium downloads like eBooks, music, software, and more. It comes with recurring subscriptions, licensing support, protected downloads, and more. This is what we use to sell our plugins.

To get started, use the SiteGround EDD hosting plan which makes set up easy, or download the plugin from Easy Digital Downloads website.

Easy Digital Downloads

After that, you can set up downloads as individual products that people can purchase, or download for free in exchange for an email address.

We hope this article helped you learn how to add a download link in WordPress. You may also want to see our guide on how to register a domain name and our expert picks of the best virtual business phone number apps with free options.

If you liked this article, then please subscribe to our YouTube Channel for WordPress video tutorials. You can also find us on Twitter and Facebook.

The post How to Easily Add a Download Link in WordPress (3 Ways) first appeared on WPBeginner.

Inspirational Websites Roundup #37

Another month, another roundup of amazing websites! These past couple of weeks we’ve collected some fine designs for you that will update you on the current trends. What’s in? Dark designs are hip and pastel tones took over stronger colors. Some unique takes are setting a higher bar for designers. It’s good to keep in mind: who dares something new, stands out!

Hope you get inspired and up-to-date on the latest web design trends! Enjoy!

Kargo

Baukunst: The Art of Building

Morningstar Ventures

Fundomo

David

Sector 32

Docent

P2P STUDIOS

Exo Ape

Rugby-Man

Erin Wesley

Elsa Joseph

Panic

Cyd Stumpel

gradation

Wild Fox Squad NFT

Dylan Brouwer Design

Hunter Yeany

Basis Studio

MOHEIM

Arcadia Science

Stas Bondar Thinking Designer

Anna Sherruble

RocketAir

Honey Pot Feminine Care

Vibia

Tenzr

Zhvanetsky Boulevard

Polybion

Arcana

NaN

Six

The post Inspirational Websites Roundup #37 appeared first on Codrops.

How to keep columns header on excel without change after export data to exc

I work on sql server 2017 I run script depend on python language v 3.10 .

I need to export data to excel fileStudentExport.xlsx already exist, and keep header without change after export.

header of excel file StudentExport.xlsx before export data to it as below

StudentId,StudentName

after run script query to export data to StudentExport.xlsx and Header changed to

StudentId,Name

my issue is header changed from column name StudentName to Name (exist on sql)

I try to change InputDataSet.to_excel it to keep header on excel file StudentExport.xlsx without change as below

InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], header=False, startrow= 1,index=False)

but it give me blank header

script Query I run it as below for lookup

 declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
    declare @FixedPath NVARCHAR(MAX)='G:\ExportFiles\StudentExport.xlsx'
    CREATE TABLE #FinalExportList(TableCount INT IDENTITY(1,1),Cols NVARCHAR(MAX),TableName NVARCHAR(200))
    insert into #FinalExportList(TableName,Cols)
    values
    ('dbo.students','TRY_CONVERT(VARCHAR(MAX),StudentId) AS [StudentId], Name')

    DECLARE
    @TableName NVARCHAR(200)
    ,@SQL NVARCHAR(MAX) = N''
    ,@PythonScript NVARCHAR(MAX) = N''
    ,@ExportFilePath NVARCHAR(MAX) = N''


    SELECT @ExportPath = CASE WHEN RIGHT(@ExportPath,1) = '\' THEN @ExportPath ELSE CONCAT(@ExportPath,'\') END


    -- Just for testing purpose top 10 records are selected
    SELECT @SQL = CONCAT('SELECT TOP 10 ',Cols,' FROM ',TableName,';')
    ,@TableName = TableName
    FROM #FinalExportList


    SET @PythonScript = N'import shutil
    FullFilePath = ExcelFilePath+"StudentExport.xlsx"
    shutil.copy(FixedPath,ExportPath)
    InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
    'f

    exec sp_execute_external_script
    @language = N'Python'
    ,@script = @PythonScript
    ,@input_data_1 = @SQL
    ,@params = N'@ExcelFilePath NVARCHAR(MAX), @TableName NVARCHAR(200),@FixedPath NVARCHAR(MAX),@ExportPath NVARCHAR(MAX)'
    ,@ExcelFilePath = @ExportPath -- file path where Excel files are placed
    ,@TableName = @TableName
    ,@FixedPath=@FixedPath
    ,@ExportPath=@ExportPath


    CREATE TABLE [dbo].[students](
     [StudentId] [int] NOT NULL,
     [Name] [varchar](50) NULL,
      CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED 
     (
     [StudentId] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     ) ON [PRIMARY]
     GO
     INSERT [dbo].[students] ([StudentId], [Name]) VALUES (1, N'ahmed')




expected result
StudentId StudentName
1          ahmed