void GenearateExcel()
{
// Create a new workbook and a sheet named "Test"
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("Test");
var sheet1 = workbook.CreateSheet("ITSRFP_SendEmailToCarriers_TaskStatus");
// Add header labels
//var rowIndex = 0;
//var row = sheet.CreateRow(rowIndex);
//row.CreateCell(0).SetCellValue("Username");
//row.CreateCell(1).SetCellValue("Email");
//row.CreateCell(3).SetCellValue("Link");
//rowIndex++;
//row = sheet.CreateRow(rowIndex);
//row.CreateCell(0).SetCellValue("Ram");
//row.CreateCell(1).SetCellValue("*****@*****.**");
//rowIndex++;
//insert a hyperlink
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Test2'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
string filename = @"C:\Users\RNUNNA\Documents\Visual Studio 2013\Projects\DataDictionary\DataDictionary\Download\Test.xls";
// Save the Excel spreadsheet to a file on the web server's file system
using (var fileData = new FileStream(filename, FileMode.Create))
{
workbook.Write(fileData);
}
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
string saveAsFileName = string.Format("MembershipExport-{0:d}.xls", DateTime.Now).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
static void Main(string[] args)
{
InitializeWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
CellStyle hlink_style = hssfworkbook.CreateCellStyle();
Font hlink_font = hssfworkbook.CreateFont();
hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
hlink_font.Color = HSSFColor.BLUE.index;
hlink_style.SetFont(hlink_font);
Cell cell;
Sheet sheet = hssfworkbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.URL);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new HSSFHyperlink(HyperlinkType.FILE);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new HSSFHyperlink(HyperlinkType.EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a place in this workbook
//Create a target sheet and cell
Sheet sheet2 = hssfworkbook.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address = ("'Target Sheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
WriteToFile();
}
public void AddHyperlinkInXlsTest()
{
InitializeWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
ICellStyle hlink_style = hssfworkbook.CreateCellStyle();
IFont hlink_font = hssfworkbook.CreateFont();
hlink_font.Underline = FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = hssfworkbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new HSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new HSSFHyperlink(HyperlinkType.Email);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a place in this workbook
//Create a target sheet and cell
ISheet sheet2 = hssfworkbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
WriteToFile();
}
private void SetCellHyperlink(HSSFWorkbook workbook, HSSFCell cell, String linkAddress)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)cell.CellStyle;
HSSFFont font = (HSSFFont)cellStyle.GetFont(workbook);
font.Underline = FontUnderlineType.Single;
cellStyle.SetFont(font);
HSSFHyperlink hyperlink = new HSSFHyperlink(HyperlinkType.Url);
hyperlink.Address = linkAddress;
cell.Hyperlink = hyperlink;
cell.CellStyle = cellStyle;
}
public static IHyperlink CreateHyperlink(this ISheet sheet, Int32 x, Int32 y)
{
IHyperlink link = null;
if (sheet is XSSFSheet)
{
link = new XSSFHyperlink(HyperlinkType.Document);
}
else
{
link = new HSSFHyperlink(HyperlinkType.Document);
}
String address = $"!R{x.ToString()}C{y.ToString()}";
link.Address = $"'{ sheet.SheetName}'" + address;
return(link);
}
public void TestCopyHSSFHyperlink()
{
HSSFHyperlink hlink = new HSSFHyperlink(HyperlinkType.Url);
hlink.Address = ("http://poi.apache.org/");
hlink.FirstColumn = (3);
hlink.FirstRow = (2);
hlink.LastColumn = (5);
hlink.LastRow = (6);
hlink.Label = ("label");
XSSFHyperlink xlink = new XSSFHyperlink(hlink);
Assert.AreEqual("http://poi.apache.org/", xlink.Address);
Assert.AreEqual(new CellReference(2, 3), new CellReference(xlink.CellRef));
// Are HSSFHyperlink.label and XSSFHyperlink.tooltip the same? If so, perhaps one of these needs renamed for a consistent Hyperlink interface
// Assert.AreEqual("label", xlink.Tooltip);
}
public void TestShiftRows()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("46445.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
//verify existing hyperlink in A3
ICell cell1 = sheet.GetRow(2).GetCell(0);
IHyperlink link1 = cell1.Hyperlink;
Assert.IsNotNull(link1);
Assert.AreEqual(2, link1.FirstRow);
Assert.AreEqual(2, link1.LastRow);
//assign a hyperlink to A4
HSSFHyperlink link2 = new HSSFHyperlink(HyperlinkType.Document);
link2.Address = ("Sheet2!A2");
ICell cell2 = sheet.GetRow(3).GetCell(0);
cell2.Hyperlink = (link2);
Assert.AreEqual(3, link2.FirstRow);
Assert.AreEqual(3, link2.LastRow);
//move the 3rd row two rows down
sheet.ShiftRows(sheet.FirstRowNum, sheet.LastRowNum, 2);
//cells A3 and A4 don't contain hyperlinks anymore
Assert.IsNull(sheet.GetRow(2).GetCell(0).Hyperlink);
Assert.IsNull(sheet.GetRow(3).GetCell(0).Hyperlink);
//the first hypelink now belongs to A5
IHyperlink link1_shifted = sheet.GetRow(2 + 2).GetCell(0).Hyperlink;
Assert.IsNotNull(link1_shifted);
Assert.AreEqual(4, link1_shifted.FirstRow);
Assert.AreEqual(4, link1_shifted.LastRow);
//the second hypelink now belongs to A6
IHyperlink link2_shifted = sheet.GetRow(3 + 2).GetCell(0).Hyperlink;
Assert.IsNotNull(link2_shifted);
Assert.AreEqual(5, link2_shifted.FirstRow);
Assert.AreEqual(5, link2_shifted.LastRow);
}
public void TestCreateDocumentLink()
{
HSSFWorkbook wb = new HSSFWorkbook();
//link to a place in this workbook
IHyperlink link;
ICell cell;
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//create a target sheet and cell
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
//cell A1 has a link to 'Target Sheet-1'!A1
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.TextMark = ("'Target Sheet'!A1");
cell.Hyperlink = (link);
//cell B1 has a link to cell A1 on the same sheet
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Hyperlinks'!A1");
cell.Hyperlink = (link);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
Assert.AreEqual("'Target Sheet'!A1", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Hyperlinks'!A1", link.TextMark);
Assert.AreEqual("'Hyperlinks'!A1", link.Address);
}
/// <summary>
/// 创建与指定 工作表关联的超链接
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="address">链接地址</param>
/// <returns></returns>
public static IHyperlink CreateHyperlink(this ISheet sheet, String address = null)
{
IHyperlink link = null;
if (sheet is XSSFSheet)
{
link = new XSSFHyperlink(HyperlinkType.Document);
}
else
{
link = new HSSFHyperlink(HyperlinkType.Document);
}
//'Murray工作表_2'!A1
if (address == null)
{
address = "!A1";
}
link.Address = $"'{ sheet.SheetName}'" + address;
return(link);
}
private void SaveExcel(string filePath)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet0");
int rowCount = dt.Rows.Count;
for (int i = 0; i < rowCount; ++i)
{
IRow row = sheet.CreateRow(i);
ICell cellName = row.CreateCell(0);
cellName.SetCellValue(dt.Rows[i][0].ToString());
ICell cellContent = row.CreateCell(1);
cellContent.SetCellValue(dt.Rows[i][1].ToString());
ICell cellHyperlink = row.CreateCell(2);
cellHyperlink.SetCellValue("<链接>");
//超链接
HSSFHyperlink Hyperlink = new HSSFHyperlink(HyperlinkType.File);
Hyperlink.Address = "./" + dt.Rows[i][0].ToString() + ".txt";
cellHyperlink.Hyperlink = Hyperlink;
//颜色+下划线
IFont font = workbook.CreateFont(); //创建字体样式
font.Color = HSSFColor.Blue.Index; //设置字体颜色
font.Underline = FontUnderlineType.Single; //下划线
ICellStyle style = workbook.CreateCellStyle(); //创建单元格样式
style.SetFont(font); //设置单元格样式中的字体样式
cellHyperlink.CellStyle = style; //为单元格设置显示样式
}
using (FileStream fs = File.Create(filePath))
{
workbook.Write(fs);
workbook.Close();
fs.Close();
}
MessageBox.Show("转换完成");
}
/// <summary>
/// 设置某单元格为超链接格式
/// </summary>
/// <param name="iRow"></param>
/// <param name="iColumn"></param>
/// <param name="backColor"></param>
public void SetCellsHyperlink(int iRow, int iColumn, HyperlinkType hyperlinkType)
{
IRow row = _activeSheet.GetRow(iRow);
if (null == row)
{
row = _activeSheet.CreateRow(iRow);
}
ICell cell = row.GetCell(iColumn);
if (null == cell)
{
cell = row.CreateCell(iColumn);
}
//设置超链接
HSSFHyperlink link = new HSSFHyperlink(hyperlinkType);
link.Address = ReadstringCellValue(iRow, iColumn);
cell.Hyperlink = link;
}
public void TestRemoveHyperlink()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell1 = row.CreateCell(1) as HSSFCell;
HSSFHyperlink link1 = new HSSFHyperlink(HyperlinkType.Url);
Assert.IsNotNull(link1);
cell1.RemoveHyperlink();
Assert.IsNull(cell1.Hyperlink);
HSSFCell cell2 = row.CreateCell(0) as HSSFCell;
HSSFHyperlink link2 = new HSSFHyperlink(HyperlinkType.Url);
Assert.IsNotNull(link2);
cell2.Hyperlink = (/*setter*/ null);
Assert.IsNull(cell2.Hyperlink);
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
/// <summary>
/// 编辑配置页
/// </summary>
/// <param name="Config">sheet</param>
/// <param name="caseName">案例名</param>
/// <param name="sheetName">案例的ID sheet名</param>
public static void creatConfigRow(ISheet Config, string caseName, string sheetName, int pCount)
{
IRow row = Config.CreateRow(Config.LastRowNum + 1);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue(caseName);
//创建一个超链接对象
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
// strTableName 这个参数为 sheet名字 A1 为单元格 其他是固定格式
link.Address = "'" + sheetName + "'!A1";
//设置 cellTableName 单元格 的连接对象
cell0.Hyperlink = link;
ICellStyle hlink_style = Config.Workbook.CreateCellStyle();
IFont hlink_font = Config.Workbook.CreateFont();
hlink_font.Underline = FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
cell0.CellStyle = hlink_style;
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue(sheetName);
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue(pCount);
}
public void TestCreate()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell;
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
IHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = link;
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new HSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = link;
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new HSSFHyperlink(HyperlinkType.Email);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = link;
//link to a place in this workbook
//Create a target sheet and cell
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.Document);
link.TextMark = ("'Target Sheet'!A1");
cell.Hyperlink = link;
//serialize and read again
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
wb = new HSSFWorkbook(new MemoryStream(out1.ToArray()));
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("link1.xls", link.Address);
cell = sheet.GetRow(2).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("mailto:[email protected]?subject=Hyperlinks", link.Address);
cell = sheet.GetRow(3).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
}
protected void btnTblExport_Click(object sender, EventArgs e)
{
/* Table Info*/
DataSet ds = (DataSet)ViewState["ds"];
/*Table info Detail's*/
DataSet dsTableInfo = new DataSet();
query = query = @"
/* Columns */
Select * From INFORMATION_SCHEMA.COLUMNS
/* getting PK,FK & depndent tables Info*/
select * from (
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME )A
/* Getting Dependent SP's ,Views & Triggers*/
;WITH stored_procedures AS
(
SELECT
o.name AS proc_name, oo.name AS table_name,o.xtype,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name)AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
)
SELECT proc_name, table_name,xtype FROM stored_procedures
WHERE row = 1 ORDER BY proc_name,table_name /* Clustered & non clustered Keys*/ SELECT so.name AS TableName, si.name AS IndexName, si.type_desc AS IndexType FROM sys.indexes si JOIN sys.objects so ON si.[object_id] = so.[object_id] WHERE so.type = 'U' AND si.name IS NOT NULL ORDER BY so.name, si.type ";
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(dsTableInfo);
}
}
}
/* Table Info */
// Create a new workbook and a sheet named "Test"
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("Index");
//insert a hyperlink
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
/*Add header labels*/
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("SI No");
row.CreateCell(1).SetCellValue("Table Name");
rowIndex++;
// Add data rows
//select All rows
IEnumerable <DataRow> allRows = from tblrows in ds.Tables[0].AsEnumerable() select tblrows;
IEnumerable <DataRow> tablerows = allRows.Where(p => p.Field <string>("TABLE_TYPE") == "BASE TABLE");
//Rows of Table structure
IEnumerable <DataRow> allTableStrRows = from tblrows in dsTableInfo.Tables[0].AsEnumerable() select tblrows;
IEnumerable <DataRow> allTableDependentRows = from tblrows in dsTableInfo.Tables[1].AsEnumerable() select tblrows;
IEnumerable <DataRow> allTableDependentothersRows = from tblrows in dsTableInfo.Tables[2].AsEnumerable() select tblrows;
foreach (DataRow datarow in tablerows)
{
//creating table sheets
string hypLinkText = string.Empty;
string sheetName = "";
sheetName = datarow["TABLE_NAME"].ToString().Trim();
if (sheetName.Length > 31)
{
sheetName = sheetName.Remove(26).ToString();
sheetName = sheetName += "....";
}
hypLinkText = sheetName;
sheetName = workbook.CreateSheet(sheetName).ToString();
/* create a new Data Row */
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(rowIndex.ToString());
/* Create Table HyperLink */
cell = sheet.CreateRow(rowIndex).CreateCell(1);
cell.SetCellValue(hypLinkText);
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = ("'" + hypLinkText + "'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
rowIndex++;
}
/*Create Each Table Info */
var tblrowIndex = 0;
foreach (DataRow tbldatarow in tablerows)
{
tblrowIndex = 0;
string tblsheetName = tbldatarow["TABLE_NAME"].ToString().Trim();
if (tblsheetName.Length > 31)
{
tblsheetName = tblsheetName.Remove(26).ToString();
tblsheetName = tblsheetName += "....";
}
/*Add header labels*/
ISheet tblsheet = workbook.GetSheet(tblsheetName);
var tblInforow = tblsheet.CreateRow(tblrowIndex);
tblInforow.CreateCell(0).SetCellValue("SI No");
tblInforow.CreateCell(1).SetCellValue("COLUMN NAME");
tblInforow.CreateCell(2).SetCellValue("DATA TYPE");
tblInforow.CreateCell(3).SetCellValue("IS NULLABLE");
tblInforow.CreateCell(4).SetCellValue("MAXIMUM LENGTH");
/*Add header labels for dependent tables*/
tblInforow.CreateCell(5).SetCellValue("");
tblInforow.CreateCell(6).SetCellValue("SI No");
tblInforow.CreateCell(7).SetCellValue("Column Name");
tblInforow.CreateCell(8).SetCellValue("Table Name");
tblrowIndex++;
IEnumerable <DataRow> TableStrRows = allTableStrRows.Where(p => p.Field <string>("TABLE_NAME") == tbldatarow["TABLE_NAME"].ToString());
/* create a new Data Row */
tblInforow = tblsheet.CreateRow(tblrowIndex);
foreach (DataRow tblInfoRow in TableStrRows)
{
tblInforow.CreateCell(0).SetCellValue((tblrowIndex.ToString()).ToString());
tblInforow.CreateCell(1).SetCellValue(tblInfoRow["COLUMN_NAME"].ToString());
tblInforow.CreateCell(2).SetCellValue(tblInfoRow["DATA_TYPE"].ToString());
tblInforow.CreateCell(3).SetCellValue(tblInfoRow["IS_NULLABLE"].ToString());
tblInforow.CreateCell(4).SetCellValue(tblInfoRow["CHARACTER_MAXIMUM_LENGTH"].ToString());
tblrowIndex++;
}
tblInforow.CreateCell(5).SetCellValue("");
/*Create table Dependent Tables Info. */
IEnumerable <DataRow> TableDependentRows = allTableDependentRows.Where(p => p.Field <string>("FK_Table") == tbldatarow["TABLE_NAME"].ToString());
/* create a new Data Row */
foreach (DataRow tblInfoRow in TableDependentRows)
{
tblInforow.CreateCell(6).SetCellValue((tblrowIndex.ToString()).ToString());
tblInforow.CreateCell(7).SetCellValue(tblInfoRow["PK_Column"].ToString());
tblInforow.CreateCell(8).SetCellValue(tblInfoRow["PK_Table"].ToString());
tblrowIndex++;
}
}
string filename = @"C:\New folder\Test_dat.xls";
// Save the Excel spreadsheet to a file on the web server's file system
using (var fileData = new FileStream(filename, FileMode.Create))
{
workbook.Write(fileData);
}
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
string saveAsFileName = string.Format("DatabaseDictonary-{0:d}.xls", DateTime.Now).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
private static Boolean WriteToCell(ref ISheet sheet, Int32 row, Int32 column, Object data, String fieldFormat = "", Boolean isHyperlink = false)
{
Boolean result = false;
if (sheet != null && data != null && row > -1 && column > -1)
{
Type dataType = data.GetType();
// Write the column header
IRow wRow = sheet.GetRow(row);
ICell wCell = null;
if (wRow == null)
{
wRow = sheet.CreateRow(row);
}
wCell = wRow.GetCell(column);
if (wCell == null)
{
wCell = wRow.CreateCell(column);
}
if (dataType.Equals(typeof(Double)) ||
dataType.Equals(typeof(Int16)) ||
dataType.Equals(typeof(Int32)) ||
dataType.Equals(typeof(Int64)) ||
dataType.Equals(typeof(System.Decimal)))
{
wCell.SetCellType(CellType.Numeric);
if (data != null)
{
wCell.SetCellValue(Convert.ToDouble(data));
}
}
else if (dataType.Equals(typeof(Boolean)))
{
wCell.SetCellType(CellType.Boolean);
wCell.SetCellValue((Boolean)data);
}
else if (dataType.Equals(typeof(DateTime)))
{
wCell.SetCellType(CellType.String);
if (!String.IsNullOrWhiteSpace(fieldFormat))
{
wCell.SetCellValue(((DateTime)data).ToString(fieldFormat));
}
else
{
wCell.SetCellValue(((DateTime)data).ToString("dd MMM yyyy hh:mm:ss"));
}
}
else if (dataType.Equals(typeof(DateTimeOffset)))
{
wCell.SetCellType(CellType.String);
if (!String.IsNullOrWhiteSpace(fieldFormat))
{
wCell.SetCellValue(((DateTimeOffset)data).ToString(fieldFormat));
}
else
{
wCell.SetCellValue(((DateTimeOffset)data).ToString("dd MMM yyyy hh:mm:ss"));
}
}
else if (dataType.Equals(typeof(String)))
{
if (isHyperlink == false)
{
wCell.SetCellType(CellType.String);
wCell.SetCellValue((String)data);
}
else
{
wCell.SetCellType(CellType.String);
wCell.SetCellValue((String)data);
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = ((String)data);
wCell.Hyperlink = link;
}
}
result = true;
}
return(result);
}
/// <summary>
/// 根据属性类型创建对应单元格
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>字符串值</returns>
public static void CreateCell(this IRow row, int columnIndex, PropertyInfo property, object instance)
{
var val = property.GetValue(instance);
var cell = row.CreateCell(columnIndex);
if (property.PropertyType.Equals(typeof(string)))
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
else if (property.PropertyType.Equals(typeof(int)) ||
property.PropertyType.Equals(typeof(double)) ||
property.PropertyType.Equals(typeof(decimal)) ||
property.PropertyType.Equals(typeof(float)))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(val));
var attr = property.GetCustomAttributes(typeof(DisplayFormatAttribute), true).FirstOrDefault();
if (attr != null)
{
var format = ((DisplayFormatAttribute)attr).DataFormatString ?? "";
if (!format.IsNullOrEmpty())
{
IDataFormat dataFormat = cell.Sheet.Workbook.CreateDataFormat();
ICellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.DataFormat = dataFormat.GetFormat(format);
cell.CellStyle = style;
}
}
}
else if (property.PropertyType.Equals(typeof(DateTime)) || property.PropertyType.Equals(typeof(DateTime?)))
{
cell.SetCellType(CellType.Numeric);
var format = "yyyy-MM-dd hh:mm:ss ";
var attr = property.GetCustomAttributes(typeof(DisplayFormatAttribute), true).FirstOrDefault();
if (attr != null)
{
format = ((DisplayFormatAttribute)attr).DataFormatString ?? format;
}
IDataFormat dataFormat = cell.Sheet.Workbook.CreateDataFormat();
ICellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.DataFormat = dataFormat.GetFormat(format);
cell.SetCellValue(val.TryDateTime());
cell.CellStyle = style;
}
else if (property.PropertyType.Equals(typeof(bool)))
{
cell.SetCellType(CellType.Boolean);
}
else if (property.PropertyType.Equals(typeof(string)) && instance != null)
{
var value = property.GetValue(instance);
if (value != null &&
value.ToString().ToLower().StartsWith("http") ||
value.ToString().ToLower().StartsWith("https"))
{
cell.SetCellType(CellType.Blank);
IHyperlink link;
if (cell.Sheet.Workbook.GetType() == typeof(XSSFWorkbook))
{
link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = val.ToString();
}
else
{
link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = val.ToString();
}
cell.SetCellValue(val.ToString());
cell.Hyperlink = link;
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
}
private void SetTableNamePageList(IWorkbook workbook, MTableDefine table, string filename)
{
ISheet sheet = workbook.CreateSheet(table.TableName);
// 第0行：返回一览表
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("返回一览表");
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);//建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
link.Address = "#数据表一栏!A1";
cell.Hyperlink = link;
this.SetHyperLinkCellStyle(workbook, cell, false);
HSSFColor gold = new HSSFColor.Gold();
// 第一行、表介绍的的表头
row = sheet.CreateRow(1);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("TableName");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("表明");
cell = row.CreateCell(2);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("表说明");
// 表介绍的内容
row = sheet.CreateRow(2);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableName);
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
cell = row.CreateCell(2);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
// 一、表定义
row = sheet.CreateRow(3);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null, "微软雅黑", 10, false);
cell.SetCellValue("一、表定义");
// 表字段定义表头
row = sheet.CreateRow(4);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("序号");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("字段中文名");
cell = row.CreateCell(2);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("字段英文名");
cell = row.CreateCell(3);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("数据类型");
cell = row.CreateCell(4);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("位数");
cell = row.CreateCell(5);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("非空");
cell = row.CreateCell(6);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("主键");
cell = row.CreateCell(7);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("外部关系");
cell = row.CreateCell(8);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("唯一索引");
cell = row.CreateCell(9);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("索引");
cell = row.CreateCell(10);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("自增");
cell = row.CreateCell(11);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("默认值");
cell = row.CreateCell(12);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("字段格式");
cell = row.CreateCell(13);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("值约束");
cell = row.CreateCell(14);
cell.CellStyle = this.GetCellStyle(workbook, gold);
cell.SetCellValue("项目意义");
int i = 5;
// 字段定义
foreach (var field in table.FieldList)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(field.Index);
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(field.FieldNameCH);
cell = row.CreateCell(2);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(field.FieldName);
cell = row.CreateCell(3);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(field.DataType);
cell = row.CreateCell(4);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(field.Length);
cell = row.CreateCell(5);
cell.CellStyle = this.GetCellStyle(workbook, null, "宋体");
if (!field.IsNullable)
{
cell.SetCellValue("○");
}
else
{
cell.SetCellValue(string.Empty);
}
cell = row.CreateCell(6);
cell.CellStyle = this.GetCellStyle(workbook, null);
if (field.PrimaryKeyIndex > 0)
{
cell.SetCellValue(field.PrimaryKeyIndex);
}
else
{
cell.SetCellValue(string.Empty);
}
// 外部关系先不填写
cell = row.CreateCell(7);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(string.Empty);
cell = row.CreateCell(8);
cell.CellStyle = this.GetCellStyle(workbook, null, "宋体");
if (field.IsUniqueIndex)
{
cell.SetCellValue("○");
}
else
{
cell.SetCellValue(string.Empty);
}
cell = row.CreateCell(9);
cell.CellStyle = this.GetCellStyle(workbook, null);
if (field.IndexNo > 0)
{
cell.SetCellValue(field.IndexNo);
}
else
{
cell.SetCellValue(string.Empty);
}
cell = row.CreateCell(10);
cell.CellStyle = this.GetCellStyle(workbook, null, "宋体");
if (field.IsAutoIncrement)
{
cell.SetCellValue("○");
}
else
{
cell.SetCellValue(string.Empty);
}
cell = row.CreateCell(11);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(field.DefaultValue);
// 字段格式，暂时不写
cell = row.CreateCell(12);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(string.Empty);
// 值约束
cell = row.CreateCell(13);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(field.ValueConstraint);
// 项目意义
cell = row.CreateCell(14);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(string.Empty);
i++;
}
for (int j = 0; j <= 14; j++)
{
sheet.AutoSizeColumn(j);
}
}
private void SetTableIndexPage(IWorkbook workbook, MDataBaseDefine database)
{
ISheet sheet = workbook.CreateSheet("数据表一栏");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null, "微软雅黑", 10, false);
cell.SetCellValue("Table表一栏");
row = sheet.CreateRow(1);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("库名");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("");
row = sheet.CreateRow(2);
cell = row.CreateCell(0);
cell.SetCellValue("库类型");
cell.CellStyle = this.GetCellStyle(workbook, null);
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(database.DataBaseName);
row = sheet.CreateRow(3);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("服务器地址");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(database.ServerAddress);
row = sheet.CreateRow(4);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("建库时间");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("");
row = sheet.CreateRow(5);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("读账号");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(database.ReadAccount);
row = sheet.CreateRow(6);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue("写账号");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(database.WriteAccount);
row = sheet.CreateRow(7);
cell = row.CreateCell(4);
cell.CellStyle = this.GetCellStyle(workbook, null, "宋体", 10, false);
cell.SetCellValue("○修改，●新增，◎使用");
HSSFColor gray = new HSSFColor.Grey40Percent();
row = sheet.CreateRow(8);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("#");
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("一级分类");
cell = row.CreateCell(2);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("二级分类");
cell = row.CreateCell(3);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("TableName");
cell = row.CreateCell(4);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("表名");
cell = row.CreateCell(5);
cell.CellStyle = this.GetCellStyle(workbook, gray);
cell.SetCellValue("备注");
int i = 9;
foreach (var table in database.TableList)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(i - 8);
cell = row.CreateCell(1);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
cell = row.CreateCell(2);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
cell = row.CreateCell(3);
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);//建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
link.Address = "#" + table.TableName + "!A1";
cell.Hyperlink = link;
cell.SetCellValue(table.TableName);
this.SetHyperLinkCellStyle(workbook, cell);
cell = row.CreateCell(4);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
cell = row.CreateCell(5);
cell.CellStyle = this.GetCellStyle(workbook, null);
cell.SetCellValue(table.TableNameCH);
i++;
}
for (int j = 0; j <= 6; j++)
{
sheet.AutoSizeColumn(j);
}
}
/// <summary>
/// 生成EXECL文件，通过读取DataTable和列头映射信息
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="excelInfo">Excel导出信息</param>
/// <returns>文件流</returns>
public static MemoryStream Export(DataTable dt, ExcelInfo excelInfo)
{
if (dt == null || excelInfo == null || excelInfo.ColumnInfoList == null)
{
throw new ArgumentNullException();
}
bool isMoreHeader = excelInfo.GroupHeader != null && excelInfo.GroupHeader.Count > 0;
if (isMoreHeader)
{
return(CreateMoreHeaderXls(dt, excelInfo));
}
int rowHeight = 20;
List <ColumnInfo> ColumnInfoList = excelInfo.ColumnInfoList;
//每个标签页最多行数
int sheetRow = 65536;
HSSFWorkbook workbook = new HSSFWorkbook();
//文本样式
ICellStyle centerStyle = workbook.CreateCellStyle();
centerStyle.VerticalAlignment = VerticalAlignment.Center;
centerStyle.Alignment = HorizontalAlignment.Center;
ICellStyle leftStyle = workbook.CreateCellStyle();
leftStyle.VerticalAlignment = VerticalAlignment.Center;
leftStyle.Alignment = HorizontalAlignment.Left;
ICellStyle rightStyle = workbook.CreateCellStyle();
rightStyle.VerticalAlignment = VerticalAlignment.Center;
rightStyle.Alignment = HorizontalAlignment.Right;
//寻找列头和DataTable之间映射关系
foreach (DataColumn col in dt.Columns)
{
ColumnInfo info = ColumnInfoList.FirstOrDefault(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
if (info != null)
{
switch (info.Align.ToLower())
{
case "left":
info.Style = leftStyle;
break;
case "center":
info.Style = centerStyle;
break;
case "right":
info.Style = rightStyle;
break;
}
info.IsMapDT = true;
}
}
int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / MAX_ROW_INDEX);
int total = dt.Rows.Count;
//超链接字体颜色
IFont blueFont = workbook.CreateFont();
blueFont.Color = HSSFColor.Blue.Index;
//最多生成5个标签页的数据
sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
ICell cell = null;
object cellValue = null;
//标题头索引
int headIndex = string.IsNullOrEmpty(excelInfo.Remark) ? 0 : 1;
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
{
ISheet sheet = workbook.CreateSheet();
sheet.CreateFreezePane(0, headIndex + 1, 0, headIndex + 1);
if (headIndex > 0)
{
//输出备注行
IRow RemarkRow = sheet.CreateRow(0);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, ColumnInfoList.Count - 1));
ICell rcell = RemarkRow.CreateCell(0);
ICellStyle remarkStyle = workbook.CreateCellStyle();
remarkStyle.WrapText = true;
remarkStyle.VerticalAlignment = VerticalAlignment.Top;
remarkStyle.Alignment = HorizontalAlignment.Left;
IFont rfont = workbook.CreateFont();
rfont.FontHeightInPoints = 12;
remarkStyle.SetFont(rfont);
rcell.CellStyle = remarkStyle;
RemarkRow.HeightInPoints = rowHeight * 5;
rcell.SetCellValue(excelInfo.Remark);
}
//输出表头
IRow headerRow = sheet.CreateRow(headIndex);
//设置行高
headerRow.HeightInPoints = rowHeight;
//首行样式
ICellStyle HeaderStyle = workbook.CreateCellStyle();
HeaderStyle.FillPattern = FillPattern.SolidForeground;
HeaderStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
IFont font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
HeaderStyle.SetFont(font);
HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
HeaderStyle.Alignment = HorizontalAlignment.Center;
//输出表头信息 并设置表头样式
int i = 0;
foreach (var data in ColumnInfoList)
{
cell = headerRow.CreateCell(i);
cell.SetCellValue(data.Header.Trim());
cell.CellStyle = HeaderStyle;
i++;
}
//开始循环所有行
int iRow = 1 + headIndex;
int startRow = sheetIndex * (sheetRow - 1);
int endRow = (sheetIndex + 1) * (sheetRow - 1);
endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;
for (int rowIndex = startRow; rowIndex < endRow; rowIndex++)
{
IRow row = sheet.CreateRow(iRow);
row.HeightInPoints = rowHeight;
i = 0;
foreach (var item in ColumnInfoList)
{
cell = row.CreateCell(i);
if (item.IsMapDT)
{
cellValue = dt.Rows[rowIndex][item.Field];
Type columnType = dt.Columns[item.Field].DataType;
cell.SetCellValue(cellValue, columnType);
cell.CellStyle = item.Style;
if (item.IsLink)
{
cellValue = dt.Rows[rowIndex][item.Field + "Link"];
if (cellValue != DBNull.Value && cellValue != null)
{
//建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
//给HSSFHyperlink的地址赋值 ，默认为该列加上Link
link.Address = cellValue.ToString();
cell.Hyperlink = link;
cell.CellStyle.SetFont(blueFont);
}
}
}
i++;
}
iRow++;
}
//自适应列宽度
for (int j = 0; j < ColumnInfoList.Count; j++)
{
sheet.AutoSizeColumn(j);
int width = sheet.GetColumnWidth(j) + 2560;
sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms);
}
public ResultadoOperacion HyperLinks()
{
var filePatterns = File.ReadAllLines("FormatosArchivosMusica.txt");
var folderPatterns = File.ReadAllLines("FormatosCarpetas.txt");
var searchFile = new FileSearch()
{
FilePatterns = filePatterns,
FolderPatterns = folderPatterns,
PathMusicas = PathColeccion
};
var ro = GetExcel();
if (!ro.Ok)
{
return(ro);
}
try
{
using (var fs = File.OpenRead(Excel))
{
hssfWorkbook = new HSSFWorkbook(fs);
}
}
catch (Exception e)
{
return(ResultadoOperacion.Fail($"No se pudo abrir el excel {Excel}. Error: {e.Message}."));
}
var sheet = hssfWorkbook.GetSheet(Hoja);
if (sheet == null)
{
return(ResultadoOperacion.Fail($"La hoja {Hoja} no existe en el archivo {Excel}"));
}
var row = 1;
var linkStyle = hssfWorkbook.CreateCellStyle();
linkStyle.Alignment = HorizontalAlignment.Center;
var fontWindings3 = hssfWorkbook.CreateFont();
fontWindings3.FontName = "Wingdings 3";
fontWindings3.FontHeightInPoints = 16;
linkStyle.SetFont(fontWindings3);
var filas = 0; var filasError = 0;
while (true)
{
var xlRow = sheet.GetRow(row);
if (xlRow == null)
{
break;
}
filas++;
var cell = xlRow.GetCell(ColumnCdPista);
row++;
var cdPista = cell?.StringCellValue;
if (cdPista?.Length != 5)
{
Console.WriteLine("FilaExcel: {1}. {0}.", $"cdPista Incorrecto ({cdPista})", row);
continue;
}
int i;
if (!int.TryParse(cdPista.Substring(0, 2), out i))
{
Console.WriteLine("FilaExcel: {1}. {0}.", $"cdPista Incorrecto ({cdPista})", row);
continue;
}
if (!int.TryParse(cdPista.Substring(3, 2), out i))
{
Console.WriteLine("FilaExcel: {1}. {0}.", $"cdPista Incorrecto ({cdPista})", row);
continue;
}
if (row >= 10000)
{
break;
}
var itemdata = new ItemData()
{
CdPista = cdPista, Coleccion = ""
};
if (ColumnInterprete > -1)
{
cell = xlRow.GetCell(ColumnInterprete);
itemdata.Interprete = cell?.StringCellValue;
}
if (ColumnTitulo > -1)
{
cell = xlRow.GetCell(ColumnTitulo);
itemdata.Titulo = cell?.StringCellValue;
}
var resultadoOperacion = searchFile.GetFile(itemdata);
if (!resultadoOperacion.Ok)
{
filasError++;
Console.WriteLine("FilaExcel: {1}. {0}.", resultadoOperacion.Mensaje, row);
continue;
}
cell = xlRow.GetCell(ColumnLink) ?? xlRow.CreateCell(ColumnLink, CellType.String);
var cellHyperlink = new HSSFHyperlink(HyperlinkType.File)
{
Address = resultadoOperacion.Mensaje.Substring(1)
};
cell.Hyperlink = cellHyperlink;
cell.SetCellValue("u"); //en font Windings 3 es play
cell.CellStyle = linkStyle;
if (ColumnCarpeta >= 0)
{
cell = xlRow.GetCell(ColumnCarpeta) ?? xlRow.CreateCell(ColumnCarpeta, CellType.String);
cell.SetCellValue(itemdata.Carpeta);
}
if (ColumnMusica >= 0)
{
cell = xlRow.GetCell(ColumnMusica) ?? xlRow.CreateCell(ColumnMusica, CellType.String);
cell.SetCellValue(itemdata.Archivo);
}
}
using (var fs = File.OpenWrite(Excel))
{
hssfWorkbook.Write(fs);
}
return(new ResultadoOperacion {
Ok = true, Mensaje = $"Se procesaron {filas} del excel, de las cuales {filasError} filas no se encontro el archivo de música."
});
}
/// <summary>
/// 创建一个Sheet页
/// </summary>
/// <param name="Sheet">Sheet</param>
public void CreateSheet(Sheet sheetInfo)
{
if (string.IsNullOrWhiteSpace(sheetInfo.Name))
{
sheetInfo.Name = "Sheet" + workbook.NumberOfSheets + 1;
}
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetInfo.Name);
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 20;
var columIndex = 0;
foreach (var column in sheetInfo.Columns)
{
headerRow.CreateCell(columIndex).SetCellValue(column.Name);
headerRow.GetCell(columIndex).CellStyle = HeadStyle;
//设置列宽
sheet.SetColumnWidth(columIndex, column.Width * 256);
sheet.SetColumnHidden(columIndex, column.Hidden);
columIndex++;
}
#endregion
#region 填充内容
rowIndex = 1;
foreach (DataRow row in sheetInfo.DataSource.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
var columnIndex = 0;
foreach (var column in sheetInfo.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);
if (!sheetInfo.DataSource.Columns.Contains(column.Code))
{
newCell.SetCellValue("");
}
else
{
string drValue = row[column.Code].ToString();
switch (column.DataType)
{
case ColumnDataType.String: //字符串类型
newCell.SetCellValue(drValue);
break;
case ColumnDataType.Date: //日期类型
System.DateTime dateV = DateTime.MinValue;
System.DateTime.TryParse(drValue, out dateV);
if (dateV != DateTime.MinValue)
{
newCell.SetCellValue(dateV);
}
newCell.CellStyle = DateStyle; //格式化显示
break;
case ColumnDataType.DateTime: //日期类型
System.DateTime datetimeV = DateTime.MinValue;
System.DateTime.TryParse(drValue, out datetimeV);
if (datetimeV != DateTime.MinValue)
{
newCell.SetCellValue(datetimeV);
}
newCell.CellStyle = DateTimeStyle; //格式化显示
break;
case ColumnDataType.Bool: //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case ColumnDataType.Int: //整型
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case ColumnDataType.Float: //浮点型
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case ColumnDataType.Url: // 链接样式
newCell.SetCellValue(drValue);
var link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = drValue;
newCell.Hyperlink = link;
newCell.CellStyle = DateStyle; //格式化显示
break;
case ColumnDataType.Other:
default:
newCell.SetCellValue(drValue);
break;
}
}
columnIndex++;
}
rowIndex++;
}
#endregion
}
/// <summary>
/// 生成EXECL文件，通过读取DataTable和列头映射信息
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="excelInfo">Excel导出信息</param>
/// <returns>文件流</returns>
public static MemoryStream CreateMoreHeaderXls(DataTable dt, ExcelInfo excelInfo)
{
int rowHeight = 20;
List <ColumnInfo> ColumnInfoList = excelInfo.ColumnInfoList;
HSSFWorkbook workbook = new HSSFWorkbook();
//文本样式
ICellStyle centerStyle = workbook.CreateCellStyle();
centerStyle.VerticalAlignment = VerticalAlignment.Center;
centerStyle.Alignment = HorizontalAlignment.Center;
ICellStyle leftStyle = workbook.CreateCellStyle();
leftStyle.VerticalAlignment = VerticalAlignment.Center;
leftStyle.Alignment = HorizontalAlignment.Left;
ICellStyle rightStyle = workbook.CreateCellStyle();
rightStyle.VerticalAlignment = VerticalAlignment.Center;
rightStyle.Alignment = HorizontalAlignment.Right;
//首行样式
ICellStyle HeaderStyle = workbook.CreateCellStyle();
HeaderStyle.FillPattern = FillPattern.SolidForeground;
HeaderStyle.FillForegroundColor = HSSFColor.LightCornflowerBlue.Index;
HeaderStyle.BorderTop = BorderStyle.Thin;
HeaderStyle.BorderLeft = BorderStyle.Thin;
HeaderStyle.BorderRight = BorderStyle.Thin;
HeaderStyle.BorderBottom = BorderStyle.Thin;
HeaderStyle.TopBorderColor = HSSFColor.Black.Index;
HeaderStyle.LeftBorderColor = HSSFColor.Black.Index;
HeaderStyle.RightBorderColor = HSSFColor.Black.Index;
HeaderStyle.BottomBorderColor = HSSFColor.Black.Index;
IFont font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
HeaderStyle.SetFont(font);
HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
HeaderStyle.Alignment = HorizontalAlignment.Center;
Dictionary <string, int> dictGroupMap = new Dictionary <string, int>(StringComparer.OrdinalIgnoreCase);
Dictionary <ColumnInfo, bool> dictColumn = new Dictionary <ColumnInfo, bool>();
//寻找列头和DataTable之间映射关系
foreach (DataColumn col in dt.Columns)
{
ColumnInfo info = ColumnInfoList.FirstOrDefault <ColumnInfo>(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
if (info != null)
{
dictColumn[info] = col.DataType == typeof(int) || col.DataType == typeof(float) || col.DataType == typeof(double) ||
col.DataType == typeof(long);
switch (info.Align.ToLower())
{
case "left":
info.Style = leftStyle;
break;
case "center":
info.Style = centerStyle;
break;
case "right":
info.Style = rightStyle;
break;
}
info.IsMapDT = true;
}
}
int index = 0;
foreach (var item in ColumnInfoList)
{
if (excelInfo.GroupHeader.FirstOrDefault(e => e.StartColumnName.Equals(item.Field, StringComparison.OrdinalIgnoreCase)) != null)
{
dictGroupMap[item.Field] = index;
}
index++;
}
List <int> listColumnIndex = new List <int>(ColumnInfoList.Count);
for (int i = 0; i < ColumnInfoList.Count; i++)
{
listColumnIndex.Add(i);
}
foreach (var item in excelInfo.GroupHeader)
{
int startCol = dictGroupMap[item.StartColumnName];
int lastCol = startCol + item.NumberOfColumns - 1;
for (int j = startCol; j <= lastCol; j++)
{
listColumnIndex.Remove(j);
}
}
int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / MAX_ROW_INDEX);
int total = dt.Rows.Count;
//超链接字体颜色
IFont blueFont = workbook.CreateFont();
blueFont.Color = HSSFColor.Blue.Index;
//最多生成5个标签页的数据
sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
ICell cell = null;
object cellValue = null;
//标题头索引
int headIndex = string.IsNullOrEmpty(excelInfo.Remark) ? 0 : 1;
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
{
ISheet sheet = workbook.CreateSheet();
if (headIndex > 0)
{
//输出备注行
IRow RemarkRow = sheet.CreateRow(0);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, ColumnInfoList.Count - 1));
ICell rcell = RemarkRow.CreateCell(0);
ICellStyle remarkStyle = workbook.CreateCellStyle();
remarkStyle.WrapText = true;
remarkStyle.VerticalAlignment = VerticalAlignment.Top;
remarkStyle.Alignment = HorizontalAlignment.Left;
IFont rfont = workbook.CreateFont();
rfont.FontHeightInPoints = 12;
remarkStyle.SetFont(rfont);
rcell.CellStyle = remarkStyle;
RemarkRow.HeightInPoints = rowHeight * 5;
rcell.SetCellValue(excelInfo.Remark);
}
//输出表头
IRow firstHeaderRow = sheet.CreateRow(headIndex);
IRow secondHeaderRow = sheet.CreateRow(headIndex + 1);
//设置行高
firstHeaderRow.HeightInPoints = rowHeight;
secondHeaderRow.HeightInPoints = rowHeight;
//输出表头信息 并设置表头样式
int i = 0, groupIndex = 0;
MoreHeader groupheader = null;
foreach (var data in ColumnInfoList)
{
cell = secondHeaderRow.CreateCell(i);
cell.SetCellValue(data.Header.Trim());
cell.CellStyle = HeaderStyle;
cell = firstHeaderRow.CreateCell(i);
cell.SetCellValue(data.Header.Trim());
cell.CellStyle = HeaderStyle;
groupheader = excelInfo.GroupHeader[groupIndex];
if (groupheader.StartColumnName.Equals(data.Field, StringComparison.CurrentCultureIgnoreCase))
{
cell.SetCellValue(groupheader.TitleText.Trim());
groupIndex++;
if (groupIndex >= excelInfo.GroupHeader.Count)
{
groupIndex--;
}
}
i++;
}
foreach (var item in listColumnIndex)
{
sheet.AddMergedRegion(new CellRangeAddress(headIndex, headIndex + 1, item, item));
}
int startCol, lastCol;
foreach (var item in excelInfo.GroupHeader)
{
startCol = dictGroupMap[item.StartColumnName];
lastCol = startCol + item.NumberOfColumns - 1;
sheet.AddMergedRegion(new CellRangeAddress(headIndex, headIndex, startCol, lastCol));
}
//冻结列 行
sheet.CreateFreezePane(excelInfo.FixColumns, headIndex + 2, excelInfo.FixColumns, headIndex + 2);
//开始循环所有行
int iRow = 2 + headIndex;
int startRow = sheetIndex * (MAX_ROW_INDEX - 1);
int endRow = (sheetIndex + 1) * (MAX_ROW_INDEX - 1);
endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;
for (int rowIndex = startRow; rowIndex < endRow; rowIndex++)
{
IRow row = sheet.CreateRow(iRow);
row.HeightInPoints = rowHeight;
i = 0;
foreach (var item in ColumnInfoList)
{
cell = row.CreateCell(i);
if (item.IsMapDT)
{
cellValue = dt.Rows[rowIndex][item.Field];
Type columnType = dt.Columns[item.Field].DataType;
cell.SetCellValue(cellValue, columnType);
cell.CellStyle = item.Style;
if (item.IsLink)
{
cellValue = dt.Rows[rowIndex][item.Field + "Link"];
if (cellValue != DBNull.Value && cellValue != null)
{
//建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
//给HSSFHyperlink的地址赋值 ，默认为该列加上Link
link.Address = cellValue.ToString();
cell.Hyperlink = link;
cell.CellStyle.SetFont(blueFont);
}
}
}
i++;
}
iRow++;
}
//自适应列宽度
for (int j = 0; j < ColumnInfoList.Count; j++)
{
sheet.AutoSizeColumn(j);
int width = sheet.GetColumnWidth(j) + 2560;
sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms);
}
private async Task ExportToSheet <T>(ISheet sheet, List <T> data, Dictionary <string, string> columns, Func <string, T, Task <NpoiDataModel> > func, HSSFWorkbook workbook)
{
#region 设置下划线字体样式
IFont font = workbook.CreateFont(); //创建字体样式
font.Color = HSSFColor.Blue.Index; //设置字体颜色
font.Underline = FontUnderlineType.Single;
ICellStyle linkStyle = workbook.CreateCellStyle(); //创建单元格样式
linkStyle.SetFont(font); //设置单元格样式中的字体样式
#endregion
IRow headerRow = sheet.CreateRow(0);
var dicProperty = new Dictionary <string, PropertyInfo>();
var props = typeof(T).GetProperties();
//设置标题行
int i = 0;
foreach (var item in columns)
{
headerRow.CreateCell(i).SetCellValue(item.Value);
//匹配属性
var prop = props.FirstOrDefault(p => p.Name.ToLower() == item.Key.ToLower());
if (prop != null)
{
dicProperty.Add(item.Key.ToLower(), prop);
}
i++;
}
//填充数据
for (i = 0; i < data.Count; i++)
{
var row = sheet.CreateRow(i + 1);
var index = 0;
foreach (var item in columns)
{
NpoiDataModel model = await func(item.Key, data[i]);
if (model.CellType == CellType.Image)
{
row.Height = 80 * 20;
await AddCellPicture(sheet, workbook, model.Path, i + 1, index);
//row.CreateCell(index).SetCellValue(model.Path);
}
else if (model.CellType == CellType.Link)
{
var cell = row.CreateCell(index);
cell.SetCellValue(model.Path);
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url); //建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
link.Address = model.Path; //给HSSFHyperlink的地址赋值
cell.Hyperlink = link;
cell.CellStyle = linkStyle; //为单元格设置显示样式
}
else
{
if (!string.IsNullOrEmpty(model.PropertyName))
{
row.CreateCell(index).SetCellValue(dicProperty[model.PropertyName.ToLower()]?.GetValue(data[i])?.ToString() ?? "");
}
else
{
row.CreateCell(index).SetCellValue(model.Value);
}
}
index++;
}
//var row = sheet.CreateRow(i + 1);
//foreach (var item in dicIndexer)
//{
// row.CreateCell(item.Key).SetCellValue(item.Value.GetValue(data[i])?.ToString());
//}
}
}
/// <summary>
/// 将邮件列表内容存入excel
/// </summary>
private void workOutputExcel()
{
Invoke(updateState, (object)SystemState.working);
Invoke(printstr, (object)("开始输出邮件数据到excel：" + excelFileName));
try
{
string excelPath = excelFileName.Substring(0, excelFileName.LastIndexOf("\\")) + "\\";
string attPath = excelPath + @"邮件附件\";
if (!Directory.Exists(attPath))
{
Directory.CreateDirectory(attPath);
}
Invoke(printstr, (object)("附件内容输出到：" + attPath));
IWorkbook wb = new HSSFWorkbook();
ICellStyle style1 = wb.CreateCellStyle();
IFont font1 = wb.CreateFont();
font1.Underline = FontUnderlineType.None;
font1.Color = HSSFColor.Black.Index;
style1.SetFont(font1);
ICellStyle style2 = wb.CreateCellStyle();
IFont font2 = wb.CreateFont();
font2.Underline = FontUnderlineType.Single;
font2.Color = HSSFColor.Blue.Index;
style2.SetFont(font2);
string[] strHead = new string[] { "收信日期", "标题", "发信人", "收信人", "抄送", "正文", "附件" };
int[] columnWidth = new int[] { 21, 40, 15, 15, 15, 30, 30 };
ISheet sheet1 = wb.CreateSheet();
var headRow = sheet1.CreateRow(0);
for (int i = 0; i < columnWidth.Length; i++)
{
var tcell = headRow.CreateCell(i);
tcell.SetCellValue(strHead[i]);
tcell.CellStyle = style1;
sheet1.SetColumnWidth(i, columnWidth[i] * 256);
}
OpenPop.Mime.Message message;
for (int i = 0; i < emails.Count; i++)
{
//向excel文件中添加项
var contentRow = sheet1.CreateRow(i + 1);
ICell tcell;
tcell = contentRow.CreateCell(0);
tcell.SetCellValue(emails[i].Date);
tcell.CellStyle = style1;
tcell = contentRow.CreateCell(1);
tcell.SetCellValue(emails[i].Subject);
tcell.CellStyle = style1;
tcell = contentRow.CreateCell(2);
tcell.SetCellValue(emails[i].From);
tcell.CellStyle = style1;
tcell = contentRow.CreateCell(3);
tcell.SetCellValue(emails[i].To);
tcell.CellStyle = style1;
tcell = contentRow.CreateCell(4);
tcell.SetCellValue(emails[i].Cc);
tcell.CellStyle = style1;
string tmp = emails[i].Content;
if (tmp.Length > 1000)
{
tmp = tmp.Substring(0, 1000) + "****内容未完****";
}
tcell = contentRow.CreateCell(5);
tcell.SetCellValue(tmp);
tcell.CellStyle = style1;
//存储附件
if (emailClient.GetMessageSize(emails[i].no) > 10 * 1024 * 1024)
{
//邮件大于10m不接收附件
tcell = contentRow.CreateCell(6);
tcell.SetCellValue("****附件过大，请去邮箱自行查看****");
tcell.CellStyle = style1;
Invoke(printstr, (object)(String.Format("保存第 {0} 封邮件 ,附件过大无法下载。", i + 1)));
}
else
{
message = emailClient.GetMessage(emails[i].no);
List <MessagePart> attachments = message.FindAllAttachments();
int attno = 0;
foreach (MessagePart attachment in attachments)
{
attno++;
string filename = attPath + "[" + emails[i].no + attachments.Count + "] - " + attachment.FileName;
FileInfo fi = new FileInfo(filename);
attachment.Save(fi);
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = filename;
//link.Address = ToUnicode(filename);
ICell cell = contentRow.CreateCell(5 + attno, CellType.Blank);
cell.SetCellValue(attachment.FileName);
cell.Hyperlink = link;
cell.CellStyle = style2;
}
if (attno == 0)
{
//无附件
tcell = contentRow.CreateCell(6);
tcell.SetCellValue("无");
tcell.CellStyle = style1;
}
Invoke(printstr, (object)(String.Format("保存第 {0} 封邮件 , {1} 个附件", i + 1, attachments.Count)));
}
}
FileStream file = new FileStream(excelFileName, FileMode.Create, FileAccess.ReadWrite);
wb.Write(file);
file.Close();
file.Dispose();
Invoke(printstr, (object)("excel文件输出完毕。请查看：" + excelFileName));
Invoke(updateState, (object)SystemState.endwork);
}
catch (Exception e)
{
Invoke(printstr, (object)("出错，错误码：" + e.Message));
Invoke(updateState, (object)SystemState.endwork);
}
}
//public static IFont font = newhssfworkbook.CreateFont();//创建字体样式
//public static ICellStyle style = newhssfworkbook.CreateCellStyle();//创建单元格样式
public static void WriteExcel(TelCollection tel)
{
#region 设置字体
font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index; //设置字体颜色
style.SetFont(font); //设置单元格样式中的字体样式
#endregion
//默认数据行
var newrow = newsheet.CreateRow(newsheet.LastRowNum + 1);//创建新行
//搜索引擎
var newcell0 = newrow.CreateCell(0);
newcell0.SetCellValue(tel.Search);
#region 号码
var newcell1 = newrow.CreateCell(1);
newcell1.SetCellValue(tel.Telphone); //手机号
//设置号码超链接
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url); //建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
if (tel.Search == "360")
{
link.Address = Reg360.url + tel.Telphone;//给HSSFHyperlink的地址赋值
}
else
{
link.Address = RegBaidu.url + tel.Telphone;
}
newcell1.Hyperlink = link; //将链接方式赋值给单元格的Hyperlink即可将链接附加到单元格上
//超链接字体
newcell1.CellStyle = style; //为单元格设置显示样式
#endregion
#region 标题
var newcell2 = newrow.CreateCell(2);
newcell2.SetCellValue(tel.Title); //标题
//设置标题超链接
HSSFHyperlink titleLink = new HSSFHyperlink(HyperlinkType.Url); //建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
titleLink.Address = tel.TitleLink; //给HSSFHyperlink的地址赋值
newcell2.Hyperlink = titleLink; //将链接方式赋值给单元格的Hyperlink即可将链接附加到单元格上
//设置字体
newcell2.CellStyle = style; //为单元格设置显示样式
#endregion
//简介
var newcell3 = newrow.CreateCell(3);
newcell3.SetCellValue(tel.Abstract);
Object locker = new Object();
lock (locker)
{
try
{
FileStream fs = File.OpenWrite(filePathOut);
newhssfworkbook.Write(fs); //向打开的这个xls文件中写入表并保存。
fs.Close();
}
catch (Exception ex)
{
StringHelp.Write(StringHelp.pathError, ex.Message + tel.Telphone + "\r\n");
System.Threading.Thread.Sleep(1 * 1000);
WriteExcel(tel);
StringHelp.Write(StringHelp.pathError, tel.Telphone + "重新执行\r\n");
}
}
}
/// <summary>
/// 生成数据行
/// </summary>
protected virtual void GenerateItems <T>(IEnumerable <T> dataSource, ExcelColumnBuilder <T> columns, ISheet sheet)
where T : class
{
if (!dataSource.Any())
{
return;
}
//保存单元格样式信息
List <ICellStyle> bodyStyles = new List <ICellStyle>(columns.ColumnCount);
//生成样式信息
foreach (var column in columns)
{
#region 设置样式
var bodyStyle = this.Workbook.CreateCellStyle();
this.SetDefaultBodyStyle(bodyStyle);
if (column.BodyStyle != null)
{
column.BodyStyle(bodyStyle);
}
bodyStyle.WrapText = false; //设置为自动换行
bodyStyle.Alignment = HorizontalAlignment.CenterSelection; //设置为水平居中
bodyStyle.VerticalAlignment = VerticalAlignment.Center; //设置为垂直居中
bodyStyles.Add(bodyStyle);
#endregion
}
//第一行用户数据列头
Int32 rowIndex = 1;
foreach (var item in dataSource)
{
var row = sheet.CreateRow(rowIndex);
Int32 columnIndex = 0;
rowIndex++;
foreach (var column in columns)
{
var cell = row.CreateCell(columnIndex);
cell.CellStyle = bodyStyles[columnIndex];
/************如果是集合就将集合的数据显示到一个Cell并换行***********/
if (column.IsCollection)
{
if (column.CustomRenderer == null)
{
#region 获取数据 并 格式化单元格数据
Object value = null;
if (column.ColumnDelegate != null)
{
value = column.ColumnDelegate(item);
}
else
{
var property = item.GetType().GetProperty(column.Name);
if (property != null)
{
value = property.GetValue(item, null);
}
}
String formattedValue = null;
if (value != null)
{
if (column.Format != null)
{
formattedValue = String.Format(column.Format, value);
}
else
{
if (value is IEnumerable)
{
var IValue = value as IEnumerable;
var enumerable = IValue as object[] ?? IValue.Cast <object>().ToArray();
var list = Enumerable.OfType <object>(enumerable);
if (list.Count() > 1)
{
foreach (var obj in list)
{
formattedValue += "\r\n" + obj.ToString();
}
}
else
{
var firstOrDefault = list.FirstOrDefault();
if (firstOrDefault != null)
{
formattedValue = firstOrDefault.ToString();
}
}
}
//formattedValue = value.ToString();
}
}
#endregion
cell.SetCellValue(formattedValue);
}
else
{
//自定义呈现单元格
column.CustomRenderer(item, cell);
}
}
/***********************/
else
{
if (column.CustomRenderer == null)
{
#region 获取数据 并 格式化单元格数据
dynamic value = null;
if (column.ColumnDelegate != null)
{
value = column.ColumnDelegate(item);
}
else
{
var property = item.GetType().GetProperty(column.Name);
if (property != null)
{
value = property.GetValue(item, null);
}
}
if (value != null)
{
if (column.Format != null)
{
var formattedValue = String.Format(column.Format, value);
cell.SetCellValue(formattedValue);
}
else
{
if (value is decimal)
{
cell.SetCellValue((double)value);
}
else if (value is int)
{
cell.SetCellValue((double)value);
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
#endregion
}
else
{
//自定义呈现单元格
column.CustomRenderer(item, cell);
}
}
#region 设置链接
if (null != column.HrefDelegate)
{
var href = column.HrefDelegate(item);
if (null != href)
{
var link = new HSSFHyperlink(new HyperlinkType())
{
Address = href.ToString()
};
cell.Hyperlink = link;
}
}
#endregion
columnIndex++;
}
}
}
/// <summary>
/// 导出数据到多个Excel标签页中
/// </summary>
/// <param name="multiSheet">多标签页信息</param>
/// <param name="s">输出流</param>
public static void ExportToMutilSheet(ExportMultiSheet multiSheet, Stream s)
{
if (multiSheet == null || multiSheet.ListSheet == null || multiSheet.ListSheet.Count == 0)
{
throw new ArgumentNullException();
}
string fileExt = ".xls";
if (string.IsNullOrEmpty(multiSheet.FileName))
{
multiSheet.FileName = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + fileExt;
}
else
{
if (!multiSheet.FileName.EndsWith(fileExt))
{
multiSheet.FileName = multiSheet.FileName + fileExt;
}
}
IWorkbook workbook = new HSSFWorkbook();
//文本样式
ICellStyle centerStyle = workbook.CreateCellStyle();
centerStyle.VerticalAlignment = VerticalAlignment.Center;
centerStyle.Alignment = HorizontalAlignment.Center;
ICellStyle leftStyle = workbook.CreateCellStyle();
leftStyle.VerticalAlignment = VerticalAlignment.Center;
leftStyle.Alignment = HorizontalAlignment.Left;
ICellStyle rightStyle = workbook.CreateCellStyle();
rightStyle.VerticalAlignment = VerticalAlignment.Center;
rightStyle.Alignment = HorizontalAlignment.Right;
//超链接字体颜色
IFont blueFont = workbook.CreateFont();
blueFont.Color = HSSFColor.Blue.Index;
ICellStyle leftStyleLink = workbook.CreateCellStyle();
leftStyleLink.VerticalAlignment = VerticalAlignment.Center;
leftStyleLink.Alignment = HorizontalAlignment.Left;
leftStyleLink.SetFont(blueFont);
ISheet sheet = null;
DataTable dt = null;
DataRow dr = null;
List <ColumnInfo> ColumnInfoList = null;
int rowHeight = 20;
object cellValue = null;
IRow row = null;
ICell cell = null;
//每写入100条数据进度更新一次
int totalData = multiSheet.ListSheet.Sum(e => e.Data.Rows.Count);
//写入总数
int writeTotal = 0;
foreach (ExportSheetInfo exportSheetInfo in multiSheet.ListSheet)
{
dt = exportSheetInfo.Data;
ColumnInfoList = exportSheetInfo.ColumnInfoList;
//寻找列头和DataTable之间映射关系
foreach (DataColumn col in dt.Columns)
{
ColumnInfo info = ColumnInfoList.FirstOrDefault <ColumnInfo>(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
if (info != null)
{
info.Align = info.Align ?? "left";
switch (info.Align.ToLower())
{
case "left":
info.Style = leftStyle;
break;
case "center":
info.Style = centerStyle;
break;
case "right":
info.Style = rightStyle;
break;
}
info.IsMapDT = true;
}
}
//标题头索引
int headIndex = string.IsNullOrEmpty(exportSheetInfo.Remark) ? 0 : 1;
int total = dt.Rows.Count;
int sheetNum = (int)Math.Ceiling(total * 1.0 / (MAX_ROW_INDEX - headIndex - 1));
int drIndex = 0;
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
{
string sheetName = string.IsNullOrEmpty(exportSheetInfo.SheetName) ? "Sheet " + workbook.NumberOfSheets : (sheetNum > 1 ? (exportSheetInfo.SheetName + sheetIndex) : exportSheetInfo.SheetName);
sheet = workbook.CreateSheet(sheetName);
sheet.CreateFreezePane(0, headIndex + 1, 0, headIndex + 1);
if (headIndex > 0)
{
//输出备注行
IRow RemarkRow = sheet.CreateRow(0);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, ColumnInfoList.Count - 1));
ICell rcell = RemarkRow.CreateCell(0);
ICellStyle remarkStyle = workbook.CreateCellStyle();
remarkStyle.WrapText = true;
remarkStyle.VerticalAlignment = VerticalAlignment.Top;
remarkStyle.Alignment = HorizontalAlignment.Left;
IFont rfont = workbook.CreateFont();
rfont.FontHeightInPoints = 12;
remarkStyle.SetFont(rfont);
rcell.CellStyle = remarkStyle;
RemarkRow.HeightInPoints = rowHeight * 5;
rcell.SetCellValue(exportSheetInfo.Remark);
}
//输出表头
IRow headerRow = sheet.CreateRow(headIndex);
//设置行高
headerRow.HeightInPoints = rowHeight;
//首行样式
ICellStyle HeaderStyle = workbook.CreateCellStyle();
HeaderStyle.FillPattern = FillPattern.SolidForeground;
HeaderStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
IFont font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
HeaderStyle.SetFont(font);
HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
HeaderStyle.Alignment = HorizontalAlignment.Center;
//输出表头信息 并设置表头样式
int i = 0;
foreach (var data in ColumnInfoList)
{
cell = headerRow.CreateCell(i);
cell.SetCellValue(data.Header.Trim());
cell.CellStyle = HeaderStyle;
i++;
}
//开始循环所有行
int iRow = 1 + headIndex;
int startRow = iRow;
while (startRow < MAX_ROW_INDEX && drIndex < total)
{
row = sheet.CreateRow(startRow);
row.HeightInPoints = rowHeight;
i = 0;
dr = dt.Rows[drIndex];
foreach (var item in ColumnInfoList)
{
cell = row.CreateCell(i);
if (item.IsMapDT)
{
cellValue = dr[item.Field];
Type columnType = dt.Columns[item.Field].DataType;
cell.SetCellValue(cellValue, columnType);
cell.CellStyle = item.Style;
if (item.IsLink)
{
cellValue = dr[item.Field + "Link"];
if (cellValue != DBNull.Value && cellValue != null)
{
//建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
//给HSSFHyperlink的地址赋值 ，默认为该列加上Link
link.Address = cellValue.ToString();
cell.Hyperlink = link;
cell.CellStyle = leftStyleLink;
}
}
}
i++;
}
drIndex++;
startRow++;
writeTotal++;
}
//自适应列宽度
for (int j = 0; j < ColumnInfoList.Count; j++)
{
sheet.AutoSizeColumn(j);
int width = sheet.GetColumnWidth(j) + 2560;
sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
}
}
}
if (s is ZipOutputStream)
{
ZipOutputStream zs = s as ZipOutputStream;
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
byte[] m_buffer = ms.GetBuffer();
zs.Write(m_buffer, 0, m_buffer.Length);
}
}
else
{
workbook.Write(s);
}
}
public static void ExportToExcel()
{
InitializeWorkbook();
//创建日期格式
var format = hssfworkbook.CreateDataFormat();
var datetimestyle = hssfworkbook.CreateCellStyle();
datetimestyle.DataFormat = format.GetFormat("yyyy年m月d日h时mm分");
//创建超链接格式
var hlinkFont = hssfworkbook.CreateFont();
hlinkFont.Underline = FontUnderlineType.Single;
hlinkFont.Color = HSSFColor.Blue.Index;
var hlinkStyle = hssfworkbook.CreateCellStyle();
hlinkStyle.SetFont(hlinkFont);
//创建表头
var sheet = hssfworkbook.CreateSheet("电机信息");
var row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("电机ID");
row.CreateCell(1).SetCellValue("电机名称");
row.CreateCell(2).SetCellValue("电机运行时间");
try
{
for (int i = 0; i < ConfigPump.Pumpinfos.Count; i++)
{
var pumpinfo = ConfigPump.Pumpinfos[i];
var row2 = sheet.CreateRow(i + 1);
var linkCell = row2.CreateCell(0);
linkCell.SetCellValue(pumpinfo.PumpId);
row2.CreateCell(1).SetCellValue(pumpinfo.PumpName);
row2.CreateCell(2).SetCellValue(pumpinfo.TotalRunTime.ToString());
var link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = pumpinfo.PumpName + "信息!A1";
linkCell.Hyperlink = link;
linkCell.CellStyle = hlinkStyle;
var sheet2 = hssfworkbook.CreateSheet(pumpinfo.PumpName + "信息");
var row3 = sheet2.CreateRow(0);
row3.CreateCell(0).SetCellValue("启动时间");
row3.CreateCell(1).SetCellValue("停止时间");
row3.CreateCell(2).SetCellValue("运行时间");
for (int j = 0; j < pumpinfo.PumpTimes.Count; j++)
{
var row4 = sheet2.CreateRow(j + 1);
var cell2 = row4.CreateCell(0);
cell2.SetCellValue(pumpinfo.PumpTimes[j].StartTime);
cell2.CellStyle = datetimestyle;
var cell3 = row4.CreateCell(1);
cell3.SetCellValue(pumpinfo.PumpTimes[j].StopTime);
cell3.CellStyle = datetimestyle;
row4.CreateCell(2).SetCellValue(pumpinfo.PumpTimes[j].RunTime.ToString());
}
sheet2.DefaultColumnWidth = 22;
}
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
WriteToFile();
}
catch (Exception)
{
throw;
}
}
public void Object2Cell(System.Reflection.FieldInfo prop, object obj, NPOI.SS.UserModel.ICell cell)
#endif
{
//如果是一个公式计算结果的Cell，不能往里面生写
if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
return;
}
var type = obj.GetType();
if (type == typeof(sbyte))
{
var value = (sbyte)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(Int16))
{
var value = (Int16)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(Int32))
{
var value = (Int32)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(Int64))
{
var value = (Int64)obj;
cell.SetCellValue(value);
return;
}
if (type == typeof(byte))
{
var value = (byte)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(UInt16))
{
var value = (UInt16)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(UInt32))
{
var value = (UInt32)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(UInt64))
{
var value = (UInt64)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(double))
{
var value = (double)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(float))
{
var value = (float)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(string))
{
var value = (string)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(bool))
{
var value = (bool)obj;
cell.SetCellValue(value);
return;
}
else if (type == typeof(DateTime))
{
var value = (DateTime)obj;
cell.SetCellValue(value);
return;
}
else if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List <>))
{
var argType = type.GenericTypeArguments[0];
//var atts = argType.GetCustomAttributes(typeof(EngineNS.IO.Serializer.ExcelSheetAttribute), true);
//if (atts == null || atts.Length == 0)
// return;
//var sheetAttr = atts[0] as EngineNS.IO.Serializer.ExcelSheetAttribute;
string sheetName = "LST_" + prop.Name;
int index = -1;
var link = cell.Hyperlink as HSSFHyperlink;
if (link != null && link.Address.Contains($"{sheetName}!A"))
{
var suffix = link.Address.Substring($"{sheetName}!A".Length);
index = System.Convert.ToInt32(suffix);
if (index > 0)
{
index--;
}
}
var sheet = this.GetSheetSure($"{sheetName}");
//var header = InitSheet(sheet, type);
var row = GetRowSure(sheet, index);
FillObjectList2Row(obj as System.Collections.IList, row, argType);
if (link == null)
{
link = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
}
link.Address = $"{sheetName}!A{row.RowNum + 1}";
cell.SetCellValue(link.Address);
cell.Hyperlink = link;
var target = row.GetCell(0);
if (target != null)
{
var linkBack = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
linkBack.Address = $"{cell.Sheet.SheetName}!{GetColumnString(cell.ColumnIndex)}{cell.RowIndex + 1}";
target.Hyperlink = linkBack;
target.SetCellValue(linkBack.Address);
}
}
else
{
var atts = type.GetCustomAttributes(typeof(EngineNS.IO.Serializer.ExcelSheetAttribute), true);
if (atts == null || atts.Length == 0)
{
return;
}
var sheetAttr = atts[0] as EngineNS.IO.Serializer.ExcelSheetAttribute;
var sheet = this.GetSheetSure(sheetAttr.SheetName);
var header = InitSheet(sheet, type);
string sheetName = sheetAttr.SheetName;
int index = -1;
var link = cell.Hyperlink as HSSFHyperlink;
if (link != null && link.Address.Contains($"{sheetName}!A"))
{
var suffix = link.Address.Substring($"{sheetName}!A".Length);
index = System.Convert.ToInt32(suffix);
if (index > 0)
{
index--;
}
}
var row = GetRowSure(sheet, index);
FillObject2Row(obj, row, header, type);
if (link == null)
{
link = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
}
link.Address = $"{sheetName}!A{row.RowNum + 1}";
cell.SetCellValue(link.Address);
cell.Hyperlink = link;
var target = GetCellSure(row, 0, typeof(string));
if (target != null)
{
var linkBack = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.Document);
linkBack.Address = $"{cell.Sheet.SheetName}!{GetColumnString(cell.ColumnIndex)}{cell.RowIndex+1}";
target.Hyperlink = linkBack;
target.SetCellValue(linkBack.Address);
}
}
}
