375 lines
16 KiB
C#
375 lines
16 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Data.OleDb;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Windows;
|
||
|
||
using ExportExcelTest.Models;
|
||
|
||
using GeologyToolkit;
|
||
|
||
using Microsoft.Win32;
|
||
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.SS.Util;
|
||
using NPOI.XSSF.UserModel;
|
||
|
||
namespace ExportExcelTest
|
||
{
|
||
/// <summary>
|
||
/// MainWindow.xaml 的交互逻辑
|
||
/// </summary>
|
||
public partial class MainWindow : Window
|
||
{
|
||
public MainWindow() { InitializeComponent(); }
|
||
|
||
private void CreateCell(IRow row, int column, object value)
|
||
{
|
||
var cell = row.CreateCell(column);
|
||
if (value is int intValue)
|
||
{
|
||
cell.SetCellValue(intValue);
|
||
}
|
||
if (value is double doubleValue)
|
||
{
|
||
cell.SetCellValue(doubleValue);
|
||
}
|
||
else if (value is string stringValue)
|
||
{
|
||
cell.SetCellValue(stringValue);
|
||
}
|
||
else
|
||
{
|
||
cell.SetCellValue(value.ToString());
|
||
}
|
||
}
|
||
|
||
private static ICellStyle CreateCellStyle(IWorkbook workbook)
|
||
{
|
||
// 创建一个新的单元格样式
|
||
var style = workbook.CreateCellStyle();
|
||
// 创建一个新的字体样式
|
||
var font = workbook.CreateFont();
|
||
// 设置字体为
|
||
font.FontName = "宋体";
|
||
// 设置字体为加粗
|
||
//font.IsBold = true;
|
||
// 设置字体颜色为白色
|
||
//font.Color = IndexedColors.Black.Index;
|
||
// 将创建的字体样式应用到单元格样式中
|
||
style.SetFont(font);
|
||
// 设置单元格填充前景色为蓝色
|
||
//style.FillForegroundColor = IndexedColors.Blue.Index;
|
||
// 设置填充模式为实心
|
||
//style.FillPattern = FillPattern.SolidForeground;
|
||
// 设置上边框为细线样式
|
||
style.BorderTop = BorderStyle.Thin;
|
||
// 设置下边框为细线样式
|
||
style.BorderBottom = BorderStyle.Thin;
|
||
// 设置左边框为细线样式
|
||
style.BorderLeft = BorderStyle.Thin;
|
||
// 设置右边框为细线样式
|
||
style.BorderRight = BorderStyle.Thin;
|
||
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // 添加水平居中
|
||
// 设置垂直对齐方式为居中
|
||
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
|
||
|
||
return style;
|
||
}
|
||
|
||
private static ICellStyle CreateHeaderStyle(IWorkbook workbook)
|
||
{
|
||
ICellStyle headerStyle = workbook.CreateCellStyle();
|
||
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
||
headerStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
|
||
headerStyle.BorderBottom = BorderStyle.Thin;
|
||
headerStyle.BorderTop = BorderStyle.Thin;
|
||
headerStyle.BorderLeft = BorderStyle.Thin;
|
||
headerStyle.BorderRight = BorderStyle.Thin;
|
||
IFont font = workbook.CreateFont();
|
||
font.FontName = "宋体";
|
||
font.IsBold = true;
|
||
font.FontHeightInPoints = 11;
|
||
headerStyle.SetFont(font);
|
||
return headerStyle;
|
||
}
|
||
//创建数据示例
|
||
private SheetTable CreateSampleData(TypeOfSurrounding surrounding)
|
||
{
|
||
var st = new SheetTable(surrounding);
|
||
//一级开项
|
||
var kw = st.AddMain("开挖");
|
||
//二级开项
|
||
var tf = kw.AddSub("土方");
|
||
//细项
|
||
var pt = tf.AddDetail("普通开挖");
|
||
//计量方式，计量单位
|
||
pt.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 45.463).SetProof(4, 481.95);
|
||
pt.AddMeature(MeasurementUnit.Kilogram).SetProof(1, 48.596).SetProof(3, 78.894);
|
||
//细项
|
||
var jx = tf.AddDetail("机械开挖");
|
||
//计量方式，计量单位
|
||
jx.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 78.562).SetProof(5, 484.89);
|
||
//二级开项及细项
|
||
var sf = kw.AddSub("石方").AddDetail("爆破开挖");
|
||
//计量方式，计量单位
|
||
sf.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 487.84).SetProof(6, 15.6);
|
||
sf.AddMeature(MeasurementUnit.Kilogram).SetProof(1, 153.26).SetProof(3, 48.596);
|
||
|
||
|
||
var cqwg = st.AddMain("衬砌圬工");
|
||
var ec = cqwg.AddSub("二衬");
|
||
|
||
var c30 = ec.AddDetail("C30钢筋砼");
|
||
c30.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 48.596).SetProof(3, 78.894);
|
||
c30.AddMeature(MeasurementUnit.Kilonewton).SetProof(2, 48.6).SetProof(4, 78.894);
|
||
|
||
var c30F = ec.AddDetail("C30钢筋砼 防水型");
|
||
c30F.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 48.596).SetProof(3, 78.894);
|
||
c30F.AddMeature(MeasurementUnit.CubicMeter).SetProof(2, 48.6).SetProof(4, 78.894);
|
||
|
||
var c40 = ec.AddDetail("C40钢筋砼");
|
||
c40.AddMeature(MeasurementUnit.Kilonewton).SetProof(1, 48.596).SetProof(3, 78.894);
|
||
|
||
return st;
|
||
}
|
||
private void CreateSheetTitle(ISheet sheet, Measurement measurement)
|
||
{
|
||
var proofs = measurement.Proofs;
|
||
TypeOfSurrounding typeOfSurrounding = measurement.Detail.SubItem.Primary.Table.TypeOfSurrounding;
|
||
// 创建字体和样式
|
||
var workbook = sheet.Workbook;
|
||
var style = CreateHeaderStyle(workbook);
|
||
|
||
// 创建第一行（合并行）
|
||
IRow row0 = sheet.CreateRow(0);
|
||
string[] headerTitles = { "项目及建议材料说明", "单位", "支护类型", $"{typeOfSurrounding.GetDescription()}合计", $"TC-300-围岩类型：{typeOfSurrounding.GetDescription()}", "BIM算量规则", "备注及说明" };
|
||
//项目及建议材料说明
|
||
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2));
|
||
CreateCell(row0, 0, headerTitles[0]);
|
||
//单位
|
||
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 3, 3));
|
||
CreateCell(row0, 3, headerTitles[1]);
|
||
|
||
// 支护类型合并水平方向（跨proofs.Count列）
|
||
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 4, proofs.Count + 3));
|
||
CreateCell(row0, 4, headerTitles[2]);
|
||
|
||
IRow row1 = sheet.CreateRow(1);
|
||
for (int i = 0; i < proofs.Count; i++)
|
||
{
|
||
var item = proofs.ElementAt(i);
|
||
|
||
CreateCell(row1, i + 4, item.TypeOfSupport);
|
||
}
|
||
//合计
|
||
sheet.AddMergedRegion(new CellRangeAddress(0, 1, proofs.Count + 4, proofs.Count + 4));
|
||
CreateCell(row0, proofs.Count + 4, headerTitles[3]);
|
||
|
||
//围岩类型
|
||
sheet.AddMergedRegion(new CellRangeAddress(0, 0, proofs.Count + 5, proofs.Count + 6));
|
||
CreateCell(row0, proofs.Count + 5, headerTitles[4]);
|
||
//BIM算量规则及描述
|
||
CreateCell(row1, proofs.Count + 5, headerTitles[5]);
|
||
CreateCell(row1, proofs.Count + 6, headerTitles[6]);
|
||
for (int i = 0; i < 2; i++)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
for (int j = 0; j < measurement.Proofs.Count + 7; j++)
|
||
{
|
||
ICell cell = row.GetCell(j) ?? row.CreateCell(j);
|
||
cell.CellStyle = style;
|
||
sheet.AutoSizeColumn(j);
|
||
}
|
||
}
|
||
}
|
||
|
||
private void ExportButton_Click(object sender, RoutedEventArgs e)
|
||
{
|
||
var table = CreateSampleData(TypeOfSurrounding.LevelThree);
|
||
var table1 = CreateSampleData(TypeOfSurrounding.LevelFive);
|
||
var saveDialog = new SaveFileDialog
|
||
{
|
||
Filter = "Excel文件|*.xlsx",
|
||
FileName = $"工程量表.xlsx",
|
||
InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
|
||
};
|
||
|
||
if (saveDialog.ShowDialog() == true)
|
||
{
|
||
ExportToExcel(new List<SheetTable>() { table, table1 }, saveDialog.FileName, 2, 0);
|
||
MessageBox.Show("导出成功！", "完成", MessageBoxButton.OK, MessageBoxImage.Information);
|
||
}
|
||
}
|
||
|
||
private void ExportToExcel(List<SheetTable> tables, string filePath, int startRowIndex, int startColumnIndex)
|
||
{
|
||
var workbook = new XSSFWorkbook();
|
||
// 创建样式
|
||
var style = CreateCellStyle(workbook);
|
||
//var borderStyle = CreateBorderStyle(workbook);
|
||
|
||
foreach (SheetTable table in tables)
|
||
{
|
||
int startRowIndexTemp = startRowIndex;
|
||
int startColumnIndexTemp = startColumnIndex;
|
||
|
||
var sheet = workbook.CreateSheet(table.Name);
|
||
var measurement = table.MainCategories.FirstOrDefault().SubCategories.FirstOrDefault().DetailCategories.FirstOrDefault().MeasureTypes.FirstOrDefault();
|
||
CreateSheetTitle(sheet, measurement);
|
||
foreach (var mainCat in table.MainCategories)
|
||
{
|
||
int mainStartRow = startRowIndexTemp;
|
||
|
||
foreach (var subCat in mainCat.SubCategories)
|
||
{
|
||
int subStartRow = startRowIndexTemp;
|
||
|
||
foreach (var detailCate in subCat.DetailCategories)
|
||
{
|
||
int detailStartRow = startRowIndexTemp;
|
||
foreach (var measureType in detailCate.MeasureTypes)
|
||
{
|
||
int measureStartRow = startRowIndexTemp;
|
||
IRow row = sheet.CreateRow(startRowIndexTemp);
|
||
// 只在第一次出现时创建主分类单元格
|
||
if (startRowIndexTemp == mainStartRow)
|
||
{
|
||
CreateCell(row, startColumnIndexTemp, mainCat.Name);
|
||
}
|
||
// 只在第一次出现时创建子分类单元格
|
||
if (startRowIndexTemp == subStartRow)
|
||
{
|
||
CreateCell(row, startColumnIndexTemp + 1, subCat.Name);
|
||
}
|
||
// 只在第一次出现时创建细项分类单元格
|
||
if (startRowIndexTemp == detailStartRow)
|
||
{
|
||
CreateCell(row, startColumnIndexTemp + 2, detailCate.Name);
|
||
}
|
||
// 只在第一次出现时创建计量方式单元格
|
||
if (startRowIndexTemp == measureStartRow)
|
||
{
|
||
CreateCell(row, startColumnIndexTemp + 3, measureType.Unit.GetDescription());
|
||
}
|
||
|
||
for (int i = 0; i < measureType.Proofs.Count; i++)
|
||
{
|
||
var proof = measureType.Proofs.ElementAt(i);
|
||
CreateCell(row, startColumnIndexTemp + 4 + i, proof.Amount);
|
||
}
|
||
//合计
|
||
CreateCell(row, startColumnIndexTemp + 4 + measureType.Proofs.Count, measureType.Total);
|
||
|
||
CreateCell(row, startColumnIndexTemp + 5 + measureType.Proofs.Count, measureType.CalculationRule);
|
||
CreateCell(row, startColumnIndexTemp + 6 + measureType.Proofs.Count, measureType.Description);
|
||
|
||
startRowIndexTemp++;
|
||
|
||
}
|
||
//合并细项单元格
|
||
if (startRowIndexTemp > detailStartRow + 1)
|
||
{
|
||
sheet.AddMergedRegion(new CellRangeAddress(
|
||
detailStartRow,
|
||
startRowIndexTemp - 1,
|
||
startColumnIndexTemp + 2,
|
||
startColumnIndexTemp + 2));
|
||
|
||
//sheet.GetRow(detailStartRow).GetCell(startColumnIndexTemp + 2).CellStyle = style;
|
||
}
|
||
}
|
||
|
||
// 合并子分类单元格
|
||
if (startRowIndexTemp > subStartRow + 1)
|
||
{
|
||
sheet.AddMergedRegion(new CellRangeAddress(
|
||
subStartRow,
|
||
startRowIndexTemp - 1,
|
||
startColumnIndexTemp + 1,
|
||
startColumnIndexTemp + 1));
|
||
//sheet.GetRow(subStartRow).GetCell(startColumnIndexTemp + 1).CellStyle = subStyle;
|
||
|
||
}
|
||
}
|
||
|
||
// 合并主分类单元格
|
||
if (startRowIndexTemp > mainStartRow + 1)
|
||
{
|
||
sheet.AddMergedRegion(new CellRangeAddress(mainStartRow, startRowIndexTemp - 1, startColumnIndexTemp, startColumnIndexTemp));
|
||
//sheet.GetRow(mainStartRow).GetCell(startColumnIndexTemp).CellStyle = mainStyle;
|
||
}
|
||
}
|
||
|
||
// 从第二行开始调整样式
|
||
for (int i = 2; i < sheet.LastRowNum + 1; i++)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
for (int j = 0; j < measurement.Proofs.Count + 7; j++)
|
||
{
|
||
ICell cell = row.GetCell(j) ?? row.CreateCell(j);
|
||
cell.CellStyle = style;
|
||
sheet.AutoSizeColumn(j);
|
||
}
|
||
}
|
||
// 遍历所有合并区域并设置样式
|
||
//for (int i = 0; i < sheet.NumMergedRegions; i++)
|
||
//{
|
||
// CellRangeAddress region = sheet.GetMergedRegion(i);
|
||
// IRow row = sheet.GetRow(region.FirstRow);
|
||
// ICell cell = row.GetCell(region.FirstColumn);
|
||
// cell.CellStyle = style;
|
||
//}
|
||
}
|
||
|
||
using (var fs = new FileStream(filePath, FileMode.Create))
|
||
{
|
||
workbook.Write(fs);
|
||
}
|
||
}
|
||
|
||
private void Button_Click(object sender, RoutedEventArgs e)
|
||
{
|
||
var dialog = new OpenFileDialog() { };
|
||
var result = dialog.ShowDialog();
|
||
if (result == true)
|
||
{
|
||
//using (OleDbConnection oleDbConnection = new OleDbConnection())
|
||
//{
|
||
// oleDbConnection.ConnectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={dialog.FileName}";
|
||
// OleDbCommand oleDbCommand = new OleDbCommand();
|
||
// oleDbCommand.CommandText = "select * from [zksd]";
|
||
// oleDbCommand.Connection = oleDbConnection;
|
||
|
||
// using (OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
|
||
// {
|
||
// DataTable dt = new DataTable();
|
||
// da.Fill(dt);
|
||
// dg.DataContext = dt;
|
||
// }
|
||
//}
|
||
MdbHelper mdbHelper = new MdbHelper(dialog.FileName);
|
||
mdbHelper.Open();
|
||
var tables = mdbHelper.GetTables();
|
||
|
||
foreach (DataRow row in tables.Rows)
|
||
{
|
||
var tableName = row["TABLE_NAME"].ToString();
|
||
var ds = mdbHelper.GetDataSetByTableName(tableName);
|
||
var dt = ds.Tables[0];
|
||
foreach (DataRow r in dt.Rows)
|
||
{
|
||
foreach (DataColumn c in dt.Columns)
|
||
{
|
||
Console.WriteLine(r[c]);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
}
|
||
}
|