private void Initialize()
{
//CT_Font ctFont = CreateDefaultFont();
XSSFFont xssfFont = CreateDefaultFont();
fonts.Add(xssfFont);
CT_Fill[] ctFill = CreateDefaultFills();
fills.Add(new XSSFCellFill(ctFill[0]));
fills.Add(new XSSFCellFill(ctFill[1]));
CT_Border ctBorder = CreateDefaultBorder();
borders.Add(new XSSFCellBorder(ctBorder));
CT_Xf styleXf = CreateDefaultXf();
styleXfs.Add(styleXf);
CT_Xf xf = CreateDefaultXf();
xf.xfId = 0;
xfs.Add(xf);
}
public void TestApplyFont()
{
XSSFRichTextString rt = new XSSFRichTextString();
rt.Append("123");
rt.Append("4567");
rt.Append("89");
Assert.AreEqual("123456789", rt.String);
Assert.AreEqual(false, rt.HasFormatting());
XSSFFont font1 = new XSSFFont();
font1.IsBold = (true);
rt.ApplyFont(2, 5, font1);
Assert.AreEqual(true, rt.HasFormatting());
Assert.AreEqual(4, rt.NumFormattingRuns);
Assert.AreEqual(0, rt.GetIndexOfFormattingRun(0));
Assert.AreEqual("12", rt.GetCTRst().GetRArray(0).t);
Assert.AreEqual(2, rt.GetIndexOfFormattingRun(1));
Assert.AreEqual("345", rt.GetCTRst().GetRArray(1).t);
Assert.AreEqual(5, rt.GetIndexOfFormattingRun(2));
Assert.AreEqual(2, rt.GetLengthOfFormattingRun(2));
Assert.AreEqual("67", rt.GetCTRst().GetRArray(2).t);
Assert.AreEqual(7, rt.GetIndexOfFormattingRun(3));
Assert.AreEqual(2, rt.GetLengthOfFormattingRun(3));
Assert.AreEqual("89", rt.GetCTRst().GetRArray(3).t);
Assert.AreEqual(-1, rt.GetIndexOfFormattingRun(9999));
Assert.AreEqual(-1, rt.GetLengthOfFormattingRun(9999));
Assert.IsNull(rt.GetFontAtIndex(9999));
}
public void GetFontAt()
{
XSSFWorkbook workbook = new XSSFWorkbook();
try
{
StylesTable styleSource = workbook.GetStylesSource();
short i = 0;
//get default font
IFont fontAt = workbook.GetFontAt(i);
Assert.IsNotNull(fontAt);
//get customized font
XSSFFont customFont = new XSSFFont();
customFont.IsItalic = (true);
int x = styleSource.PutFont(customFont);
fontAt = workbook.GetFontAt((short)x);
Assert.IsNotNull(fontAt);
}
finally
{
workbook.Close();
}
}
public void TestSetTextSingleParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromRgb(0, 255, 255)));
font.FontName = ("Arial");
rt.ApplyFont(font);
shape.SetText(rt);
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
Assert.AreEqual("Test String", paras[0].Text);
List <XSSFTextRun> runs = paras[0].TextRuns;
Assert.AreEqual(1, runs.Count);
Assert.AreEqual("Arial", runs[0].FontFamily);
var clr = runs[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb);
wb.Close();
}
public string exportExcel(string fileName, string serverUrl, string serverFlag, string serverConnName, string layer, string wkt = null, string where = null, string whereKey = null, string whereValue = null, string statistics = null, string clip = null)
{
string daochu_result = "";
{
try
{
string fileNameDir = System.AppDomain.CurrentDomain.BaseDirectory;
string saveFilePath = fileNameDir + "\\resource\\download\\excel";
if (!Directory.Exists(saveFilePath))
{
Directory.CreateDirectory(saveFilePath);
}
{
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFFont fieldFont = (XSSFFont)workBook.CreateFont();
fieldFont.FontHeightInPoints = 13;
fieldFont.Boldweight = 600;
ICellStyle fieldStyle = workBook.CreateCellStyle();
fieldStyle.Alignment = HorizontalAlignment.Center;
fieldStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
fieldStyle.SetFont(fieldFont);
////下面这两行颜色不管用
fieldStyle.FillForegroundColor = IndexedColors.Blue.Index;
fieldStyle.FillBackgroundColor = IndexedColors.Red.Index;
ICellStyle dataStyle = workBook.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.Center;
dataStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
{
string returnstr = "";
Common common = new Common();
XSSFSheet sheet_sub = (XSSFSheet)workBook.CreateSheet(layer);
if (serverFlag == "query")
{
IRow _frow = sheet_sub.CreateRow(0);
_frow.Height = 3 * 256;//行高
_frow.CreateCell(0).SetCellValue("序号");
_frow.GetCell(0).CellStyle = fieldStyle;
string url = serverUrl + "query?serverConnName=" + serverConnName + "&tableName=" + layer + "&wkt=" + wkt + "&where=" + where + "&clip=" + clip + "&whereValue=" + whereValue + "&whereKey=" + whereKey;
returnstr = common.queryDataPost(url);
if (returnstr.IndexOf("\"result\":\"error\"") > -1)
{
daochu_result = "\"exportResult\":\"error\",\"exportMessage\":\"error\"";
workBook.Close();
}
var jo = (JObject)JsonConvert.DeserializeObject(returnstr);
if (jo["result"].ToString() == "success")
{
var joo = (JObject)jo["data"];
var ja = (JArray)joo["features"];
var index = 0;
foreach (JObject items in ja)
{
IRow _frowData = sheet_sub.CreateRow(index + 1);
_frowData.Height = 2 * 256;//行高
_frowData.CreateCell(0).SetCellValue(index + 1);
_frowData.GetCell(0).CellStyle = dataStyle;
JObject _properties = (JObject)items["properties"];
var _colIndex = 1;
foreach (var _item in _properties)
{
var _key = _item.Key;
var _value = _item.Value;
if (_key.ToLower() == "gid" || _key.ToLower() == "calarea")
{
continue;
}
if (index == 0)
{
//填充表头
_frow.CreateCell(_colIndex).SetCellValue(_key);
_frow.GetCell(_colIndex).CellStyle = fieldStyle;
sheet_sub.SetColumnWidth(_colIndex, 18 * 256);
}
_frowData.CreateCell(_colIndex).SetCellValue(_value.ToString());
_frowData.GetCell(_colIndex).CellStyle = dataStyle;
_colIndex++;
}
index++;
}
}
}
else if (serverFlag == "statistics")
{
IRow _frow = sheet_sub.CreateRow(0);
_frow.Height = 3 * 256;//行高
_frow.CreateCell(0).SetCellValue("序号");
_frow.CreateCell(1).SetCellValue("类型");
_frow.CreateCell(2).SetCellValue("数量");
_frow.CreateCell(3).SetCellValue("面积(亩)");
string url = serverUrl + "statistics?serverConnName=" + serverConnName + "&tableName=" + layer + "&wkt=" + wkt + "&where=" + where + "&clip=" + clip + "&whereValue=" + whereValue + "&whereKey=" + whereKey + "&fieldName=" + statistics;
returnstr = common.queryDataPost(url);
if (returnstr.IndexOf("\"result\":\"error\"") > -1)
{
daochu_result = "\"exportResult\":\"error\",\"exportMessage\":\"error\"";
workBook.Close();
}
var jo = (JObject)JsonConvert.DeserializeObject(returnstr);
if (jo["result"].ToString() == "success")
{
var ja = (JArray)jo["data"];
var index = 0;
for (int i = 0; i < 4; i++)
{
_frow.GetCell(i).CellStyle = fieldStyle;
sheet_sub.SetColumnWidth(i, 18 * 256);
}
foreach (JObject items in ja)
{
string _name = "", _amount = "", _calarea = "";
foreach (var item in items)
{
switch (item.Key)
{
case "name":
_name = item.Value.ToString();
break;
case "amount":
_amount = item.Value.ToString();
break;
case "calarea":
_calarea = item.Value.ToString();
break;
}
}
IRow _frowData = sheet_sub.CreateRow(index + 1);
_frowData.Height = 2 * 256;//行高
_frowData.CreateCell(0).SetCellValue(index + 1);
_frowData.CreateCell(1).SetCellValue(_name);
_frowData.CreateCell(2).SetCellValue(_amount);
string _mj = areaTransfer(_calarea);
_frowData.CreateCell(3).SetCellValue(_mj);
for (int jj = 0; jj < 4; jj++)
{
_frowData.GetCell(jj).CellStyle = dataStyle;
}
index++;
}
}
}
}
//保存
try
{
string saveFileName = saveFilePath + "\\" + fileName + ".xlsx";
using (FileStream fs = new FileStream(saveFileName, FileMode.Create))
{
workBook.Write(fs);
workBook.Close();
daochu_result = "\"exportResult\":\"success\",\"exportMessage\":\"" + "/resource/download/excel/" + fileName + ".xlsx" + "\"";
}
}
catch (System.Exception e)
{
daochu_result = "\"exportResult\":\"error\",\"exportMessage\":\"" + e.ToString() + "\"";
workBook.Close();
}
}
}
catch (Exception e)
{
daochu_result = "\"exportResult\":\"errpr\",\"exportMessage\":\"" + e.ToString() + "\"";
}
}
return(daochu_result);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
//workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (XSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(1);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
/*
/// <summary>
/// Create ORACLE Database Connection
/// </summary>
/// <returns></returns>
public SqlConnection CreateConnectionOracle(out bool returnStatus, out string returnErrorMessage)
{
string connectionString;
SqlConnection connection;
try
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ReefPermitsDev2"].ToString();
connection = new SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();
returnErrorMessage = "";
returnStatus = true;
return connection;
}
catch (Exception ex)
{
returnErrorMessage = ex.Message;
returnStatus = false;
connection = new OracleConnection();
return connection;
}
}
*/
/// <remarks></remarks>
//XLSX spreadsheet
public MemoryStream TableToXLSXms(DataTable SourceData, string[] SourceColOrder, string[] SourceColText, bool IncludeHeader, string FileToSave)
{
string ErrorMessage;
//bool returnEmptyOnError;
XSSFWorkbook xlWorkbook;
XSSFSheet xlSheet;
XSSFRow xlRow;
XSSFCell xlCell;
int CurrentRow, CurrentColumn;
MemoryStream ms = new MemoryStream();
try
{
FileInfo fiSave = new FileInfo(FileToSave);
string SheetName = fiSave.Name.Substring(0, fiSave.Name.IndexOf("."));
SheetName = Regex.Replace(SheetName, "[^A-Z]", String.Empty, RegexOptions.IgnoreCase);
//bool blnNoError = true;
xlWorkbook = new XSSFWorkbook();
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
//dsi.Company = "{company name here}";
//xlWorkbook.DocumentSummaryInformation = dsi;
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Title = SheetName;
//si.Author = "{user name here}";
//si.ApplicationName = "{application's name}";
//xlWorkbook.SummaryInformation = si;
xlSheet = (XSSFSheet)xlWorkbook.CreateSheet(SheetName);
xlSheet.DisplayGridlines = false;
XSSFCellStyle xlStyle;
//int[] aryWidths = new int[SourceData.Columns.Count - 1]; //old code. Seems to work for Oracle?
int[] aryWidths = new int[SourceData.Columns.Count];
CurrentRow = 0;
CurrentColumn = 0;
//Write header
if (IncludeHeader)
{
xlRow = (XSSFRow)xlSheet.CreateRow(CurrentRow);
if (SourceColText == null)
{
//Auto names. Does not work
/*
SourceColOrder = new string[SourceData.Columns.Count - 1];
foreach (DataColumn dcCol in SourceData.Columns)
{
SourceColOrder[CurrentColumn] = dcCol.ColumnName;
xlCell = (HSSFCell)xlRow.CreateCell(CurrentColumn);
//xlStyle = xlWorkbook.CreateCellStyle();
//HSSFFont xlFont = xlWorkbook.CreateFont();
xlStyle = (HSSFCellStyle)xlWorkbook.CreateCellStyle();
HSSFFont xlFont = (HSSFFont)xlWorkbook.CreateFont();
xlFont.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index;
//xlFont.Boldweight = HSSFFont.FONT_ARIAL;
xlStyle.SetFont(xlFont);
xlStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index;
//xlStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
xlCell.SetCellValue(dcCol.ColumnName.Replace("_", " "));
xlCell.CellStyle = xlStyle;
if (dcCol.ColumnName.Length + 1 > aryWidths[CurrentColumn])
{
aryWidths[CurrentColumn] = dcCol.ColumnName.Length + 1;
}
CurrentColumn += 1;
}
*/
}
else
{
//Specified names
foreach (string strName in SourceColText)
{
xlCell = (XSSFCell)xlRow.CreateCell(CurrentColumn);
xlStyle = (XSSFCellStyle)xlWorkbook.CreateCellStyle();
xlStyle.BorderLeft = BorderStyle.Medium;
xlStyle.BorderRight = BorderStyle.Thin;
xlStyle.BorderBottom = BorderStyle.Medium;
xlStyle.BorderTop = BorderStyle.Medium;
XSSFFont xlFont = (XSSFFont)xlWorkbook.CreateFont();
//xlFont.Color = NPOI.XSSF.Util.XSSFColor.Blue.Index;
//xlFont.Color = (short)NPOI.SS.UserModel.FontColor.Red;
xlFont.Color = IndexedColors.Blue.Index;
xlFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
xlStyle.SetFont(xlFont);
//xlStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index;
//xlStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
xlCell.SetCellValue(strName);
xlCell.CellStyle = xlStyle;
//if (strName.Length + 1 > aryWidths[CurrentColumn])
//{
//aryWidths[CurrentColumn] = strName.Length + 1;
//}
CurrentColumn += 1;
}
}
}
xlStyle = (XSSFCellStyle)xlWorkbook.CreateCellStyle();
xlStyle.BorderLeft = BorderStyle.Thin;
xlStyle.BorderRight = BorderStyle.Thin;
xlStyle.BorderBottom = BorderStyle.Thin;
//Write data rows
Object SourceDataCell;
DateTime TripDate7;
string TripDatestr = "";
foreach (DataRow drvRow in SourceData.Rows)
{
CurrentRow += 1;
CurrentColumn = 0;
xlRow = (XSSFRow)xlSheet.CreateRow(CurrentRow);
foreach (string strColName in SourceColOrder) //For CurrentColumn = 0 To SourceData.Columns.Count - 1
{
SourceDataCell = drvRow[strColName];
xlCell = (XSSFCell)xlRow.CreateCell(CurrentColumn);
//xlStyle = (HSSFCellStyle)xlWorkbook.CreateCellStyle();
//xlStyle.BorderLeft = CellBorderType.THIN;
//xlStyle.BorderRight = CellBorderType.THIN;
//xlStyle.BorderBottom = CellBorderType.THIN;
xlCell.CellStyle = xlStyle;
if (SourceDataCell.GetType() == typeof(System.Int32) || SourceDataCell.GetType() == typeof(System.Int64))
{
xlCell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
if (SourceDataCell != null) //IsDBNull
{
//xlCell.SetCellValue((Double)SourceDataCell);
xlCell.SetCellValue(SourceDataCell.ToString());
}
}
else if (SourceDataCell.GetType() == typeof(System.DateTime))
{
xlCell.SetCellType(NPOI.SS.UserModel.CellType.String);
if (SourceDataCell != null) //IsDBNull
{
TripDate7 = DateTime.Parse(SourceDataCell.ToString());
TripDatestr = UtilitiesBLL.FormatDate(TripDate7);
xlCell.SetCellValue(TripDatestr);
//xlCell.SetCellValue((DateTime)TripDate7);
//xlCell.SetCellValue((DateTime)SourceDataCell);
//xlCell.SetCellValue(SourceDataCell.ToString());
}
}
else if (SourceDataCell.GetType() == typeof(System.Double) || SourceDataCell.GetType() == typeof(System.Decimal))
{
xlCell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
if (SourceDataCell != null) //IsDBNull
{
//xlCell.SetCellValue((Double)SourceDataCell);
xlCell.SetCellValue(SourceDataCell.ToString());
}
}
else
{
xlCell.SetCellValue(SourceDataCell.ToString());
}
//if (SourceDataCell.ToString().Length > aryWidths[CurrentColumn])
//{
//aryWidths[CurrentColumn] = SourceDataCell.ToString().Length;
//}
CurrentColumn += 1;
}
}
//Set column widths
for (CurrentColumn = 0; CurrentColumn <= aryWidths.GetUpperBound(0); CurrentColumn++)
{
if (aryWidths[CurrentColumn] > 50)
{
xlSheet.SetColumnWidth(CurrentColumn, 50 * 256);
}
else if (aryWidths[CurrentColumn] < 10)
{
xlSheet.SetColumnWidth(CurrentColumn, 10 * 256);
}
else
{
xlSheet.SetColumnWidth(CurrentColumn, (aryWidths[CurrentColumn] + 1) * 256);
}
}
//MemoryStream ms = new MemoryStream();
xlWorkbook.Write(ms);
ms.Flush();
ms.Close();
}
catch (Exception ex)
{
//blnNoError = false;
ErrorMessage = ex.Message;
//HttpContext.Current.Trace.Warn(ex.ToString());
MemoryStream mserr = new MemoryStream();
//byte[] BLOBbyterr;
//mserr.Write(BLOBbyterr, 0, BLOBbyterr.Length);
return mserr;
}
return ms;
}
public int PutFont(XSSFFont font)
{
return(PutFont(font, false));
}
public static void ExportToExcel(DataSet ds, string ExcelFilePath = null)
{
try
{
XSSFWorkbook hssfwb;
hssfwb = new XSSFWorkbook();
XSSFSheet sh;
int sheetcount = 0;
XSSFFont _style = (XSSFFont)hssfwb.CreateFont();
_style.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
_style.Boldweight = 2;
foreach (DataTable table in ds.Tables)
{
sh = (XSSFSheet)hssfwb.CreateSheet(ds.Tables[sheetcount].TableName);
for (int col = 1; col < table.Columns.Count + 1; col++)
{
if (col == 1)
{
sh.CreateRow(0);
}
sh.GetRow(0)
.CreateCell(col - 1);
sh.GetRow(0)
.GetCell(col - 1)
.SetCellValue(table.Columns[col - 1].ColumnName);
}
for (int i = 0; i < table.Rows.Count; i++)
{
var r = sh.CreateRow(i + 1);
for (int j = 0; j < table.Columns.Count; j++)
{
sh.GetRow(i + 1)
.CreateCell(j);
sh.GetRow(i + 1)
.GetCell(j)
.SetCellValue(table.Rows[i].ItemArray[j].ToString());
sh.GetRow(i + 1)
.GetCell(j)
.SetCellType(CellType.String);
//if (coloringrows != null && coloringrows.Count > 0 && coloringrows.Contains(i))
//{
// sh.GetRow(i + 1)
// .GetCell(j)
// .CellStyle.SetFont(_style);
// coloringrows.Remove(i);
//}
}
}
sheetcount++;
}
using (FileStream file = new FileStream(ExcelFilePath, FileMode.Create, FileAccess.Write))
{
hssfwb.Write(file);
file.Close();
}
}
//Read the excel to datatable
//HSSFWorkbook hssfwb;
//using (FileStream file = new FileStream(@"c:\test.xls", FileMode.Open, FileAccess.Read))
//{
// hssfwb = new HSSFWorkbook(file);
//}
//ISheet sheet = hssfwb.GetSheet("Arkusz1");
//for (int row = 0; row <= sheet.LastRowNum; row++)
//{
// if (sheet.GetRow(row) != null)
// {
// // write it in your data table
// }
//}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public FileResult ExportExcel()
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("UpassLog");
var headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("Processtime");
headerRow.CreateCell(1).SetCellValue("Category (id)");
headerRow.CreateCell(2).SetCellValue("Event (id)");
headerRow.CreateCell(3).SetCellValue("ProgramID");
headerRow.CreateCell(4).SetCellValue("InstitutionID");
headerRow.CreateCell(5).SetCellValue("GUID");
headerRow.CreateCell(6).SetCellValue("TaskID");
headerRow.CreateCell(7).SetCellValue("StateID");
headerRow.CreateCell(8).SetCellValue("CardSerialNumber");
headerRow.CreateCell(9).SetCellValue("URI");
headerRow.CreateCell(10).SetCellValue("URIType");
headerRow.CreateCell(11).SetCellValue("ErrorID");
headerRow.CreateCell(12).SetCellValue("Info/Error Description");
headerRow.RowStyle = workbook.CreateCellStyle();
headerRow.RowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
sheet.CreateFreezePane(0, 1, 0, 1);
int rowNumber = 1;
var row = sheet.CreateRow(rowNumber++);
foreach (var datarow in RetrievedResult)
{
row = sheet.CreateRow(rowNumber++);
row.CreateCell(0).SetCellValue(datarow.ProcessDatetime.ToString("MM/dd/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture));
row.CreateCell(1).SetCellValue(String.Format("{0} ({1})", datarow.Category, datarow.CategoryID));
row.CreateCell(2).SetCellValue(String.Format("{0} ({1})", datarow.Event, datarow.EventID));
row.CreateCell(3).SetCellValue(datarow.ProgramID);
row.CreateCell(4).SetCellValue(datarow.InstitutionID);
row.CreateCell(5).SetCellValue(datarow.GUID);
row.CreateCell(6).SetCellValue(String.Format("{0}", datarow.TaskID));
row.CreateCell(7).SetCellValue(String.Format("{0}", datarow.StateID));
row.CreateCell(8).SetCellValue(datarow.CardSerialNumber);
row.CreateCell(9).SetCellValue(datarow.URI);
row.CreateCell(10).SetCellValue(datarow.URIType);
row.CreateCell(11).SetCellValue(datarow.ProcessErrorID);
row.CreateCell(12).SetCellValue(datarow.ProcessErrorDescr);
}
//XSSFColor foreColor = new XSSFColor(Color.Red);
XSSFColor backColor = new XSSFColor(Color.Yellow);
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.SetFillForegroundColor(backColor);
style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
for (int i = 0; i <= 12; i++)
{
XSSFCell cell = (XSSFCell)headerRow.GetCell(i);
cell.CellStyle = style;
cell.CellStyle.SetFont(font);
}
for (int i = 0; i <= 12; i++)
{
sheet.AutoSizeColumn(i);
}
MemoryStream output = new MemoryStream();
workbook.Write(output);
return(File(output.ToArray(), "application/vnd.ms-excel", "UpassLog.xlsx"));
}
public async Task <ActionResult> File(int id)
{
string filename = "";
XSSFWorkbook wb = null;
try
{
var peak = await db.Peak
.Include(p => p.Usuario)
.Include(p => p.Area)
.Include(p => p.Periodo)
.Include(p => p.UsuarioPadre)
.Include(p => p.PeakObjetivos)
.Include(p => p.PeakRevisiones)
.Include(p => p.PeakPlanesDesarrollo)
.FirstOrDefaultAsync(p => p.Id == id);
if (peak == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.NotFound));
}
else if (peak.Estado != EstadoPeak.Finished)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest, "Peak is in " + peak.Estado.ToString().Replace("_", " ")));
}
var date = DateTime.Now;
string baseFilename = AppDomain.CurrentDomain.BaseDirectory + "/App_Data/Peak_Base.xlsx";
filename = $"{AppDomain.CurrentDomain.BaseDirectory}/UploadFolder/Peak_{peak.Id}_{date.Year}{date.Month}{date.Day}{date.Hour}{date.Minute}{date.Second}{date.Millisecond}.xlsx";
System.IO.File.Copy(baseFilename, filename, true);
wb = new XSSFWorkbook(filename);
var sheet = wb.GetSheet("PEAK");
int rowNo = 0;
string nl = Environment.NewLine;
sheet.GetRow(rowNo++).Cells[2].SetCellValue(peak.Usuario.UsuarioNombre);
sheet.GetRow(rowNo++).Cells[2].SetCellValue(peak.Cargo);
var row = sheet.GetRow(rowNo++);
row.Cells[2].SetCellValue(peak.Area.Nombre);
row.Cells[4].SetCellValue($"From: {((Months)peak.Periodo.FechaIni.Month).ToString()} {peak.Periodo.FechaIni.Day}, {peak.Periodo.FechaIni.Year} through {((Months)peak.Periodo.FechaFin.Month).ToString()} {peak.Periodo.FechaFin.Day}, {peak.Periodo.FechaFin.Year}");
if (peak.UsuarioPadre != null)
{
sheet.GetRow(rowNo++).Cells[2].SetCellValue(peak.UsuarioPadre.UsuarioNombre);
}
rowNo = 8;
var rowBase = sheet.GetRow(rowNo++);
sheet.ShiftRows(rowNo, sheet.LastRowNum, peak.PeakObjetivos.Count, true, false);
var setRow = new Action <int, object>((index, value) =>
{
var cell = row.CreateCell(index, CellType.Blank);
var style = wb.CreateCellStyle();
style.CloneStyleFrom(rowBase.Cells[index].CellStyle);
cell.CellStyle = style;
if (value != null)
{
string typeName = value.GetType().Name;
if (typeName == "Int16")
{
cell.SetCellValue((short)value);
}
else if (typeName == "Int32")
{
cell.SetCellValue((int)value);
}
else if (typeName == "Single")
{
cell.SetCellValue((float)value);
}
else if (typeName == "XSSFRichTextString")
{
cell.SetCellValue((IRichTextString)value);
}
else
{
cell.SetCellValue(value.ToString());
}
}
});
foreach (var po in peak.PeakObjetivos)
{
row = sheet.CreateRow(rowNo);
setRow(0, po.Numero);
setRow(1, po.Peso / 100f);
setRow(2, po.Objetivo);
setRow(3, null);
setRow(4, po.FechaMeta.ToShortDateString());
setRow(5, po.MedidoPor);
setRow(6, po.ResultadosActuales);
setRow(7, po.Comentarios);
setRow(8, null);
setRow(9, po.Completado / 100f);
setRow(10, po.Calificacion);
setRow(11, po.Factor);
setRow(12, po.ComentariosJefe);
setRow(13, null);
sheet.AddMergedRegion(new CellRangeAddress(rowNo, rowNo, 2, 3));
sheet.AddMergedRegion(new CellRangeAddress(rowNo, rowNo, 7, 8));
rowNo++;
}
rowBase.ZeroHeight = true;
row = sheet.GetRow(rowNo);
row.Cells[1].SetCellFormula($"SUM(B10:B{9 + peak.PeakObjetivos.Count})");
row.Cells[11].SetCellFormula($"SUM(L10:L{9 + peak.PeakObjetivos.Count})");
rowNo += 3;
row = sheet.GetRow(rowNo);
row.Cells[0].SetCellValue(peak.JustificacionFactorAjuste);
row.Cells[11].SetCellValue(peak.FactorAjuste);
row = sheet.GetRow(++rowNo);
row.Cells[11].SetCellValue(peak.FactorAjuste);
string comentariosRevision = "", comentariosRevisionJefe = "";
foreach (var pr in peak.PeakRevisiones)
{
comentariosRevision += pr.Comentarios + $"{nl} ---------------- {nl}";
comentariosRevisionJefe += pr.ComentariosJefe + $"{nl} ---------------- {nl}";
}
rowNo += 8;
row = sheet.GetRow(rowNo);
row.Cells[0].SetCellValue(comentariosRevision);
row.Cells[8].SetCellValue(comentariosRevisionJefe);
var peakCoreValues = await db.PeakCoreValue
.Include(pcv => pcv.CoreValue)
.Where(pcv => pcv.PeakId == id)
.OrderBy(pcv => pcv.CoreValue.Orden)
.ToListAsync();
rowNo += 8;
rowBase = sheet.GetRow(rowNo++);
sheet.ShiftRows(rowNo, sheet.LastRowNum, peakCoreValues.Count, true, false);
foreach (var pcv in peakCoreValues)
{
row = sheet.CreateRow(rowNo);
var font = new XSSFFont()
{
FontName = HSSFFont.FONT_ARIAL,
FontHeightInPoints = 12,
};
var rt = new XSSFRichTextString();
font.IsBold = true;
rt.Append($"{pcv.CoreValue.Nombre} /", font);
font.Color = IndexedColors.LightBlue.Index;
font.IsItalic = true;
rt.Append($"{pcv.CoreValue.Competencia}{nl}{nl}", font);
font.Color = IndexedColors.Black.Index;
font.IsBold = false;
rt.Append($"{pcv.CoreValue.Descripcion}", font);
setRow(0, rt);
setRow(1, null);
setRow(2, null);
setRow(3, null);
setRow(4, null);
setRow(5, null);
setRow(6, null);
setRow(7, null);
setRow(8, null);
setRow(9, pcv.Autoevaluacion.ToString().Replace("_", " "));
setRow(10, null);
setRow(11, null);
setRow(12, pcv.Evaluacion.ToString().Replace("_", " "));
setRow(13, null);
sheet.AddMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 8));
sheet.AddMergedRegion(new CellRangeAddress(rowNo, rowNo, 9, 11));
sheet.AddMergedRegion(new CellRangeAddress(rowNo, rowNo, 12, 13));
row.HeightInPoints = 70;
rowNo++;
}
rowBase.ZeroHeight = true;
rowNo += 2;
row = sheet.GetRow(rowNo);
row.Cells[0].SetCellValue(peak.ComentariosCompetencias);
rowNo += 5;
row = sheet.GetRow(rowNo);
row.Cells[0].SetCellValue(peak.ResumenContribuciones);
row.Cells[8].SetCellValue(peak.ResumenContribucionesJefe);
rowNo += 3;
row = sheet.GetRow(rowNo);
row.Cells[0].SetCellValue(peak.Fortalezas);
row.Cells[8].SetCellValue(peak.FortalezasJefe);
rowNo += 3;
row = sheet.GetRow(rowNo);
row.Cells[0].SetCellValue(peak.ObjetivosFuturo);
row.Cells[8].SetCellValue(peak.ObjetivosFuturoJefe);
rowNo += 3;
row = sheet.GetRow(rowNo);
row.Cells[0].SetCellValue(peak.RendimientoGeneral);
if (peak.PeakPlanesDesarrollo.Count > 0)
{
rowNo += 6;
rowBase = sheet.GetRow(rowNo++);
sheet.ShiftRows(rowNo, sheet.LastRowNum, peak.PeakPlanesDesarrollo.Count, true, false);
foreach (var ppd in peak.PeakPlanesDesarrollo)
{
row = sheet.CreateRow(rowNo);
setRow(0, ppd.Area);
setRow(1, null);
setRow(2, null);
setRow(3, ppd.Plan);
setRow(4, null);
setRow(5, null);
setRow(6, null);
setRow(7, null);
setRow(8, null);
setRow(9, ppd.FechaMeta.ToShortDateString());
setRow(10, ppd.ResultadoDeseado);
setRow(11, null);
setRow(12, null);
setRow(13, null);
sheet.AddMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 2));
sheet.AddMergedRegion(new CellRangeAddress(rowNo, rowNo, 3, 8));
sheet.AddMergedRegion(new CellRangeAddress(rowNo, rowNo, 10, 13));
rowNo++;
}
rowBase.ZeroHeight = true;
}
sheet.ForceFormulaRecalculation = true;
sheet = wb.GetSheet("Competency Assessment");
rowNo = 2;
rowBase = sheet.GetRow(rowNo++);
foreach (var pcv in peakCoreValues)
{
row = sheet.CreateRow(rowNo);
var font = new XSSFFont()
{
FontName = HSSFFont.FONT_ARIAL,
FontHeightInPoints = 10,
IsItalic = true,
};
var rt = new XSSFRichTextString();
font.IsBold = true;
rt.Append($"{nl}{pcv.CoreValue.Nombre}{nl}{nl}", font);
font.IsBold = false;
rt.Append($"{pcv.CoreValue.Descripcion}{nl}", font);
setRow(0, rt);
rt = new XSSFRichTextString();
font.IsBold = true;
rt.Append($"{nl}{pcv.CoreValue.Competencia}{nl}{nl}", font);
font.IsBold = false;
rt.Append($"{pcv.CoreValue.CompetenciaDescripcion}{nl}", font);
setRow(1, rt);
setRow(2, pcv.CoreValue.HabilidadAlta);
setRow(3, pcv.CoreValue.HabilidadMedia);
setRow(4, pcv.CoreValue.HabilidadBaja);
rowNo++;
}
rowBase.ZeroHeight = true;
var ms = new MemoryStream();
wb.Write(ms);
return(File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"Peak_{peak.Usuario.UsuarioId}.xlsx"));
}
catch (Exception ex)
{
return(new HttpStatusCodeResult(HttpStatusCode.InternalServerError, ex.Message));
}
finally
{
if (wb != null)
{
wb.Close();
}
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}
}
public void TestXSSFTextParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/ "Arial");
rt.ApplyFont(font);
shape.SetText(/*setter*/ rt);
Assert.IsNotNull(shape.GetCTShape());
Assert.IsNotNull(shape.GetEnumerator());
Assert.IsNotNull(XSSFSimpleShape.GetPrototype());
foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber)))
{
shape.TextParagraphs[(0)].SetBullet(nr);
Assert.IsNotNull(shape.Text);
}
shape.TextParagraphs[(0)].IsBullet = (false);
Assert.IsNotNull(shape.Text);
shape.SetText("testtext");
Assert.AreEqual("testtext", shape.Text);
shape.SetText(new XSSFRichTextString());
//Assert.AreEqual("null", shape.Text);
Assert.AreEqual(String.Empty, shape.Text);
shape.AddNewTextParagraph();
shape.AddNewTextParagraph("test-other-text");
shape.AddNewTextParagraph(new XSSFRichTextString("rtstring"));
shape.AddNewTextParagraph(new XSSFRichTextString());
//Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text);
Assert.AreEqual("test-other-text\nrtstring\n", shape.Text);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.CLIP);
Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.OVERFLOW);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.CLIP);
Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.OVERFLOW);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.Bottom;
Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.Top;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.STACKED);
Assert.AreEqual(TextDirection.STACKED, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.HORIZONTAL);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ 12.32);
Assert.AreEqual(12.32, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ 12.31);
Assert.AreEqual(12.31, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ 13.31);
Assert.AreEqual(13.31, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ 23.31);
Assert.AreEqual(23.31, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
Assert.IsTrue(shape.WordWrap);
shape.WordWrap = (/*setter*/ false);
Assert.IsFalse(shape.WordWrap);
shape.WordWrap = (/*setter*/ true);
Assert.IsTrue(shape.WordWrap);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.NORMAL);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.SHAPE);
Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.NONE);
Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit);
Assert.AreEqual(5, shape.ShapeType);
shape.ShapeType = (/*setter*/ 23);
Assert.AreEqual(23, shape.ShapeType);
// TODO: should this be supported?
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
Assert.IsNotNull(shape.GetShapeProperties());
}
finally
{
wb.Close();
}
}
static void Main(string[] args)
{
XSSFWorkbook workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("测试");
sheet.SetColumnWidth(0, 10 * 256); //设置宽度，超过255会抛异常
sheet.SetColumnWidth(1, 20 * 256); //设置宽度，超过255会抛异常
sheet.SetColumnWidth(2, 30 * 256); //设置宽度，超过255会抛异常
//标题单元格字体
var headerFont = (XSSFFont)workbook.CreateFont();
headerFont.Color = HSSFColor.Black.Index;
headerFont.FontHeightInPoints = 11;
headerFont.IsBold = true;
//标题单元格样式
var headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderBottom = BorderStyle.Thick;
headerStyle.SetFont(headerFont);
//创建标题行
var headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
#region 填充标题内容
var headerCol0 = headerRow.CreateCell(0);
headerCol0.SetCellType(CellType.String);
headerCol0.SetCellValue("序号");
headerCol0.CellStyle = headerStyle;
var headerCol1 = headerRow.CreateCell(1);
headerCol1.SetCellType(CellType.String);
headerCol1.SetCellValue("名称");
headerCol1.CellStyle = headerStyle;
var headerCol2 = headerRow.CreateCell(2);
headerCol2.SetCellType(CellType.String);
headerCol2.SetCellValue("图片");
headerCol2.CellStyle = headerStyle;
var headerCol3 = headerRow.CreateCell(3);
headerCol3.SetCellType(CellType.String);
headerCol3.SetCellValue("部门");
headerCol3.CellStyle = headerStyle;
#endregion
//数据单元格字体1
XSSFFont hyperLinkFont = (XSSFFont)workbook.CreateFont();
hyperLinkFont.Color = HSSFColor.Blue.Index;
hyperLinkFont.FontHeightInPoints = 11;
//数据单元格样式1
ICellStyle hyperLinkStyle = workbook.CreateCellStyle();
hyperLinkStyle.Alignment = HorizontalAlignment.Left;
hyperLinkStyle.VerticalAlignment = VerticalAlignment.Center;
hyperLinkStyle.FillForegroundColor = HSSFColor.Blue.Index;
hyperLinkStyle.BorderLeft = BorderStyle.Thin;
hyperLinkStyle.BorderRight = BorderStyle.Thin;
hyperLinkStyle.BorderTop = BorderStyle.Thin;
hyperLinkStyle.BorderBottom = BorderStyle.Thin;
hyperLinkStyle.SetFont(hyperLinkFont);
//数据单元格字体2
XSSFFont normalCellFont = (XSSFFont)workbook.CreateFont();
normalCellFont.Color = HSSFColor.Black.Index;
normalCellFont.FontHeightInPoints = 11;
//数据单元格样式2
var normalCellStyle = workbook.CreateCellStyle();
normalCellStyle.Alignment = HorizontalAlignment.Left;
normalCellStyle.VerticalAlignment = VerticalAlignment.Center;
normalCellStyle.BorderLeft = BorderStyle.Thin;
normalCellStyle.BorderRight = BorderStyle.Thin;
normalCellStyle.BorderTop = BorderStyle.Thin;
normalCellStyle.BorderBottom = BorderStyle.Thin;
normalCellStyle.SetFont(normalCellFont);
#region 填充数据内容
int rownum = 1;
while (rownum < 10)
{
var dataRow = sheet.CreateRow(rownum);
dataRow.HeightInPoints = 15;
var dataCol00 = dataRow.CreateCell(0);
dataCol00.SetCellType(CellType.Numeric);
dataCol00.SetCellValue(rownum);
dataCol00.CellStyle = normalCellStyle;
var dataCol01 = dataRow.CreateCell(1);
dataCol01.SetCellType(CellType.String);
dataCol01.SetCellValue($"这是内容-{rownum}");
dataCol01.CellStyle = normalCellStyle;
//超链接
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = $"{rownum}.png";
var dataCol02 = dataRow.CreateCell(2);
dataCol02.SetCellType(CellType.String);
dataCol02.SetCellValue("点击查看图片");
dataCol02.CellStyle = hyperLinkStyle; //设置样式
dataCol02.Hyperlink = link; //设置超链接
var dataCol03 = dataRow.CreateCell(3);
dataCol03.SetCellType(CellType.String);
dataCol03.SetCellValue(rownum > 4 ? "研发部" : "财务部");
dataCol03.CellStyle = normalCellStyle;
rownum++;
}
#endregion
//合并单元格测试
sheet.AddMergedRegion(new CellRangeAddress(1, 4, 3, 3)); //firstRow,lastRow,firstColumn,lastColumn
sheet.AddMergedRegion(new CellRangeAddress(5, 9, 3, 3)); //firstRow,lastRow,firstColumn,lastColumn
//写入文件
FileStream file = new FileStream(@"C:\my.xlsx", FileMode.OpenOrCreate);
workbook.Write(file);
file.Close();
}
private static MemoryStream ExportExcel2007(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
//格式日期
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//格式数字
XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat;
decimelStyle.DataFormat = decimelformat.GetFormat("0.00####");
// 取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
if (rowIndex == 1048576 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion 表头及样式
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.IsLocked = true;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] > 255 ? 254 : arrColWidth[column.Ordinal] + 1) * 256);
}
//sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
}
rowIndex = 2;
#endregion 列头及样式
rowIndex = 2;
}
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (drValue.Length > 0)
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
Double doubV = 0;
Double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
newCell.CellStyle = decimelStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion 填充内容
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
if (rowIndex == 0)
{
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
rowIndex = 1;
}
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream，2007格式
/// </summary>
/// <param name="dtSource">源DataTable</param>
public static MemoryStream ExportDT2007(DataTable dtSource)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
#region 右击文件 属性信息
{
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
//dsi.Company = "http://www.huobanplus.com";
//workbook.DocumentSummaryInformation = dsi;
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = "杭州火图科技"; //填加xls文件作者信息
//si.ApplicationName = "伙伴商城"; //填加xls文件创建程序信息
//si.LastAuthor = "voidarea"; //填加xls文件最后保存者信息
//si.Comments = ""; //填加xls文件作者信息
//si.Title = ""; //填加xls文件标题信息
//si.Subject = ""; //填加文件主题信息
//si.CreateDateTime = DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (arrColWidth[column.Ordinal] > 255)
{
arrColWidth[column.Ordinal] = 254;
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
#endregion
//rowIndex = 2;
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet;
//workbook.Dispose();
return(ms);
}
}
public string screenshotExportExcel(string fileName, string imgPath, string wkt = null, string serverUrl = null, string serverConnName = null, string layer = null, string layerText = null, string statistics = null, string statisticsText = null)
{
string daochu_result = "";
{
try
{
string fileNameDir = System.AppDomain.CurrentDomain.BaseDirectory;
string saveFilePath = fileNameDir + "\\resource\\download\\excel";
if (!Directory.Exists(saveFilePath))
{
Directory.CreateDirectory(saveFilePath);
}
{
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFFont fieldFont = (XSSFFont)workBook.CreateFont();
fieldFont.FontHeightInPoints = 13;
fieldFont.Boldweight = 600;
ICellStyle fieldStyle = workBook.CreateCellStyle();
fieldStyle.Alignment = HorizontalAlignment.Center;
fieldStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
fieldStyle.SetFont(fieldFont);
////下面这两行颜色不管用
fieldStyle.FillForegroundColor = IndexedColors.Blue.Index;
fieldStyle.FillBackgroundColor = IndexedColors.Red.Index;
XSSFSheet sheet = (XSSFSheet)workBook.CreateSheet("截图");
sheet.SetColumnWidth(0, 50 * 256);
if (string.IsNullOrEmpty(wkt))
{
//只保存图片
IRow _firstRow = sheet.CreateRow(0);
ICell _firstCell = _firstRow.CreateCell(0);
_firstCell.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd"));
_firstCell.CellStyle = fieldStyle;
IRow _picRow = sheet.CreateRow(1);
_picRow.Height = 70 * 256;//行高，存放地图
// 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//在rowindex=1行col=0列插入图片，图片占1行1列
setPic(workBook, patriarch, fileNameDir + "/" + imgPath, sheet, 1, 0, 1, 1);
}
else
{
//如果A4导出，则不要截图
workBook.RemoveAt(0);
}
if (!string.IsNullOrEmpty(wkt))
{
string returnstr = "";
Common common = new Common();
string wktArea = "0";
string url = serverUrl + "area?serverConnName=" + serverConnName + "&wkt=" + wkt;
returnstr = common.queryDataPost(url);
JObject jo = (JObject)JsonConvert.DeserializeObject(returnstr);
if (jo["result"].ToString() == "success")
{
wktArea = ((JArray)jo["data"])[0]["area1"].ToString();
wktArea = areaTransfer(wktArea);
}
ICellStyle dataStyle = workBook.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.Center;
dataStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
returnstr = common.queryDataPost(url);
if (!string.IsNullOrEmpty(layer) && !string.IsNullOrEmpty(layerText) && !string.IsNullOrEmpty(statistics) && !string.IsNullOrEmpty(statisticsText))
{
var layerArray = layer.Split(',');
var layerTextArray = layerText.Split(',');
var statisticsArray = statistics.Split(',');
var statisticsTextArray = statisticsText.Split(',');
if (layerArray.Length == layerTextArray.Length && statisticsArray.Length == statisticsTextArray.Length && layerTextArray.Length == statisticsArray.Length)
{
for (var j = 0; j < layerArray.Length; j++)
{
string tableName = layerArray[j];
string statisticsField = statisticsArray[j];
XSSFSheet sheet_sub = (XSSFSheet)workBook.CreateSheet(layerTextArray[j]);
IRow _frow = sheet_sub.CreateRow(0);
_frow.Height = 3 * 256;//行高
_frow.CreateCell(0).SetCellValue("序号");
_frow.CreateCell(1).SetCellValue("总面积(亩)");
_frow.CreateCell(2).SetCellValue(statisticsTextArray[j]);
_frow.CreateCell(3).SetCellValue("面积(亩)");
for (int i = 0; i < 4; i++)
{
_frow.GetCell(i).CellStyle = fieldStyle;
sheet_sub.SetColumnWidth(i, 18 * 256);
}
url = serverUrl + "statistics?serverConnName=" + serverConnName + "&tableName=" + tableName + "&wkt=" + wkt + "&clip=true&fieldName=" + Microsoft.JScript.GlobalObject.escape(statisticsField);
returnstr = common.queryDataPost(url);
if (returnstr.IndexOf("\"result\":\"error\"") > -1)
{
continue;
}
jo = (JObject)JsonConvert.DeserializeObject(returnstr);
if (jo["result"].ToString() == "success")
{
var ja = (JArray)jo["data"];
var index = 0;
foreach (JObject items in ja)
{
string _name = "", _amount = "", _cliparea = "", _calarea = "";
foreach (var item in items)
{
switch (item.Key)
{
case "name":
_name = item.Value.ToString();
break;
case "amount":
_amount = item.Value.ToString();
break;
case "cliparea":
_cliparea = item.Value.ToString();
break;
case "calarea":
_calarea = item.Value.ToString();
break;
}
}
IRow _frowData = sheet_sub.CreateRow(index + 1);
_frowData.Height = 2 * 256;//行高
_frowData.CreateCell(0).SetCellValue(index + 1);
if (index == 0)
{
_frowData.CreateCell(1).SetCellValue(wktArea);
}
else
{
_frowData.CreateCell(1).SetCellValue("");
}
_frowData.CreateCell(2).SetCellValue(_name);
string _mj = areaTransfer(_cliparea);
_frowData.CreateCell(3).SetCellValue(_mj);
for (int jj = 0; jj < 4; jj++)
{
_frowData.GetCell(jj).CellStyle = dataStyle;
}
index++;
}
}
}
}
}
}
//保存
try
{
string saveFileName = saveFilePath + "\\" + fileName + ".xlsx";
using (FileStream fs = new FileStream(saveFileName, FileMode.Create))
{
workBook.Write(fs);
workBook.Close();
daochu_result = "\"exportResult\":\"success\",\"exportMessage\":\"" + "/resource/download/excel/" + fileName + ".xlsx" + "\"";
}
}
catch (System.Exception e)
{
daochu_result = "\"exportResult\":\"error\",\"exportMessage\":\"" + e.ToString() + "\"";
workBook.Close();
}
}
}
catch (Exception e)
{
daochu_result = "\"exportResult\":\"errpr\",\"exportMessage\":\"" + e.ToString() + "\"";
}
}
return(daochu_result);
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
/// <param name="startAddress"></param>
/// <param name="dataTable"></param>
/// <param name="iWorkbook"></param>
public void FillDataFromTable(XSSFWorkbook iWorkbook, XSSFSheet sheet, string startAddress, DataTable dataTable)
{
XSSFCellStyle headerCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
CT_Color ctColor = new CT_Color();
ctColor.SetRgb(112, 173, 71);
XSSFColor xssfColor = new XSSFColor(ctColor);
headerCellStyle.SetFillBackgroundColor(xssfColor);
headerCellStyle.SetFillForegroundColor(xssfColor);
XSSFFont hssfFont = iWorkbook.CreateFont() as XSSFFont;
hssfFont.FontHeightInPoints = 10;
hssfFont.FontName = "宋体";
hssfFont.Boldweight = 700;
headerCellStyle.SetFont(hssfFont);
XSSFCellStyle contentCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
XSSFFont contentHssfFont = iWorkbook.CreateFont() as XSSFFont;
contentHssfFont.FontHeightInPoints = 10;
contentHssfFont.FontName = "宋体";
contentCellStyle.SetFont(contentHssfFont);
string rowIndexStr = string.Empty;
string cellIndexStr = string.Empty;
int cellIndex = 0;
for (int i = 0; i < startAddress.Length; i++)
{
int tempNum;
if (int.TryParse(startAddress[i].ToString(), out tempNum))
{
rowIndexStr += "" + tempNum;
}
else
{
cellIndexStr += "" + startAddress[i];
}
}
var rowIndex = Convert.ToInt32(rowIndexStr);
for (int i = cellIndexStr.Length - 1; i >= 0; i--)
{
if (i == cellIndexStr.Length - 1)
{
cellIndex += cellIndexStr[i] - 65;
}
else
{
cellIndex += (cellIndexStr[i] - 64) * 26;
}
}
cellIndex = 0;
//textBox1.Text += "\r\n 共有数据:" + _DataTable.Rows.Count;
int tempCellIndex = cellIndex;
try
{
//sheet分开包含表头
if (!ckbSheet.Checked)
{
rowIndex = sheet.LastRowNum;
if (rowIndex != 0)
{
rowIndex++;
}
}
//是否包含表头
XSSFRow excelRow;
XSSFCell excelCell;
if (ckbIsIncludeHeader.Checked && rowIndex <= 1)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (string.IsNullOrEmpty(dataColumn.ColumnName))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataColumn.ColumnName));
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
}
rowIndex++;
tempCellIndex = cellIndex;
}
//填充数据
foreach (DataRow dataRow in dataTable.Rows)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (dataRow[dataColumn] == DBNull.Value || string.IsNullOrEmpty(Convert.ToString(dataRow[dataColumn])))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = contentCellStyle;
tempCellIndex++;
continue;
}
if (dataRow[dataColumn] is decimal || dataRow[dataColumn] is int)
{
excelCell.SetCellType(CellType.Numeric);
excelCell.SetCellValue(Convert.ToDouble(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
tempCellIndex++;
}
tempCellIndex = cellIndex;
rowIndex++;
}
}
catch (Exception ex)
{
throw new Exception("行" + rowIndex + "列" + tempCellIndex + "_" + ex.Message, ex);
}
}
public string singleExportExcel(string fileName, string data)
{
string daochu_result = "";
{
try
{
string fileNameDir = System.AppDomain.CurrentDomain.BaseDirectory;
string saveFilePath = fileNameDir + "\\resource\\download\\excel";
if (!Directory.Exists(saveFilePath))
{
Directory.CreateDirectory(saveFilePath);
}
{
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFFont fieldFont = (XSSFFont)workBook.CreateFont();
fieldFont.FontHeightInPoints = 13;
fieldFont.Boldweight = 600;
ICellStyle fieldStyle = workBook.CreateCellStyle();
fieldStyle.Alignment = HorizontalAlignment.Center;
fieldStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
fieldStyle.SetFont(fieldFont);
////下面这两行颜色不管用
fieldStyle.FillForegroundColor = IndexedColors.Blue.Index;
fieldStyle.FillBackgroundColor = IndexedColors.Red.Index;
ICellStyle dataStyle = workBook.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.Center;
dataStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
{
string returnstr = "";
Common common = new Common();
returnstr = data;
{
var jo = (JObject)JsonConvert.DeserializeObject(returnstr);
{
//填充基础信息
XSSFSheet sheet_sub = (XSSFSheet)workBook.CreateSheet("基础信息");
IRow _frow = sheet_sub.CreateRow(0);
_frow.Height = 3 * 256;//行高
_frow.CreateCell(0).SetCellValue("序号");
_frow.CreateCell(1).SetCellValue("类型");
_frow.CreateCell(2).SetCellValue("内容");
for (var i = 0; i < 3; i++)
{
_frow.GetCell(i).CellStyle = fieldStyle;
if (i == 2)
{
sheet_sub.SetColumnWidth(i, 23 * 256);
}
else
{
sheet_sub.SetColumnWidth(i, 18 * 256);
}
}
JObject _properties = (JObject)jo["基础信息"];
var index = 0;
foreach (var _item in _properties)
{
IRow _frowData = sheet_sub.CreateRow(index + 1);
_frowData.Height = 2 * 256;//行高
_frowData.CreateCell(0).SetCellValue(index + 1);
var _key = _item.Key;
var _value = _item.Value;
_frowData.CreateCell(1).SetCellValue(_key.ToString());
_frowData.CreateCell(2).SetCellValue(_value.ToString());
for (var j = 0; j < 3; j++)
{
_frowData.GetCell(j).CellStyle = dataStyle;
}
index++;
}
}
{
var ja = (JArray)jo["地块信息"];
XSSFSheet sheet_sub = (XSSFSheet)workBook.CreateSheet("地块信息");
IRow _frow = sheet_sub.CreateRow(0);
_frow.Height = 3 * 256;//行高
_frow.CreateCell(0).SetCellValue("序号");
_frow.CreateCell(1).SetCellValue("类型");
_frow.CreateCell(2).SetCellValue("数量");
_frow.CreateCell(3).SetCellValue("面积(亩)");
for (var i = 0; i < 4; i++)
{
_frow.GetCell(i).CellStyle = fieldStyle;
sheet_sub.SetColumnWidth(i, 18 * 256);
}
var index = 0;
foreach (JObject items in ja)
{
IRow _frowData = sheet_sub.CreateRow(index + 1);
_frowData.Height = 2 * 256;//行高
_frowData.CreateCell(0).SetCellValue(index + 1);
_frowData.GetCell(0).CellStyle = dataStyle;
foreach (var _item in items)
{
var _key = _item.Key;
var _value = _item.Value;
var _colIndex = -1;
switch (_key)
{
case "name":
_colIndex = 1;
break;
case "amount":
_colIndex = 2;
break;
case "area":
_colIndex = 3;
break;
}
_frowData.CreateCell(_colIndex).SetCellValue(_value.ToString());
_frowData.GetCell(_colIndex).CellStyle = dataStyle;
}
index++;
}
}
}
}
//保存
try
{
string saveFileName = saveFilePath + "\\" + fileName + ".xlsx";
using (FileStream fs = new FileStream(saveFileName, FileMode.Create))
{
workBook.Write(fs);
workBook.Close();
daochu_result = "\"exportResult\":\"success\",\"exportMessage\":\"" + "/resource/download/excel/" + fileName + ".xlsx" + "\"";
}
}
catch (System.Exception e)
{
daochu_result = "\"exportResult\":\"error\",\"exportMessage\":\"" + e.ToString() + "\"";
workBook.Close();
}
}
}
catch (Exception e)
{
daochu_result = "\"exportResult\":\"errpr\",\"exportMessage\":\"" + e.ToString() + "\"";
}
}
return(daochu_result);
}
public void XSSFTextParagraph_()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/ "Arial");
rt.ApplyFont(font);
shape.SetText(rt);
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
XSSFTextParagraph text = paras[(0)];
Assert.AreEqual("Test String", text.Text);
Assert.IsFalse(text.IsBullet);
Assert.IsNotNull(text.GetXmlObject());
Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
Assert.IsNotNull(text.GetEnumerator());
Assert.IsNotNull(text.AddLineBreak());
Assert.IsNotNull(text.TextRuns);
Assert.AreEqual(2, text.TextRuns.Count);
text.AddNewTextRun();
Assert.AreEqual(3, text.TextRuns.Count);
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = (/*setter*/ TextAlign.CENTER);
Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
text.TextAlign = (/*setter*/ TextAlign.RIGHT);
Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextFontAlign = (/*setter*/ TextFontAlign.BASELINE);
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = (/*setter*/ TextFontAlign.BOTTOM);
Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
Assert.IsNull(text.BulletFont);
text.BulletFont = (/*setter*/ "Arial");
Assert.AreEqual("Arial", text.BulletFont);
Assert.IsNull(text.BulletCharacter);
text.BulletCharacter = (/*setter*/ ".");
Assert.AreEqual(".", text.BulletCharacter);
//Assert.IsNull(text.BulletFontColor);
Assert.AreEqual(Color.Empty, text.BulletFontColor);
text.BulletFontColor = (/*setter*/ color);
Assert.AreEqual(color, text.BulletFontColor);
Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/ 2.0);
Assert.AreEqual(2.0, text.Indent, 0.01);
text.Indent = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ 3.0);
Assert.AreEqual(3.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ 4.5);
Assert.AreEqual(4.5, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);
Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
text.AddTabStop(3.14);
Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);
Assert.AreEqual(100.0, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/ 3.15);
Assert.AreEqual(3.15, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/ -2.13);
Assert.AreEqual(-2.13, text.LineSpacing, 0.01);
Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/ 3.17);
Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/ -4.7);
Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);
Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/ 6.17);
Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/ -8.17);
Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);
Assert.AreEqual(0, text.Level);
text.Level = (/*setter*/ 1);
Assert.AreEqual(1, text.Level);
text.Level = (/*setter*/ 4);
Assert.AreEqual(4, text.Level);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (false);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (true);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
Assert.AreEqual(0, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
//Assert.AreEqual(0, text.BulletAutoNumberStart);
//This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1;
Assert.AreEqual(1, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
Assert.AreEqual(10, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);
Assert.IsNotNull(text.ToString());
new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
}
finally
{
wb.Close();
}
}
public void TestThemesTableColors()
{
// Load our two test workbooks
XSSFWorkbook simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple);
XSSFWorkbook complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex);
// Save and re-load them, to check for stability across that
XSSFWorkbook simpleRS = XSSFTestDataSamples.WriteOutAndReadBack(simple) as XSSFWorkbook;
XSSFWorkbook complexRS = XSSFTestDataSamples.WriteOutAndReadBack(complex) as XSSFWorkbook;
// Fetch fresh copies to test with
simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple);
complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex);
// Files and descriptions
Dictionary <String, XSSFWorkbook> workbooks = new Dictionary <String, XSSFWorkbook>();
workbooks.Add(testFileSimple, simple);
workbooks.Add("Re-Saved_" + testFileSimple, simpleRS);
workbooks.Add(testFileComplex, complex);
workbooks.Add("Re-Saved_" + testFileComplex, complexRS);
// Sanity check
//Assert.AreEqual(rgbExpected.Length, rgbExpected.Length);
// For offline testing
bool createFiles = false;
// Check each workbook in turn, and verify that the colours
// for the theme-applied cells in Column A are correct
foreach (String whatWorkbook in workbooks.Keys)
{
XSSFWorkbook workbook = workbooks[whatWorkbook];
XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet;
int startRN = 0;
if (whatWorkbook.EndsWith(testFileComplex))
{
startRN++;
}
for (int rn = startRN; rn < rgbExpected.Length + startRN; rn++)
{
XSSFRow row = sheet.GetRow(rn) as XSSFRow;
Assert.IsNotNull(row, "Missing row " + rn + " in " + whatWorkbook);
String ref1 = (new CellReference(rn, 0)).FormatAsString();
XSSFCell cell = row.GetCell(0) as XSSFCell;
Assert.IsNotNull(cell,
"Missing cell " + ref1 + " in " + whatWorkbook);
int expectedThemeIdx = rn - startRN;
ThemeElement themeElem = ThemeElement.ById(expectedThemeIdx);
Assert.AreEqual(themeElem.name.ToLower(), cell.StringCellValue,
"Wrong theme at " + ref1 + " in " + whatWorkbook);
// Fonts are theme-based in their colours
XSSFFont font = (cell.CellStyle as XSSFCellStyle).GetFont();
CT_Color ctColor = font.GetCTFont().GetColorArray(0);
Assert.IsNotNull(ctColor);
Assert.AreEqual(true, ctColor.IsSetTheme());
Assert.AreEqual(themeElem.idx, ctColor.theme);
// Get the colour, via the theme
XSSFColor color = font.GetXSSFColor();
// Theme colours aren't tinted
Assert.AreEqual(color.HasTint, false);
// Check the RGB part (no tint)
Assert.AreEqual(rgbExpected[expectedThemeIdx], HexDump.EncodeHexString(color.RGB),
"Wrong theme colour " + themeElem.name + " on " + whatWorkbook);
long themeIdx = font.GetCTFont().GetColorArray(0).theme;
Assert.AreEqual(expectedThemeIdx, themeIdx,
"Wrong theme index " + expectedThemeIdx + " on " + whatWorkbook
);
if (createFiles)
{
XSSFCellStyle cs = row.Sheet.Workbook.CreateCellStyle() as XSSFCellStyle;
cs.SetFillForegroundColor(color);
cs.FillPattern = FillPatternType.SolidForeground;
row.CreateCell(1).CellStyle = (cs);
}
}
if (createFiles)
{
FileStream fos = new FileStream("Generated_" + whatWorkbook, FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fos);
fos.Close();
}
}
}
/// <summary>
///传入ds直接生成excel在服务器目录上
/// </summary>
/// <param name="ds"></param>
/// <param name="strPath"></param>
/// <param name="strFileName"></param>
/// <returns></returns>
public static XSSFWorkbook ExportXlsxByDataSet(DataSet ds, string ReportHeader = "")
{
//NPOI
XSSFWorkbook hssfworkbook2 = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)hssfworkbook2.CreateSheet("sheet1");
//定义字体 font 设置字体类型和大小
XSSFFont font = (XSSFFont)hssfworkbook2.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
//定义单元格格式；单元格格式style1 为font的格式
XSSFCellStyle style1 = (XSSFCellStyle)hssfworkbook2.CreateCellStyle();
style1.SetFont(font);
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
XSSFCellStyle style2 = (XSSFCellStyle)hssfworkbook2.CreateCellStyle();
style2.SetFont(font);
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//style2.WrapText = true;
//设置大标题行
int RowCount = 0;
int arrFlag = 0;
string TileName1 = "";
string TileName2 = "";
string s = ReportHeader;
string[] sArray = s.Split('|');
if (ReportHeader != "")
{
foreach (string i in sArray)
{
string str1 = i.ToString();
string[] subArray = str1.Split('@');
foreach (string k in subArray)
{
Console.WriteLine(k.ToString());
if (arrFlag == 0)
{
TileName1 = k.ToString();
}
else
{
TileName2 = k.ToString();
}
arrFlag = arrFlag + 1;
}
XSSFRow row0 = (XSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列
row0.CreateCell(0).SetCellValue(TileName1);
row0.CreateCell(1).SetCellValue(TileName2);
RowCount = RowCount + 1;
arrFlag = 0;
}
}
//设置全局列宽和行高
sheet.DefaultColumnWidth = 14; //全局列宽
sheet.DefaultRowHeightInPoints = 15; //全局行高
//设置标题行数据
int a = 0;
string mColumnName = "";
XSSFRow row1 = (XSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列
for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
{
mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString();
row1.CreateCell(a).SetCellValue(mColumnName);
row1.Cells[a].CellStyle = style2;
a++;
}
//填写ds数据进excel
//数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)//写6行数据
{
XSSFRow row2 = (XSSFRow)sheet.CreateRow(i + RowCount + 1);
int b = 0;
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
string DgvValue = "";
DgvValue = ds.Tables[0].Rows[i][j].ToString();;
row2.CreateCell(b).SetCellValue(DgvValue);
b++;
}
}
return(hssfworkbook2);
}
public void OutExcelData()
{
string ExportFileName = "EmpData.xlsx"; string ExportFileTitle = "Data"; Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
XSSFWorkbook NpoiWB = new XSSFWorkbook();
XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat();
xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0"));
XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true;
cellStyleFontColor.SetFont(font1);
/// 進行產生Excel檔案流程
ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle);
List <string> listColumn = edModel.listEmployeeColumn;
/// 建立標題列
IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40;
for (int i = 0; i < listColumn.Count; i++)
{
ICell xCellT = xRowT.CreateCell(i); xCellT.SetCellValue(listColumn[i]);
}
/// 讀取資料庫資料
List <listEmployeeDetail> ListEmpData = new List <listEmployeeDetail>();
ListEmpData = edModel.ReListEmployeeDetail();
if (ListEmpData != null && ListEmpData.Count > 0)
{
for (int i = 0; i < ListEmpData.Count; i++)
{
listEmployeeDetail item = ListEmpData[i];
List <string> list = new List <string>();
list.Add(item.lEmpIndex.ToString());
list.Add(item.lEmpName.ToString());
list.Add(item.lEmpSex.ToString());
list.Add(item.lEmpEmail.ToString());
list.Add(item.lEmpAddress.ToString());
list.Add(item.lEmpMobile.ToString());
list.Add(item.lEmpPhone.ToString());
list.Add(item.lEmpNotation.ToString());
list.Add(item.lEmpRemark.ToString());
list.Add(item.lEmpStatus.ToString());
list.Add(item.lEmpJoinDate.ToString());
list.Add(item.lEmpLeaveDate.ToString());
IRow xRowD = xSheet.CreateRow(i + 1); xRowD.HeightInPoints = 30;
for (int b = 0; b < list.Count; b++)
{
ICell xCellData = xRowD.CreateCell(b); xCellData.SetCellValue(list[b]);
}
}
}
MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS);
Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + "");
Response.BinaryWrite(MS.ToArray());
// ----------------------------------------------------------------------------------------------
// 釋放記憶體參數
NpoiWB = null; MS.Close(); MS.Dispose();
Response.Flush(); Response.End();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static void ExportDT(DataTable dtSource, string strHeaderText, string sheetName, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet;
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
else
{
sheet = workbook.CreateSheet(sheetName) as XSSFSheet;
}
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.UTF8.GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.UTF8.GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
public void TestAddNewTextParagraphWithRTS()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet;
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
XSSFRichTextString rt = new XSSFRichTextString("Test Rich Text String");
XSSFFont font = wb1.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromRgb(0, 255, 255)));
font.FontName = ("Arial");
rt.ApplyFont(font);
XSSFFont midfont = wb1.CreateFont() as XSSFFont;
midfont.SetColor(new XSSFColor(Color.FromRgb(0, 255, 0)));
rt.ApplyFont(5, 14, midfont); // Set the text "Rich Text" to be green and the default font
XSSFTextParagraph para = shape.AddNewTextParagraph(rt);
// Save and re-load it
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
// Check
drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(1, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFSimpleShape);
XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0];
List <XSSFTextParagraph> paras = sshape.TextParagraphs;
Assert.AreEqual(2, paras.Count); // this should be 2 as XSSFSimpleShape Creates a default paragraph (no text), and then we add a string to that.
List <XSSFTextRun> runs = para.TextRuns;
Assert.AreEqual(3, runs.Count);
// first run properties
Assert.AreEqual("Test ", runs[0].Text);
Assert.AreEqual("Arial", runs[0].FontFamily);
var clr = runs[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
// second run properties
Assert.AreEqual("Rich Text", runs[1].Text);
Assert.AreEqual(XSSFFont.DEFAULT_FONT_NAME, runs[1].FontFamily);
clr = runs[1].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 0 },
new int[] { clr.R, clr.G, clr.B }));
// third run properties
Assert.AreEqual(" String", runs[2].Text);
Assert.AreEqual("Arial", runs[2].FontFamily);
clr = runs[2].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb2);
wb2.Close();
}
public void FillReg17SpreadSheet(Dictionary <string, Reg17Record> records)
{
xssfwb = new XSSFWorkbook();
XSSFFont myFont = (XSSFFont)xssfwb.CreateFont();
myFont.FontHeightInPoints = 12;
myFont.Boldweight = (short)FontBoldWeight.Bold;
//myFont.FontName = "Tahoma";
XSSFFont myFont2 = (XSSFFont)xssfwb.CreateFont();
myFont2.FontHeightInPoints = 18;
myFont2.Boldweight = (short)FontBoldWeight.Bold;
//myFont2.FontName = "Tahoma";
IFont boldFont = xssfwb.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.Bold;
var color = new XSSFColor(new byte[] { 196, 215, 155 });
XSSFCellStyle borderedCellStyle = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle.SetFont(myFont);
borderedCellStyle.VerticalAlignment = VerticalAlignment.Center;
XSSFCellStyle borderedCellStyle2 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle2.SetFont(myFont2);
borderedCellStyle2.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle2.Alignment = HorizontalAlignment.Center;
borderedCellStyle2.SetFillForegroundColor(color);
borderedCellStyle2.SetFillBackgroundColor(color);
borderedCellStyle2.FillBackgroundXSSFColor = color;
borderedCellStyle2.FillForegroundXSSFColor = color;
borderedCellStyle2.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle borderedCellStyle3 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle3.SetFont(myFont);
borderedCellStyle3.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle3.Alignment = HorizontalAlignment.Center;
borderedCellStyle3.SetFillForegroundColor(color);
borderedCellStyle3.SetFillBackgroundColor(color);
borderedCellStyle3.FillBackgroundXSSFColor = color;
borderedCellStyle3.FillForegroundXSSFColor = color;
borderedCellStyle3.FillPattern = FillPattern.SolidForeground;
borderedCellStyle3.WrapText = true;
ISheet Sheet = xssfwb.CreateSheet("Report");
Sheet.SetColumnWidth(0, 22 * 256);
Sheet.SetColumnWidth(1, 17 * 256);
Sheet.SetColumnWidth(2, 25 * 256);
Sheet.SetColumnWidth(3, 15 * 256);
Sheet.SetColumnWidth(4, 96 * 256);
Sheet.SetColumnWidth(5, 35 * 256);
//Creat The Headers of the excel
IRow row1 = Sheet.CreateRow(0);
//styling
ICellStyle boldStyle = xssfwb.CreateCellStyle();
boldStyle.SetFont(boldFont);
//Create The Actual Cells
row1.CreateCell(0).SetCellValue("Sistema PCI DSS");
row1.GetCell(0).CellStyle = boldStyle;
IRow row2 = Sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("REG-17");
row2.GetCell(0).CellStyle = boldStyle;
IRow row3 = Sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("Informe de Aplicación de Parches");
row3.GetCell(0).CellStyle = borderedCellStyle2;
var cra = new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 5);
Sheet.AddMergedRegion(cra);
row2.CreateCell(0).SetCellValue("REG-17");
row2.GetCell(0).CellStyle = boldStyle;
IRow row4 = Sheet.CreateRow(4);
row4.CreateCell(0).SetCellValue("Servidores");
row4.GetCell(0).CellStyle = borderedCellStyle;
row4.CreateCell(1).SetCellValue("SQLPCI");
row4.GetCell(1).CellStyle = boldStyle;
row4.CreateCell(2).SetCellValue("Fecha de Aplicación");
row4.GetCell(2).CellStyle = borderedCellStyle;
row4.CreateCell(3).SetCellValue(DateTime.Now.ToString());
row4.GetCell(3).CellStyle = borderedCellStyle;
IRow row5 = Sheet.CreateRow(5);
row5.CreateCell(1).SetCellValue("SRVFILE");
row5.GetCell(1).CellStyle = boldStyle;
IRow row6 = Sheet.CreateRow(6);
row6.CreateCell(1).SetCellValue("Site-Transfer");
row6.GetCell(1).CellStyle = boldStyle;
IRow row7 = Sheet.CreateRow(7);
row7.CreateCell(1).SetCellValue("WEBPCI");
row7.GetCell(1).CellStyle = boldStyle;
IRow row8 = Sheet.CreateRow(8);
row8.CreateCell(1).SetCellValue("Proxy Inverso");
row8.GetCell(1).CellStyle = boldStyle;
IRow row9 = Sheet.CreateRow(9);
row9.CreateCell(1).SetCellValue("Proxy Servicios");
row9.GetCell(1).CellStyle = boldStyle;
IRow row10 = Sheet.CreateRow(10);
row10.CreateCell(1).SetCellValue("Proxy Web");
row10.GetCell(1).CellStyle = boldStyle;
IRow row11 = Sheet.CreateRow(11);
row11.CreateCell(1).SetCellValue("Parches");
row11.GetCell(1).CellStyle = boldStyle;
IRow row12 = Sheet.CreateRow(12);
row12.CreateCell(1).SetCellValue("ADMPCI");
row12.GetCell(1).CellStyle = boldStyle;
IRow row13 = Sheet.CreateRow(13);
row13.CreateCell(1).SetCellValue("File Gateway");
row13.GetCell(1).CellStyle = boldStyle;
IRow row14 = Sheet.CreateRow(14);
row14.CreateCell(1).SetCellValue("Volume Gateway");
row14.GetCell(1).CellStyle = boldStyle;
IRow row15 = Sheet.CreateRow(15);
row15.CreateCell(1).SetCellValue("RD Gateway");
row15.GetCell(1).CellStyle = boldStyle;
IRow row16 = Sheet.CreateRow(16);
row16.CreateCell(1).SetCellValue("Data Analysis");
row16.GetCell(1).CellStyle = boldStyle;
IRow row18 = Sheet.CreateRow(18);
row18.CreateCell(0).SetCellValue("Código Parche");
row18.GetCell(0).CellStyle = borderedCellStyle3;
row18.CreateCell(1).SetCellValue("Fecha Publicación");
row18.GetCell(1).CellStyle = borderedCellStyle3;
row18.CreateCell(2).SetCellValue("Producto");
row18.GetCell(2).CellStyle = borderedCellStyle3;
row18.CreateCell(3).SetCellValue("Clasificación");
row18.GetCell(3).CellStyle = borderedCellStyle3;
row18.CreateCell(4).SetCellValue("Mejoras y Correcciones");
row18.GetCell(4).CellStyle = borderedCellStyle3;
row18.CreateCell(5).SetCellValue("Opinión Impacto del Parche");
row18.GetCell(5).CellStyle = borderedCellStyle3;
XSSFFont myFont4 = (XSSFFont)xssfwb.CreateFont();
myFont4.FontHeightInPoints = 10;
//myFont4.Boldweight = (short)FontBoldWeight.Bold;
//myFont.FontName = "Tahoma";
XSSFFont myFont5 = (XSSFFont)xssfwb.CreateFont();
myFont5.FontHeightInPoints = 8.5;
//myFont5.Boldweight = (short)FontBoldWeight.Bold;
//myFont2.FontName = "Tahoma";
boldFont.Boldweight = (short)FontBoldWeight.Bold;
XSSFCellStyle borderedCellStyle4 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle4.SetFont(myFont4);
borderedCellStyle4.VerticalAlignment = VerticalAlignment.Center;
borderedCellStyle4.Alignment = HorizontalAlignment.Center;
XSSFCellStyle borderedCellStyle5 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle5.SetFont(myFont5);
borderedCellStyle5.VerticalAlignment = VerticalAlignment.Center;
//borderedCellStyle5.Alignment = HorizontalAlignment.Center;
borderedCellStyle5.WrapText = true;
XSSFCellStyle borderedCellStyle6 = (XSSFCellStyle)xssfwb.CreateCellStyle();
borderedCellStyle6.SetFont(myFont4);
borderedCellStyle6.VerticalAlignment = VerticalAlignment.Center;
//borderedCellStyle6.Alignment = HorizontalAlignment.Center;
borderedCellStyle6.WrapText = true;
int i = 19;
//List<Reg17Record> records = new List<Reg17Record>();
foreach (KeyValuePair <string, Reg17Record> entry in records)
{
// do something with entry.Value or entry.Key
Reg17Record record = (Reg17Record)entry.Value;
IRow rowi = Sheet.CreateRow(i);
rowi.CreateCell(0).SetCellValue(record.PatchCode);
rowi.GetCell(0).CellStyle = borderedCellStyle4;
rowi.CreateCell(1).SetCellValue(record.PublicationDate);
rowi.GetCell(1).CellStyle = borderedCellStyle4;
rowi.CreateCell(2).SetCellValue(record.Product);
rowi.GetCell(2).CellStyle = borderedCellStyle4;
rowi.CreateCell(3).SetCellValue(record.Classification);
rowi.GetCell(3).CellStyle = borderedCellStyle4;
rowi.CreateCell(4).SetCellValue(record.EnhancementsAndCorrections);
rowi.GetCell(4).CellStyle = borderedCellStyle5;
rowi.CreateCell(5).SetCellValue(record.ImpactOpinion);
rowi.GetCell(5).CellStyle = borderedCellStyle6;
i++;
}
}
/**
* Read this shared styles table from an XML file.
*
* @param is The input stream Containing the XML document.
* @throws IOException if an error occurs while Reading.
*/
protected void ReadFrom(XmlDocument xmldoc)
{
try
{
doc = StyleSheetDocument.Parse(xmldoc, NamespaceManager);
CT_Stylesheet styleSheet = doc.GetStyleSheet();
// Grab all the different bits we care about
CT_NumFmts ctfmts = styleSheet.numFmts;
if (ctfmts != null)
{
foreach (CT_NumFmt nfmt in ctfmts.numFmt)
{
numberFormats.Add((int)nfmt.numFmtId, nfmt.formatCode);
}
}
CT_Fonts ctfonts = styleSheet.fonts;
if (ctfonts != null)
{
int idx = 0;
foreach (CT_Font font in ctfonts.font)
{
// Create the font and save it. Themes Table supplied later
XSSFFont f = new XSSFFont(font, idx);
fonts.Add(f);
idx++;
}
}
CT_Fills ctFills = styleSheet.fills;
if (ctFills != null)
{
foreach (CT_Fill fill in ctFills.fill)
{
fills.Add(new XSSFCellFill(fill));
}
}
CT_Borders ctborders = styleSheet.borders;
if (ctborders != null)
{
foreach (CT_Border border in ctborders.border)
{
borders.Add(new XSSFCellBorder(border));
}
}
CT_CellXfs cellXfs = styleSheet.cellXfs;
if (cellXfs != null)
{
xfs.AddRange(cellXfs.xf);
}
CT_CellStyleXfs cellStyleXfs = styleSheet.cellStyleXfs;
if (cellStyleXfs != null)
{
styleXfs.AddRange(cellStyleXfs.xf);
}
CT_Dxfs styleDxfs = styleSheet.dxfs;
if (styleDxfs != null)
{
dxfs.AddRange(styleDxfs.dxf);
}
}
catch (XmlException e)
{
throw new IOException(e.Message);
}
}
private void CreateCommentsSheet(ISheet commentsSheet, ICellStyle cellStyle)
{
int num = 0;
CommentsSheetColumns["File"] = _exportOptions.File;
CommentsSheetColumns["Segment ID"] = _exportOptions.SegmentId;
CommentsSheetColumns["Comment"] = _exportOptions.Comment;
CommentsSheetColumns["Author"] = _exportOptions.Author;
CommentsSheetColumns["Date"] = _exportOptions.Date;
CommentsSheetColumns["Severity"] = _exportOptions.Severity;
CommentsSheetColumns["Version"] = _exportOptions.Version;
CommentsSheetColumns["Source"] = _exportOptions.Source;
CommentsSheetColumns["Target"] = _exportOptions.Target;
string[] array = (from c in CommentsSheetColumns
where c.Value
select c.Key).ToArray <string>();
CreateRow(commentsSheet, num, cellStyle, array);
foreach (CommentEntry current in _commentEntries)
{
num++;
XSSFFont inlineCommentFont = InlineCommentFont;
List <string> list = new List <string>();
if (array.Contains("File"))
{
list.Add(current.FileName);
}
if (array.Contains("Segment ID"))
{
list.Add(current.SegmentId);
}
if (array.Contains("Comment"))
{
list.Add(current.Text);
}
if (array.Contains("Author"))
{
list.Add(current.Author);
}
if (array.Contains("Date"))
{
string item = current.Date;
DateTime dateTime;
if (DateTime.TryParse(current.Date, out dateTime))
{
item = dateTime.ToString(DateTimeFormat);
}
list.Add(item);
}
if (array.Contains("Severity"))
{
list.Add(current.Severity);
}
if (array.Contains("Version"))
{
list.Add(current.Version);
}
List <ISegmentElement> sourceSegment = array.Contains("Source") ? current.SourceSegment : null;
List <ISegmentElement> targetSegment = array.Contains("Target") ? current.TargetSegment : null;
CreateRow(commentsSheet, num, cellStyle, inlineCommentFont, sourceSegment, targetSegment,
list.ToArray());
}
for (int i = 0; i < array.Length; i++)
{
if (array[i] == "Comment" || array[i] == "Source" || array[i] == "Target")
{
commentsSheet.SetColumnWidth(i, 12500);
}
else
{
commentsSheet.AutoSizeColumn(i);
}
}
}
public static bool DataSetToExcel(DataSet ds, string Path)
{
bool result = false;
FileStream fs = null;
XSSFWorkbook workbook = new XSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
////自定义表头
string name = ds.Tables[i].TableName;
//for (var j = 0; j < dataList[name].ToArray().Count(); j++)
//{
// headerRow.CreateCell(j).SetCellValue(dataList[name].ToArray()[j]);
// headerRow.GetCell(j).CellStyle = headStyle;
//}
}
#endregion
rowIndex = 1;
}
#endregion
foreach (DataRow row in ds.Tables[i].Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in ds.Tables[i].Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
newCell.SetCellValue(GetValue(row[column].ToString(), type));
}
#endregion
rowIndex++;
}
}
using (fs = File.OpenWrite(Path))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
return(result);
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}
