Winform批量设置Excel的行高，并设置文本区域为打印区域

1、页面

（1）EEPlus用的是4.0.1，5.0以上的版本功能更强，最新版本不需要借助Microsoft.Office.Interop.Excel即可实现全部功能，但需要授权

 2、选择文件按钮

using (OpenFileDialog openFileDialog = new OpenFileDialog())
{
    openFileDialog.ValidateNames = false;
    openFileDialog.CheckFileExists = false;
    openFileDialog.CheckPathExists = true;
    openFileDialog.FileName = "选择文件夹";

    if (!string.IsNullOrWhiteSpace(textBox1.Text))
    {
        openFileDialog.InitialDirectory = textBox1.Text;
    }

    if (openFileDialog.ShowDialog() == DialogResult.OK)
    {
        textBox1.Text = Path.GetDirectoryName(openFileDialog.FileName);
    }
}

3、开始转换按钮

private void button2_Click(object sender, EventArgs e)
{
if (MessageBox.Show("此操作将处理所有的Excel，请确认要转换的文件没有被占用", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
string folderPath = textBox1.Text;
// 获取文件夹中所有Excel文件

if (!string.IsNullOrEmpty(folderPath))
{
var files = Directory.GetFiles(folderPath, "*.xlsx");
this.button1.Enabled = false;
this.button2.Enabled = false;
if (files.Length > 0)
{
int i = 0;
foreach (var file in files)
{
//用 Microsoft.Office.Interop.Excel（合并列会失效）,用EPPlus自动高度设置有时会失效

//用 Microsoft.Office.Interop.Excel设置自动行高设置
AdjustExcelSettings(file);
//用EPPlus设置合并单元格的行高设置,并设置打印区域
AdjusExcelSeetings2(file);
//AdjusExcelSeetings3(file);
i++;
this.progressBar1.Value = Convert.ToInt32(i * 100 / files.Length);
}
this.progressBar1.Value = 100;
MessageBox.Show("转换完成");
}
else
{
MessageBox.Show("没有找到excel文件");
}
}
else
{
MessageBox.Show("请选择要转换的文件");
}
this.button1.Enabled = false;
this.button2.Enabled = true;
}
}

4、共用方法

public void AdjustExcelSettings(string filePath)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

try
{
// 创建Excel应用实例
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false; // 后台运行
excelApp.DisplayAlerts = false; // 关闭提示

// 打开工作簿
workbook = excelApp.Workbooks.Open(filePath);
worksheet = workbook.Sheets[1]; // 选择第一个工作表

// 自动调整所有使用区域的行高
Microsoft.Office.Interop.Excel.Range usedRange = worksheet.UsedRange;
usedRange.EntireRow.AutoFit();

//foreach (Microsoft.Office.Interop.Excel.Range range in worksheet.UsedRange)
//{
// if (range.MergeCells)
// {
// range.Rows.AutoFit();
// }
//}
// 保存更改
workbook.Save();
workbook.Close();
excelApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show("错误: " + ex.Message);
}
finally
{
// 释放COM对象（重要！）
if (worksheet != null) Marshal.ReleaseComObject(worksheet);
if (workbook != null) Marshal.ReleaseComObject(workbook);
if (excelApp != null) Marshal.ReleaseComObject(excelApp);
}
}

public void AdjusExcelSeetings2(string file)
{
try
{
using (var package = new ExcelPackage(new FileInfo(file)))
{
foreach (var worksheet in package.Workbook.Worksheets)
{

// 遍历所有合并区域
var mergedCells = worksheet.MergedCells.ToList();

foreach (var mergedRange in mergedCells)
{
//其他列自适行高
//foreach (var row in worksheet.Cells.GroupBy(c => c.Start.Row))
//{
// worksheet.Row(row.Key).CustomHeight = true;
//}
//foreach (var row in worksheet.Cells.GroupBy(c => c.Start.Row))
//{
// worksheet.Row(row.Key).Height = worksheet.Row(row.Key).Height * 1.15;
//}

//合并列调整行高
var range = worksheet.Cells[mergedRange];
int startRow = range.Start.Row;
int endRow = range.End.Row;
int startCol = range.Start.Column;
int endCol = range.End.Column;
bool isRowMerged = range.Rows > 1 ? true : false;
//行合并的暂不处理
if (isRowMerged)
{
continue;
}
// 估算行高（示例：根据行数均分）
double totalHeight = 0;
double totalWidth = 0;
for (int row = startRow; row <= endRow; row++)
{
totalHeight += worksheet.Row(row).Height;
}
for (int clo = startCol; clo <= endCol; clo++)
{
totalWidth += worksheet.Column(clo).Width;
}
double avgHeight = totalHeight / (endRow - startRow + 1);
double fontWidth = 0;

using (var bitmap = new Bitmap(1, 1))
using (var graphics = Graphics.FromImage(bitmap))
{
var systemFont = new Font(
Font.Name,
Font.Size,
(Font.Bold ? FontStyle.Bold : FontStyle.Regular) |
(Font.Italic ? FontStyle.Italic : FontStyle.Regular));

// 测量文本宽度（像素）
SizeF size = graphics.MeasureString(((object[,])range.Value)[0, 0].ToString(), systemFont);

// 将像素转换为Excel列宽单位（1字符≈7.5像素）
fontWidth = Math.Min(size.Width / 7.5 + 1, 255); // 限制最大255字符
}
// 设置合并区域所在行的统一行高
for (int row = startRow; row <= endRow; row++)
{
worksheet.Row(row).CustomHeight = true;
if (totalWidth < fontWidth)
{
worksheet.Row(row).Height = avgHeight * ((int)((fontWidth / totalWidth) - 1) == 0 ? 1 : (int)((fontWidth / totalWidth) - 1)) * 1.15;
}
else
{
worksheet.Row(row).Height = avgHeight * 1.15;
}
}
}

// 获取当前工作表的数据范围
var dimension = worksheet.Dimension;
if (dimension != null)
{
// 设置打印区域为数据范围
worksheet.PrinterSettings.PrintArea = worksheet.Cells[
dimension.Start.Row,
dimension.Start.Column,
dimension.End.Row,
dimension.End.Column
];
// 关键设置：将所有列调整为一页
worksheet.PrinterSettings.FitToPage = true;
worksheet.PrinterSettings.FitToWidth = 1; // 1表示将所有列压缩到一页宽度
worksheet.PrinterSettings.FitToHeight = 0; // 0表示不限制页数高度

worksheet.PrinterSettings.Orientation = eOrientation.Portrait;// 设置打印方向为纵向
}
package.Save();
}

}
}
catch (Exception ex)
{
MessageBox.Show("错误: " + ex.Message);
}
}