Hello Everyone,
Is there a way to export contour lines created using python in excel to a DXF file?
Unfortunately ezdxf is not available yet.
Cheers,
Adam
Hi Adam
Interesting question! ezdxf isnât available in the Python in Excel distribution, but you can install it and use it with Anaconda Code.
As for saving .dxf files, the security model of Office.js add-ins means they donât have access to your local computer, so creating and saving files from the add-in isnât possible (at the moment).
However, a dxf file is a text file. So you can theoretically create the lines that would normally be saved in such a file, spill them to the grid, then save the sheet as a text file with dxf extension.
I think it will work. I was interested to find out, so I created a working example.
Unfortunately I canât upload an Excel file to this reply, so weâll have to do it the screenshot and code snippet way!
First, add ezdxf to your environment:
Replace the code in âImports and Definitionsâ with this code:
# Add imports
import io
import math
import tempfile
import os
import numpy as np
import pandas as pd
from matplotlib.collections import LineCollection
import matplotlib.pyplot as plt
import ezdxf
from ezdxf.enums import TextEntityAlignment
# Standard functions provided with Anaconda Code
def to_df(data) -> pd.DataFrame:
"""
Converts a REF (i.e. list of lists) into a pandas DataFrame
Assumes that the first row contains column headers
Usage: df = to_df(REF("F15:H22"))
"""
if isinstance(data, pd.DataFrame):
return data
if hasattr(data, '__len__') and len(data) > 0 and hasattr(data[0], '__len__'):
return pd.DataFrame(data[1:], columns=data[0])
raise TypeError('Expected list of lists')
def to_array(data) -> np.ndarray:
"""
Converts a REF (i.e. list of lists) into a 2-dimensional numpy array
Usage: arr = to_array(REF("B3:C44"))
"""
if isinstance(data, np.ndarray):
return data
return np.array(data)
def to_list(data) -> list:
"""
Converts a REF (i.e. list of lists) into a 1-d list.
Handles wide (1xn) or tall (nx1) data.
Usage: x = to_list(REF("G1:G55"))
"""
if not hasattr(data, '__len__'): # data is scalar
return [data]
if len(data) == 0 or not hasattr(data[0], '__len__'): # data is already a list
return list(data)
if len(data) == 1: # data is wide
return list(data[0])
elif len(data[0]) == 1: #data is tall
return [x[0] for x in data]
else:
raise TypeError(f"data must be 1xn or nx1, not {len(data)}x{len(data[0])}")
# Functions for generating .dxf contour plots and associated lines for saving as dxf files
def generate_synthetic_contour_data(grid_size=100, num_peaks=3):
"""Generate synthetic terrain-like contour data with multiple peaks."""
x = np.linspace(0, 1, grid_size)
y = np.linspace(0, 1, grid_size)
xx, yy = np.meshgrid(x, y)
# Create a height map with multiple peaks
z = np.zeros((grid_size, grid_size))
for _ in range(num_peaks):
# Random peak location and height
peak_x = np.random.random()
peak_y = np.random.random()
peak_height = np.random.uniform(0.5, 1.0)
# Add a gaussian peak
z += peak_height * np.exp(-((xx - peak_x)**2 + (yy - peak_y)**2) / 0.05)
return xx, yy, z
def contours_to_dxf_lines(contours, levels):
"""
Convert matplotlib contour data to DXF text content as a list of lines.
Args:
contours: List of contour line coordinates
levels: Elevation levels corresponding to each contour
Returns:
List of strings, each representing a line in the DXF file
"""
# Create a new DXF document in memory
doc = ezdxf.new('R2010')
# Add a new layer for each contour level
msp = doc.modelspace()
# Process each contour level
for i, level in enumerate(levels):
layer_name = f"CONTOUR_{int(level*100)}"
doc.layers.add(name=layer_name)
# Process each contour line at this level
for contour in contours[i]:
# Extract x and y coordinates
x_coords = contour[:, 0]
y_coords = contour[:, 1]
# Create polyline for this contour
polyline = msp.add_lwpolyline([(x, y) for x, y in zip(x_coords, y_coords)],
dxfattribs={'layer': layer_name})
# Add elevation label at the midpoint
mid_idx = len(x_coords) // 2
text = msp.add_text(
f"Elevation: {level:.2f}",
dxfattribs={
'layer': layer_name,
'height': 0.01,
'style': 'Standard'
}
)
# Position the text
text.set_placement((x_coords[mid_idx], y_coords[mid_idx]),
align=TextEntityAlignment.MIDDLE_CENTER)
# Instead of saving to a file, write to a string buffer
string_buffer = io.StringIO()
doc.write(string_buffer)
# Convert the buffer contents to a list of lines
string_buffer.seek(0)
dxf_lines = string_buffer.readlines()
return dxf_lines
def contours_to_excel_data(contours, levels):
"""
Convert contour data to a format suitable for Excel export.
This creates a list where each item is a line of text from the DXF file.
Each line will be placed in a single cell in Excel.
Args:
contours: Contour data from matplotlib
levels: Elevation levels
Returns:
List of strings, each representing a line in the DXF file
"""
# Get the DXF content as lines
dxf_lines = contours_to_dxf_lines(contours, levels)
# Clean up the lines (remove any trailing newlines)
cleaned_lines = [line.rstrip('\n') for line in dxf_lines]
return cleaned_lines
def plot_dxf_from_lines(dxf_lines):
"""
Takes a list of strings representing a DXF file,
processes it and plots the contours using matplotlib.
Args:
dxf_lines: List of strings, each representing a line in the DXF file
"""
# Create a temporary file to write the DXF content
with tempfile.NamedTemporaryFile(suffix='.dxf', delete=False) as temp_file:
temp_path = temp_file.name
# Write all lines to the temporary file
for line in dxf_lines:
temp_file.write((str(line) + '\n').encode('utf-8'))
try:
# Load the DXF file with ezdxf
doc = ezdxf.readfile(temp_path)
msp = doc.modelspace()
# Extract all polylines (contours)
contour_layers = {}
elevation_data = {}
for entity in msp:
if entity.dxftype() == 'LWPOLYLINE':
layer_name = entity.dxf.layer
# Extract elevation from layer name (format: CONTOUR_XX)
if layer_name.startswith('CONTOUR_'):
try:
elevation = float(layer_name.split('_')[1]) / 100 # Convert back to original scale
except (IndexError, ValueError):
elevation = 0
else:
elevation = 0
# Get vertices
points = list(entity.vertices())
# Add to the appropriate layer
if layer_name not in contour_layers:
contour_layers[layer_name] = []
elevation_data[layer_name] = elevation
contour_layers[layer_name].append(points)
# Create a matplotlib figure
fig, ax = plt.subplots(figsize=(10, 8))
# Plot each contour layer with a different color based on elevation
cmap = plt.cm.viridis
elevations = sorted(elevation_data.values())
norm = plt.Normalize(min(elevations), max(elevations))
# Plot each contour
for layer_name, contours in contour_layers.items():
elevation = elevation_data[layer_name]
color = cmap(norm(elevation))
# Create a line collection for all contours in this layer
lines = []
for contour in contours:
lines.append(contour)
line_collection = LineCollection(lines, colors=color, linewidths=1.5, label=f"{elevation:.2f}")
ax.add_collection(line_collection)
# Set axis limits
all_x = []
all_y = []
for contours in contour_layers.values():
for contour in contours:
x, y = zip(*contour)
all_x.extend(x)
all_y.extend(y)
if all_x and all_y:
ax.set_xlim(min(all_x), max(all_x))
ax.set_ylim(min(all_y), max(all_y))
# Add a title and legend
ax.set_title("Contour Plot from DXF Data")
# Add a colorbar
sm = plt.cm.ScalarMappable(cmap=cmap, norm=norm)
sm.set_array([])
plt.colorbar(sm, ax=ax, label="Elevation")
# Set axis labels
ax.set_xlabel("X")
ax.set_ylabel("Y")
finally:
# Always clean up the temporary file
os.unlink(temp_path)
# No need to call plt.show() here as your add-in will handle that
# Just return the figure for display
return fig
Create a Code cell with this code. It just generates some fake contours and creates a plot. Ensure that the cell is in Linked Mode (see the button in the image below next to the text that says âRun Linkedâ).
x, y, z = generate_synthetic_contour_data(grid_size=100, num_peaks=3)
fig, ax = plt.subplots(figsize=(10, 8))
levels = np.linspace(0.1, 1.0, 10)
contour_set = ax.contour(x, y, z, levels=levels)
plt.show()
Optionally you can hit the âCreate referenceâ button on the spreadsheet to see a bigger image of the plot. For example:
On a new sheet to the right of the sheet mentioned above, create a new code cell in A1 with this code. Again, ensure the cell is in Linked Mode:
contour_paths = []
for collection in contour_set.collections:
paths = []
for path in collection.get_paths():
paths.append(path.vertices)
contour_paths.append(paths)
# Convert contours to DXF text content
print("Converting contours to DXF text content...")
dxf_lines = contours_to_excel_data(contour_paths, levels)
That code creates the text lines that would normally be saved in the dxf file (which is just a text file). You can then save your workbook, then select this sheet and use âSave Asâ to save the sheet as a text file, giving it a file name with the dxf extension.
To verify that the lines produced in the step above are valid dxf file lines, you can create a third sheet and use this code in a Code cell which is again in Linked Mode:
fig = plot_dxf_from_lines(dxf_lines)
That code reads the dxf lines from the second sheet and recreates the plot from scratch. The created plot should be the same as the original plot on the first sheet.
dfx files arenât my area of expertise, so it would be interesting to find out if you are able to use a dxf file created from the dxf lines sheet in some other program with any degree of success.
Thanks and let me know!
Owen
1 Like
Owen, youâre a legend, mate!
Unfortunately, I canât try it out right now because my IT department blocked Python functionality for me last night. But Iâll give it a go as soon as itâs back up and running. Canât wait!