You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
194 lines
9.4 KiB
C#
194 lines
9.4 KiB
C#
using System;
|
|
using System.Collections;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.Odbc;
|
|
using System.Data.OleDb;
|
|
using System.Diagnostics;
|
|
using System.IO;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using NetLibrary.Data;
|
|
using NPOI.HSSF.UserModel;
|
|
using NPOI.HSSF.Util;
|
|
using NPOI.SS.UserModel;
|
|
using NPOI.SS.Util;
|
|
using NetLibrary.ReportPrint;
|
|
using NetLibrary;
|
|
|
|
namespace TradeManageNew
|
|
{
|
|
public class MicrosoftExcelNew
|
|
{
|
|
|
|
public string Export(DataTable tb, NetLibrary.ReportPrint.TableColumnCollection Columns)
|
|
{
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
sheet.PrintSetup.Scale = 100;
|
|
//填充表头
|
|
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
foreach (NetLibrary.ReportPrint.TableColumn item in Columns)
|
|
{
|
|
int index = Columns.IndexOf(item);
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
dataCell.SetCellValue(item.Caption);
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
//边框
|
|
//dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
//dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
//dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
//dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
////边框颜色
|
|
//dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
//dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
//dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
//dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
}
|
|
|
|
//填充内容
|
|
for (int i = 0; i < tb.Rows.Count; i++)
|
|
{
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
|
|
HSSFCell dataCell = null;
|
|
foreach (NetLibrary.ReportPrint.TableColumn item in Columns)
|
|
{
|
|
int j = Columns.IndexOf(item);
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
if (item.Format != "")
|
|
{
|
|
dataCell.SetCellValue(DataConvert.Format(tb.Rows[i][item.Name].ToString(), item.Format));
|
|
}
|
|
else
|
|
{
|
|
if (item.DataType == DbType.Decimal && tb.Rows[i][item.Name] != null && tb.Rows[i][item.Name].ToString() != "")
|
|
dataCell.SetCellValue(Convert.ToDouble(tb.Rows[i][item.Name]));
|
|
else if (item.DataType != DbType.Decimal)
|
|
dataCell.SetCellValue(tb.Rows[i][item.Name].ToString());
|
|
}
|
|
|
|
|
|
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
//边框
|
|
//dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
//dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
//dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
//dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
////边框颜色
|
|
//dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
//dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
//dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
//dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
}
|
|
}
|
|
foreach (NetLibrary.ReportPrint.TableColumn item in Columns)
|
|
{
|
|
int index = Columns.IndexOf(item);
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
else { sheet.AutoSizeColumn(index); }
|
|
}
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
//保存
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
{
|
|
workbook.Write(fs);
|
|
}
|
|
workbook.Dispose();
|
|
return "ServerCookies/" + filename;
|
|
}
|
|
|
|
|
|
public string Export<T>(List<T> lst, NetLibrary.ReportPrint.TableColumnCollection Columns) where T : class
|
|
{
|
|
Type t = typeof(T);
|
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
|
|
//sheet.PrintSetup.PaperSize = 9;
|
|
sheet.PrintSetup.FitHeight = 0;
|
|
sheet.PrintSetup.FitWidth = 0;
|
|
sheet.PrintSetup.Scale = 100;
|
|
//填充表头
|
|
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
|
|
foreach (NetLibrary.ReportPrint.TableColumn item in Columns)
|
|
{
|
|
int index = Columns.IndexOf(item);
|
|
HSSFCell dataCell = (HSSFCell)dataRow.CreateCell(index);
|
|
dataCell.SetCellValue(item.Caption);
|
|
//边框
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
//边框颜色
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
}
|
|
|
|
|
|
//填充内容
|
|
for (int i = 0; i < lst.Count; i++)
|
|
{
|
|
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
|
|
HSSFCell dataCell = null;
|
|
foreach (NetLibrary.ReportPrint.TableColumn item in Columns)
|
|
{
|
|
int j = Columns.IndexOf(item);
|
|
dataCell = (HSSFCell)dataRow.CreateCell(j);
|
|
if (item.Format != "")
|
|
{
|
|
dataCell.SetCellValue(
|
|
DataConvert.Format(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)), item.Format));
|
|
}
|
|
else if (item.DataType == DbType.Int32 && Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)) != "")
|
|
{
|
|
dataCell.SetCellValue(Convert.ToInt32(t.GetProperty(item.Name).GetValue(lst[i], null)));
|
|
}
|
|
else if (item.DataType == DbType.Decimal && Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)) != "")
|
|
{
|
|
dataCell.SetCellValue(Convert.ToDouble(t.GetProperty(item.Name).GetValue(lst[i], null)));
|
|
}
|
|
else
|
|
{
|
|
dataCell.SetCellValue(Convert.ToString(t.GetProperty(item.Name).GetValue(lst[i], null)));
|
|
}
|
|
dataCell.CellStyle.Alignment = HorizontalAlignment.CENTER;
|
|
//边框
|
|
dataCell.CellStyle.BorderBottom = CellBorderType.THIN;
|
|
dataCell.CellStyle.BorderLeft = CellBorderType.THIN;
|
|
dataCell.CellStyle.BorderRight = CellBorderType.THIN;
|
|
dataCell.CellStyle.BorderTop = CellBorderType.THIN;
|
|
//边框颜色
|
|
dataCell.CellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
dataCell.CellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
dataCell.CellStyle.LeftBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
dataCell.CellStyle.RightBorderColor = HSSFColor.OLIVE_GREEN.BLUE.BLACK.index;
|
|
}
|
|
}
|
|
foreach (NetLibrary.ReportPrint.TableColumn item in Columns)
|
|
{
|
|
int index = Columns.IndexOf(item);
|
|
if (item.Width > 0) { sheet.SetColumnWidth(index, (int)item.Width); }
|
|
else { sheet.AutoSizeColumn(index); }
|
|
}
|
|
string filename = Guid.NewGuid().ToString() + ".xls";
|
|
string DirectoryName = AppDomain.CurrentDomain.BaseDirectory + "ServerCookies";
|
|
if (System.IO.Directory.Exists(DirectoryName) == false) System.IO.Directory.CreateDirectory(DirectoryName);
|
|
string ServerFileName = DirectoryName + "/" + filename;
|
|
//保存
|
|
using (FileStream fs = new FileStream(ServerFileName, FileMode.Create, FileAccess.Write))
|
|
{
|
|
workbook.Write(fs);
|
|
}
|
|
workbook.Dispose();
|
|
return "ServerCookies/" + filename;
|
|
}
|
|
}
|
|
} |