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]);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|