using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using GeoScene.Data;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using DataTable = System.Data.DataTable;
namespace Cyberpipe
{
class ExpEXCEL
{
/// <summary>
/// 导出EXCEL单个SHEET
/// </summary>
/// <param name="dt"></param>
/// <param name="strSaveFile"></param>
public static void ExpToExcel(DataGridView dt, string strSaveFile,string str)
{
//创建一个EXCEL应用程序
Application excel = new Application();
//是否显示导出过程（显示创建后的EXCEL）
excel.Visible = false;
//定义缺省值
Missing miss = Missing.Value;
//创建一个新的工作簿
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(miss);
Worksheet sheet = workbook.Worksheets[1];
sheet.Name = str;
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dt.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dt.Columns.Count; j++)//每行的列数
{
//将数据填充到对应的单元格中
sheet.Cells[i + 2, j + 1] = dt.Rows[i].Cells[j].Value.ToString();
}
}
sheetStype(sheet, dt);
//保存文件
string strSaveFile2 = strSaveFile;
if (File.Exists(strSaveFile2))
{
int z = 1;
do
{
strSaveFile2 = Path.Combine(Path.GetDirectoryName(strSaveFile) + "\\" + Path.GetFileNameWithoutExtension(strSaveFile) + z + ".xlsx");
z++;
} while (File.Exists(strSaveFile2));
}
sheet.SaveAs(strSaveFile2, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
//关闭表格
workbook.Close(false, miss, miss);
workbooks.Close();
//释放资源
excel.Quit();
}
/// <summary>
/// 将DataTable保存为Excel文件,两个SHEET
/// </summary>
public static void ExpToExcel(DataGridView dt1, DataGridView dt, string strSaveFile)
{
//创建一个EXCEL应用程序
Application excel = new Application();
//是否显示导出过程（显示创建后的EXCEL）
excel.Visible = false;
excel.Workbooks.Add(true);
//定义缺省值
Missing miss = Missing.Value;
//创建一个新的工作簿
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(miss);
Worksheet sheet = workbook.Worksheets[1];
Worksheet sheet1;
if (workbook.Worksheets.Count == 1)
{
sheet1 = (Worksheet)workbook.Worksheets.Add(miss, workbook.ActiveSheet);
}
else {
sheet1 = workbook.Worksheets[2];
}
sheet.Name = "垂直距离";
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dt.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dt.Columns.Count; j++)//每行的列数
{
//将数据填充到对应的单元格中
sheet.Cells[i + 2, j + 1] = dt.Rows[i].Cells[j].Value.ToString();
}
}
sheetStype(sheet, dt);
sheet1.Name = "水平距离";
for (int i = 0; i < dt1.Columns.Count; i++)
{
sheet1.Cells[1, i + 1] = dt1.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dt1.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dt1.Columns.Count; j++)//每行的列数
{
//将数据填充到对应的单元格中
sheet1.Cells[i + 2, j + 1] = dt1.Rows[i].Cells[j].Value.ToString();
}
}
sheetStype(sheet, dt1);
//保存文件
string strSaveFile2 = strSaveFile;
if (File.Exists(strSaveFile2))
{
int z = 1;
do
{
strSaveFile2 = Path.Combine(Path.GetDirectoryName(strSaveFile) + "\\" + Path.GetFileNameWithoutExtension(strSaveFile) + z + ".xlsx");
z++;
} while (File.Exists(strSaveFile2));
}
sheet.SaveAs(strSaveFile2, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
//关闭表格
workbook.Close(false, miss, miss);
workbooks.Close();
//释放资源
excel.Quit();
KillProcess(excel);
}
static List<String> sheetNameList = new List<string>();
/// <summary>
/// 导出EXCEL多个SHEET(红线审核导出用)
/// </summary>
/// <param name="dt"></param>
/// <param name="strSaveFile"></param>
public static void ExpToExcelContain(DataGridView dt, string strSaveFile, string str)
{
if (dt.Rows.Count == 0) return;
sheetNameList.Clear();
string layerName = "";
string redLineName = "";
Application excel = new Application();//创建一个EXCEL应用程序
excel.Visible = false; //是否显示导出过程（显示创建后的EXCEL）
Missing miss = Missing.Value;//定义缺省值
Workbooks workbooks = excel.Workbooks;//创建一个新的工作簿
Workbook workbook = workbooks.Add(miss);
Worksheet sheet = null;
sheet.Name = str;
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dt.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)//所要添加的行数
{
for (int j = 0; j < dt.Columns.Count; j++)//每行的列数
{
sheet.Cells[i + 2, j + 1] = dt.Rows[i].Cells[j].Value.ToString();//将数据填充到对应的单元格中
}
}
sheetStype(sheet, dt);
for (int sheetCount = 0; sheetCount <= dt.Rows.Count; sheetCount++)
{
if (workbook.Worksheets.Count > sheetCount)
sheet = workbook.Worksheets[sheetCount + 1];
else
sheet = (Worksheet) workbook.Worksheets.Add(miss, workbook.ActiveSheet);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dt.Columns[i].HeaderText;
}
DataTable dataTable = new DataTable();
dataTable.Columns.Add("图层类型");
dataTable.Columns.Add("编号");
layerName = dt.Rows[sheetCount - 1].Cells["管线类型"].Value.ToString();
redLineName = dt.Rows[sheetCount - 1].Cells["红线编号"].Value.ToString();
for (int i = 0; i < RedLineAnalysisTool.redLineResultList[sheetCount].LineFeaturesInRedLine.Length; i++)
{
if (RedLineAnalysisTool.redLineResultList[sheetCount].LineFeaturesInRedLine[i].Dataset.Caption == layerName
&& RedLineAnalysisTool.redLineResultList[sheetCount].RedLineName == redLineName)
{
DataRow row = dataTable.NewRow();
row[0] = RedLineAnalysisTool.redLineResultList[sheetCount].LineFeaturesInRedLine[i].Dataset.Caption;
row[1] = RedLineAnalysisTool.redLineResultList[sheetCount].LineFeaturesInRedLine[i].Name;
dataTable.Rows.Add(row);
}
}
if (RedLineAnalysisTool.redLineResultList[sheetCount].PointFeaturesInRedLine.Length > 0)
{
for (int j = 0; j < RedLineAnalysisTool.redLineResultList[sheetCount].PointFeaturesInRedLine.Length;j++)
{
GSOFeatures feats = RedLineAnalysisTool.redLineResultList[sheetCount].PointFeaturesInRedLine;
if (feats[0].Dataset.Caption.Contains(layerName))
{
for (int n = 0; n < feats.Length; n++)
{
DataRow pointRow = dataTable.NewRow();
pointRow[0] = feats[n].Dataset.Caption;
pointRow[1] = feats[n].GetFieldAsString("编号");
dataTable.Rows.Add(pointRow);
}
}
}
}
if (!sheetNameList.Contains(layerName))
{
sheet.Name = layerName;
sheetNameList.Add(layerName);
}
else
{
sheet.Name = layerName + "1";
sheetNameList.Add(layerName + "1");
}
for (int i = 0; i < dataTable.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
}
//填充数据
for (int i = 0; i < dataTable.Rows.Count; i++) //所要添加的行数
{
for (int j = 0; j < dataTable.Columns.Count; j++) //每行的列数
{
sheet.Cells[i + 2, j + 1] = dataTable.Rows[i][j].ToString(); //将数据填充到对应的单元格中
}
}
sheetStype(sheet, dataTable);
}
if (File.Exists(strSaveFile))
{
int z = 1;
do
{
strSaveFile = Path.Combine(Path.GetDirectoryName(strSaveFile) + "\\" + Path.GetFileNameWithoutExtension(strSaveFile) + z + ".xlsx");
z++;
} while (File.Exists(strSaveFile));
}
workbook.SaveAs(strSaveFile, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
workbook.Close(false, miss, miss); //关闭表格
workbooks.Close();
excel.Quit();//释放资源
}
static void sheetStype(Worksheet sheet,DataGridView dataTable)
{
Range er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dataTable.Columns.Count]];//设置列标题的背景颜色
int rowscount = dataTable.Rows.Count;//重新选择单元格范围
int columncount = dataTable.Columns.Count;
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount, 1]];//将范围重新确定为每一行的第一个单元格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount + 1, columncount + 1]];//选中EXCEL所有表格
er.EntireColumn.AutoFit();//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 让EXCEL的文本水平居中方式
}
static void sheetStype(Worksheet sheet, DataTable dataTable)
{
Range er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dataTable.Columns.Count]];//设置列标题的背景颜色
int rowscount = dataTable.Rows.Count;//重新选择单元格范围
int columncount = dataTable.Columns.Count;
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount, 1]];//将范围重新确定为每一行的第一个单元格
er = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowscount + 1, columncount + 1]];//选中EXCEL所有表格
er.EntireColumn.AutoFit();//让EXCEL中的所有单元格的列宽碎文字的长短自动调整
er.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 让EXCEL的文本水平居中方式
}
/// <summary>
/// 杀死进程
/// </summary>
/// <param name="app"></param>
private static void KillProcess(Application app)
{
IntPtr t = new IntPtr(app.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
Process p = Process.GetProcessById(k);
p.Kill();
}
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
}
}
