|
|
|
|
using ESRI.ArcGIS.Geodatabase;
|
|
|
|
|
using System;
|
|
|
|
|
using System.Collections.Generic;
|
|
|
|
|
using System.Data;
|
|
|
|
|
using System.Linq;
|
|
|
|
|
using System.Runtime.InteropServices;
|
|
|
|
|
using System.Text;
|
|
|
|
|
using System.Threading.Tasks;
|
|
|
|
|
|
|
|
|
|
namespace KGIS.Plugin.LayerProperty2.Utils
|
|
|
|
|
{
|
|
|
|
|
public class ExportExcel
|
|
|
|
|
{
|
|
|
|
|
public static string ExportData(List<DataColumnEx> lstDataColumnEx, DataTable dtData)
|
|
|
|
|
{
|
|
|
|
|
System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
|
|
|
|
|
if (lstDataColumnEx != null && lstDataColumnEx.Count > 0)
|
|
|
|
|
{
|
|
|
|
|
List<string> headers = new List<string>();
|
|
|
|
|
lstDataColumnEx.ForEach(col =>
|
|
|
|
|
{
|
|
|
|
|
headers.Add(FormatCSVField(col.ChineseName));
|
|
|
|
|
});
|
|
|
|
|
string headerStr = String.Join("", headers.ToArray());
|
|
|
|
|
headerStr = headerStr.TrimStart('\t');
|
|
|
|
|
strBuilder
|
|
|
|
|
.Append(headerStr)
|
|
|
|
|
.Append("\t\n");
|
|
|
|
|
|
|
|
|
|
foreach (DataRow dr in dtData.Rows)
|
|
|
|
|
{
|
|
|
|
|
List<string> csvRow = new List<string>();
|
|
|
|
|
foreach (DataColumnEx col in lstDataColumnEx)
|
|
|
|
|
{
|
|
|
|
|
csvRow.Add(FormatCSVField(dr[col.ColumnName].ToTrim()));
|
|
|
|
|
}
|
|
|
|
|
string fieldStr = String.Join("", csvRow.ToArray());
|
|
|
|
|
fieldStr = fieldStr.TrimStart('\t');
|
|
|
|
|
strBuilder
|
|
|
|
|
.Append(fieldStr)
|
|
|
|
|
.Append("\t\n");
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
return strBuilder.ToString();
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public static string ExportData(List<DataColumnEx> lstDataColumnEx)
|
|
|
|
|
{
|
|
|
|
|
System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
|
|
|
|
|
if (lstDataColumnEx != null && lstDataColumnEx.Count > 0)
|
|
|
|
|
{
|
|
|
|
|
List<string> headers = new List<string>();
|
|
|
|
|
lstDataColumnEx.ForEach(col =>
|
|
|
|
|
{
|
|
|
|
|
headers.Add(FormatCSVField(col.ChineseName));
|
|
|
|
|
});
|
|
|
|
|
string headerStr = String.Join("", headers.ToArray());
|
|
|
|
|
//headerStr = headerStr.TrimStart('\t');
|
|
|
|
|
strBuilder
|
|
|
|
|
.Append(headerStr)
|
|
|
|
|
.Append("\t\n");
|
|
|
|
|
}
|
|
|
|
|
return strBuilder.ToString();
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public static string ExportData(DataTable dtData)
|
|
|
|
|
{
|
|
|
|
|
System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
|
|
|
|
|
if (dtData != null && dtData.Rows.Count > 0)
|
|
|
|
|
{
|
|
|
|
|
List<string> headers = new List<string>();
|
|
|
|
|
foreach (CustomColumn item in dtData.Columns)
|
|
|
|
|
{
|
|
|
|
|
headers.Add(FormatCSVField(item.Caption));
|
|
|
|
|
}
|
|
|
|
|
string headerStr = String.Join("", headers.ToArray());
|
|
|
|
|
headerStr = headerStr.TrimStart('\t');
|
|
|
|
|
strBuilder
|
|
|
|
|
.Append(headerStr)
|
|
|
|
|
.Append("\t\n");
|
|
|
|
|
|
|
|
|
|
foreach (DataRow dr in dtData.Rows)
|
|
|
|
|
{
|
|
|
|
|
List<string> csvRow = new List<string>();
|
|
|
|
|
foreach (CustomColumn item in dtData.Columns)
|
|
|
|
|
{
|
|
|
|
|
if (item.DicData != null && !string.IsNullOrEmpty(dr[item.ColumnName] as string))
|
|
|
|
|
{
|
|
|
|
|
csvRow.Add(FormatCSVField(GetDicValue(dr[item.ColumnName].ToString(), item.DicData)));
|
|
|
|
|
}
|
|
|
|
|
else
|
|
|
|
|
{
|
|
|
|
|
csvRow.Add(FormatCSVField(dr[item.ColumnName].ToTrim()));
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
string fieldStr = String.Join("", csvRow.ToArray());
|
|
|
|
|
fieldStr = fieldStr.TrimStart('\t');
|
|
|
|
|
strBuilder
|
|
|
|
|
.Append(fieldStr)
|
|
|
|
|
.Append("\t\n");
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
return strBuilder.ToString();
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
private static string GetDicValue(string value, List<ComboxData> lstData)
|
|
|
|
|
{
|
|
|
|
|
string returnValue = string.Empty;
|
|
|
|
|
string[] valueArr = value.Split(',');
|
|
|
|
|
foreach (string subValue in valueArr)
|
|
|
|
|
{
|
|
|
|
|
ComboxData comboxData = lstData.SingleOrDefault(d => d.Value == subValue);
|
|
|
|
|
if (comboxData != null)
|
|
|
|
|
{
|
|
|
|
|
returnValue += comboxData.Text + ",";
|
|
|
|
|
}
|
|
|
|
|
else
|
|
|
|
|
{
|
|
|
|
|
returnValue += subValue + ",";
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
if (!string.IsNullOrEmpty(returnValue))
|
|
|
|
|
{
|
|
|
|
|
returnValue = returnValue.TrimEnd(',');
|
|
|
|
|
}
|
|
|
|
|
return returnValue;
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
//public static string ToTrim(this object text)
|
|
|
|
|
//{
|
|
|
|
|
// if (text == null)
|
|
|
|
|
// return "";
|
|
|
|
|
// else return text.ToString().Trim();
|
|
|
|
|
//}
|
|
|
|
|
|
|
|
|
|
private static string FormatCSVField(string data)
|
|
|
|
|
{
|
|
|
|
|
return String.Format("\t{0}", data.Replace("\"", "\t\n"));
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public static void CreateAndAddTitle(string filePath, string sheetName, List<DataColumnEx> lstDataColumnEx, DataTable dtData)
|
|
|
|
|
{
|
|
|
|
|
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
|
|
|
|
|
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(true);
|
|
|
|
|
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
|
|
|
|
|
worksheet.Name = sheetName;
|
|
|
|
|
int colIndex = 0;
|
|
|
|
|
foreach (DataColumnEx col in lstDataColumnEx)
|
|
|
|
|
{
|
|
|
|
|
colIndex++;
|
|
|
|
|
worksheet.Cells[1, colIndex] = col.ChineseName;
|
|
|
|
|
}
|
|
|
|
|
SetFormat(worksheet, lstDataColumnEx, 2, dtData.Rows.Count);
|
|
|
|
|
AddRangeData(worksheet, dtData, 2);
|
|
|
|
|
Marshal.ReleaseComObject(worksheet);
|
|
|
|
|
excel.DisplayAlerts = false;
|
|
|
|
|
workbook.SaveCopyAs(filePath);
|
|
|
|
|
workbook.Close(false, null, null);
|
|
|
|
|
Marshal.ReleaseComObject(workbook);
|
|
|
|
|
excel.Workbooks.Close();
|
|
|
|
|
workbook = null;
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
private static void SetFormat(Microsoft.Office.Interop.Excel.Worksheet worksheet, List<DataColumnEx> lstDataColumnEx, int startRow, int rowNumber)
|
|
|
|
|
{
|
|
|
|
|
int colIndex = 0;
|
|
|
|
|
foreach (DataColumnEx col in lstDataColumnEx)
|
|
|
|
|
{
|
|
|
|
|
colIndex++;
|
|
|
|
|
if (col.Type == ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeString)
|
|
|
|
|
{
|
|
|
|
|
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[startRow, colIndex], worksheet.Cells[rowNumber + startRow - 1, colIndex]);
|
|
|
|
|
range.NumberFormatLocal = "@";
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public static void AddRangeData(Microsoft.Office.Interop.Excel.Worksheet worksheet, DataTable dtData, int startRow, bool isAddBK = false)
|
|
|
|
|
{
|
|
|
|
|
int rowNumber = dtData.Rows.Count;
|
|
|
|
|
int columnNumber = dtData.Columns.Count;
|
|
|
|
|
object[,] objData = new object[rowNumber, columnNumber];
|
|
|
|
|
for (int r = 0; r < rowNumber; r++)
|
|
|
|
|
{
|
|
|
|
|
for (int c = 0; c < columnNumber; c++)
|
|
|
|
|
{
|
|
|
|
|
objData[r, c] = dtData.Rows[r][c];
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[startRow, 1], worksheet.Cells[rowNumber + startRow - 1, columnNumber]);
|
|
|
|
|
if (isAddBK)
|
|
|
|
|
{
|
|
|
|
|
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
|
|
|
|
|
//for (int i = startRow; i < rowNumber + startRow; i++)
|
|
|
|
|
//{
|
|
|
|
|
// for (int j = 1; j < columnNumber + 1; j++)
|
|
|
|
|
// {
|
|
|
|
|
// ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
|
|
|
|
|
// ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
|
|
|
|
|
// ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
|
|
|
|
|
// }
|
|
|
|
|
//}
|
|
|
|
|
}
|
|
|
|
|
range.Value2 = objData;
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public static void CopfFileAndAddData(string filePath, string fileName, DataTable dtZD, DataTable dtQL, DataTable dtQLR)
|
|
|
|
|
{
|
|
|
|
|
string sourcePath = System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, "宗地整理模版", fileName);
|
|
|
|
|
string destPath = System.IO.Path.Combine(filePath, fileName);
|
|
|
|
|
System.IO.File.Copy(sourcePath, destPath, true);
|
|
|
|
|
|
|
|
|
|
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
|
|
|
|
|
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(destPath);
|
|
|
|
|
|
|
|
|
|
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
|
|
|
|
|
AddRangeData(worksheet, dtZD, 3, true);
|
|
|
|
|
Marshal.ReleaseComObject(worksheet);
|
|
|
|
|
if (dtQL != null && dtQL.Rows.Count > 0)
|
|
|
|
|
{
|
|
|
|
|
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[2];
|
|
|
|
|
AddRangeData(worksheet, dtQL, 3, true);
|
|
|
|
|
Marshal.ReleaseComObject(worksheet);
|
|
|
|
|
}
|
|
|
|
|
if (dtQLR != null && dtQLR.Rows.Count > 0)
|
|
|
|
|
{
|
|
|
|
|
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[3];
|
|
|
|
|
AddRangeData(worksheet, dtQLR, 3, true);
|
|
|
|
|
Marshal.ReleaseComObject(worksheet);
|
|
|
|
|
}
|
|
|
|
|
excel.DisplayAlerts = false;
|
|
|
|
|
workbook.SaveAs(destPath);
|
|
|
|
|
workbook.Close(false, null, null);
|
|
|
|
|
Marshal.ReleaseComObject(workbook);
|
|
|
|
|
excel.Workbooks.Close();
|
|
|
|
|
workbook = null;
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
public static bool DataTableToExcel(DataTable dt, string filePath)
|
|
|
|
|
{
|
|
|
|
|
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
|
|
|
|
|
if (app == null)
|
|
|
|
|
{
|
|
|
|
|
throw new Exception("Excel无法启动");
|
|
|
|
|
}
|
|
|
|
|
//app.Visible = true;
|
|
|
|
|
//Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
|
|
|
|
|
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(true);
|
|
|
|
|
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
|
|
|
|
|
|
|
|
|
|
int cnt = dt.Rows.Count;
|
|
|
|
|
int columncnt = dt.Columns.Count;
|
|
|
|
|
|
|
|
|
|
// *****************获取数据********************
|
|
|
|
|
object[,] objData = new System.Object[cnt + 1, columncnt]; // 创建缓存数据
|
|
|
|
|
// 获取列标题
|
|
|
|
|
for (int i = 0; i < columncnt; i++)
|
|
|
|
|
{
|
|
|
|
|
objData[0, i] = dt.Columns[i].ColumnName;
|
|
|
|
|
}
|
|
|
|
|
// 获取具体数据
|
|
|
|
|
for (int i = 0; i < cnt; i++)
|
|
|
|
|
{
|
|
|
|
|
System.Data.DataRow dr = dt.Rows[i];
|
|
|
|
|
for (int j = 0; j < columncnt; j++)
|
|
|
|
|
{
|
|
|
|
|
objData[i + 1, j] = dr[j];
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
//********************* 写入Excel******************
|
|
|
|
|
Microsoft.Office.Interop.Excel.Range r = ws.get_Range(app.Cells[1, 1], app.Cells[cnt + 1, columncnt]);
|
|
|
|
|
r.NumberFormat = "@";
|
|
|
|
|
//r = r.get_Resize(cnt+1, columncnt);
|
|
|
|
|
r.Value2 = objData;
|
|
|
|
|
r.EntireColumn.AutoFit();
|
|
|
|
|
//string path = GetAssemblyPath();
|
|
|
|
|
//path = path + "日志";
|
|
|
|
|
//if (!System.IO.Directory.Exists(path))
|
|
|
|
|
//{
|
|
|
|
|
// System.IO.Directory.CreateDirectory(path);
|
|
|
|
|
//}
|
|
|
|
|
//path = path + "\\批量生成不动产单元编号Log";
|
|
|
|
|
//if (!System.IO.Directory.Exists(path))
|
|
|
|
|
//{
|
|
|
|
|
// System.IO.Directory.CreateDirectory(path);
|
|
|
|
|
//}
|
|
|
|
|
wb.SaveCopyAs(filePath);//path + "\\" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx"
|
|
|
|
|
app = null;
|
|
|
|
|
return true;
|
|
|
|
|
}
|
|
|
|
|
/// <summary>
|
|
|
|
|
/// 获取Assembly的运行路径 \\结束
|
|
|
|
|
/// </summary>
|
|
|
|
|
/// <returns></returns>
|
|
|
|
|
public static string GetAssemblyPath()
|
|
|
|
|
{
|
|
|
|
|
string sCodeBase = System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
|
|
|
|
|
sCodeBase = sCodeBase.Substring(8, sCodeBase.Length - 8); // 8是 file:// 的长度
|
|
|
|
|
string[] arrSection = sCodeBase.Split(new char[] { '/' });
|
|
|
|
|
string sDirPath = "";
|
|
|
|
|
for (int i = 0; i < arrSection.Length - 1; i++)
|
|
|
|
|
{
|
|
|
|
|
sDirPath += arrSection[i] + System.IO.Path.DirectorySeparatorChar;
|
|
|
|
|
}
|
|
|
|
|
return sDirPath;
|
|
|
|
|
}
|
|
|
|
|
}
|
|
|
|
|
}
|