public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue("Date Range");
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
}
private static void OutputDateRow(ISheet sheet, IEnumerable<string> distinctDates, int rowIndex)
{
var colSpan = 3;
var startCol = 1;
var dateRow = sheet.CreateRow(rowIndex);
var headerRow = sheet.CreateRow(rowIndex + 1);
dateRow.CreateCell(0).SetCellValue("");
headerRow.CreateCell(0).SetCellValue("");
for (var i = 0; i < distinctDates.Count(); i++)
{
var cell = dateRow.CreateCell(startCol);
cell.SetCellValue(distinctDates.ElementAt(i));
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, startCol, colSpan * (i + 1)));
headerRow.CreateCell(startCol).SetCellValue(ValueHeader);
headerRow.CreateCell(startCol + 1).SetCellValue(PrefixHeader);
headerRow.CreateCell(startCol + 2).SetCellValue(DlHeader);
startCol = startCol + colSpan;
}
}
public void AddListToExcelSheet(HSSFWorkbook workbook, ISheet sheet, string Title, Dictionary<string, bool> list)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue(Title);
foreach (var org in list)
{
if (org.Value == true)
{
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
cell.SetCellValue(org.Key);
}
}
}
static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
{
sheet.SetColumnWidth(0, 6000);
sheet.SetColumnWidth(1, 6000);
sheet.SetColumnWidth(2, 3600);
sheet.SetColumnWidth(3, 3600);
sheet.SetColumnWidth(4, 2400);
sheet.SetColumnWidth(5, 2400);
sheet.SetColumnWidth(6, 2400);
sheet.SetColumnWidth(7, 2400);
sheet.SetColumnWidth(8, 2400);
IRow row = sheet.CreateRow(0);
ICellStyle style = wb.CreateCellStyle();
IFont font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
WriteHeaderCell(row, 0, "Raw Long Bits A", style);
WriteHeaderCell(row, 1, "Raw Long Bits B", style);
WriteHeaderCell(row, 2, "Value A", style);
WriteHeaderCell(row, 3, "Value B", style);
WriteHeaderCell(row, 4, "Exp Cmp", style);
WriteHeaderCell(row, 5, "LT", style);
WriteHeaderCell(row, 6, "EQ", style);
WriteHeaderCell(row, 7, "GT", style);
WriteHeaderCell(row, 8, "Check", style);
}
static void AddToExcel(ISheet _sheet, CharaData _data, int _rowIdx)
{
IRow row = _sheet.CreateRow(_rowIdx);
int cellIdx = 1;
// name
ICell nameCell = row.CreateCell(cellIdx++);
nameCell.SetCellValue(_data.m_name);
// hp
ICell hpCell = row.CreateCell(cellIdx++);
hpCell.SetCellValue(_data.m_hp);
// x
ICell posxCell = row.CreateCell(cellIdx++);
posxCell.SetCellValue(_data.m_position.x);
// y
ICell posyCell = row.CreateCell(cellIdx++);
posyCell.SetCellValue(_data.m_position.y);
// z
ICell poszCell = row.CreateCell(cellIdx++);
poszCell.SetCellValue(_data.m_position.z);
}
static void CreateHeaderRow(ISheet _sheet)
{
IRow header = _sheet.CreateRow(1);
int cellIdx = 1;
// name
ICell nameCell = header.CreateCell(cellIdx++);
nameCell.SetCellValue("name");
// hp
ICell hpCell = header.CreateCell(cellIdx++);
hpCell.SetCellValue("hp");
// x
ICell posxCell = header.CreateCell(cellIdx++);
posxCell.SetCellValue("x");
// y
ICell posyCell = header.CreateCell(cellIdx++);
posyCell.SetCellValue("y");
// z
ICell poszCell = header.CreateCell(cellIdx++);
poszCell.SetCellValue("z");
}
public bool Init()
{
if (!File.Exists(m_StrategyName))
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
IRow Row = m_StrategySheet.CreateRow(0);
Row.CreateCell(0).SetCellValue("-500");
Row.CreateCell(1).SetCellValue("-450");
Row.CreateCell(2).SetCellValue("-400");
Row.CreateCell(3).SetCellValue("-350");
Row.CreateCell(4).SetCellValue("-300");
Row.CreateCell(5).SetCellValue("-250");
Row.CreateCell(6).SetCellValue("-200");
Row.CreateCell(7).SetCellValue("-150");
Row.CreateCell(8).SetCellValue("-100");
Row.CreateCell(9).SetCellValue("-50");
Row.CreateCell(10).SetCellValue("0");
Row.CreateCell(11).SetCellValue("50");
Row.CreateCell(12).SetCellValue("100");
Row.CreateCell(13).SetCellValue("150");
Row.CreateCell(14).SetCellValue("200");
Row.CreateCell(15).SetCellValue("250");
Row.CreateCell(16).SetCellValue("300");
Row.CreateCell(17).SetCellValue("350");
Row.CreateCell(18).SetCellValue("400");
Row.CreateCell(19).SetCellValue("450");
Row.CreateCell(20).SetCellValue("500");
return true;
}
return false;
}
// convenient access to namespace
//private static ErrorEval EE = null;
private static void CreateDataRow(ISheet sheet, int rowIndex, params double[] vals)
{
IRow row = sheet.CreateRow(rowIndex);
for (int i = 0; i < vals.Length; i++)
{
row.CreateCell(i).SetCellValue(vals[i]);
}
}
// 创建 Excel 内容主体
private void CreateBody(ISheet sheet, IEnumerable<object[]> columnDatas)
{
var i = 1; // 第二行开始
foreach (var data in columnDatas)
{
var row = sheet.CreateRow(i++);
this.CreateRow(row, data);
}
}
public void CreateRows(int rowsCount, int cellsCount, ref ISheet sheet, IList<string> cellsValues = null)
{
if (sheet == null) return;
for (var i = 1; i <= rowsCount; i++)
{
var r = sheet.CreateRow(i);
CreateCells(cellsCount, ref r, cellsValues);
}
}
private void AddDataToReport(ISheet worksheet, DefaultExceptionScheduleReportData[] items, bool isDefault = true)
{
var rowIndex = worksheet.LastRowNum + 1;
var dateTitle = DateTime.MinValue;
foreach (var item in items)
{
IRow row;
if (isDefault)
{
row = worksheet.CreateRow(rowIndex++);
WriteCell(row, 0, c => c.SetCellValue(item.ScheduleDate.ToShortDateString()));
}
else
{
if (dateTitle != item.ScheduleDate)
{
dateTitle = item.ScheduleDate;
row = worksheet.CreateRow(rowIndex++);
WriteCell(row, 0, c => c.SetCellValue(dateTitle.ToShortDateString()), styles[StandardCellStyles.BoldStyle]);
}
row = worksheet.CreateRow(rowIndex++);
WriteCell(row, 0, c => c.SetCellValue((item as ServiceExceptionScheduleReportData).Service));
}
WriteCell(row, 1, c => c.SetCellValue(item.IsWorked ? "Да" : "Нет"));
if (item.IsWorked)
{
WriteCell(row, 2, c => c.SetCellValue(item.StartTime.ToString()));
WriteCell(row, 3, c => c.SetCellValue(item.FinishTime.ToString()));
WriteCell(row, 4, c => c.SetCellValue(item.ClientInterval.Minutes));
WriteCell(row, 5, c => c.SetCellValue(item.Intersection.Minutes));
WriteCell(row, 6, c => c.SetCellValue(item.MaxClientRequests));
}
};
}
static void GenerateRow(ISheet sheet1,int rowid,string firstName, string lastName, double salaryAmount, double taxRate)
{
IRow row = sheet1.CreateRow(rowid);
row.CreateCell(0).SetCellValue(firstName); //A2
row.CreateCell(1).SetCellValue(lastName); //B2
row.CreateCell(2).SetCellValue(salaryAmount); //C2
row.CreateCell(3).SetCellValue(taxRate); //D2
row.CreateCell(4).SetCellFormula(string.Format("C{0}*D{0}",rowid+1));
row.CreateCell(5).SetCellFormula(string.Format("C{0}-E{0}", rowid + 1));
}
/// <summary>
/// sheet 抬头
/// </summary>
/// <param name="sheet">sheet 对象</param>
private void CreateRowHeader(ISheet sheet)
{
var row = sheet.CreateRow(0);
for (var i = 0; i < this._columnNames.Length; i++)
{
var cell = row.CreateCell(i);
this.WriteToCell(cell, typeof(string), this._columnNames[i]);
}
this.CreateRow(row, this._columnNames, false);
}
static void CreateCellArray(ISheet sheet)
{
for (int i = 0; i < 300; i++)
{
IRow row=sheet.CreateRow(i);
for (int j = 0; j < 150; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(i*j);
}
}
}
private static void CreateHeader(ISheet workSheet, IEnumerable<Cell> cells)
{
var header = workSheet.CreateRow(0);
var currentCol = 0;
foreach (var cell in cells)
{
header.CreateCell(currentCol).SetCellValue(cell.Title);
currentCol++;
}
}
/**
* Highlight cells based on their values
*/
static void SameCell(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue(84);
sheet.CreateRow(1).CreateCell(0).SetCellValue(74);
sheet.CreateRow(2).CreateCell(0).SetCellValue(50);
sheet.CreateRow(3).CreateCell(0).SetCellValue(51);
sheet.CreateRow(4).CreateCell(0).SetCellValue(49);
sheet.CreateRow(5).CreateCell(0).SetCellValue(41);
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Cell Value Is greater than 70 (Blue Fill)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "70");
IPatternFormatting fill1 = rule1.CreatePatternFormatting();
fill1.FillBackgroundColor = (IndexedColors.Blue.Index);
fill1.FillPattern = (short)FillPattern.SolidForeground;
// Condition 2: Cell Value Is less than 50 (Green Fill)
IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "50");
IPatternFormatting fill2 = rule2.CreatePatternFormatting();
fill2.FillBackgroundColor = (IndexedColors.Green.Index);
fill2.FillPattern= (short)FillPattern.SolidForeground;
CellRangeAddress[] regions = {
CellRangeAddress.ValueOf("A1:A6")
};
sheetCF.AddConditionalFormatting(regions, rule1, rule2);
sheet.GetRow(0).CreateCell(2).SetCellValue("<== Condition 1: Cell Value is greater than 70 (Blue Fill)");
sheet.GetRow(4).CreateCell(2).SetCellValue("<== Condition 2: Cell Value is less than 50 (Green Fill)");
}
public IRow CreateRowCell(ISheet sheet, int rowIdx, int fromColIdx, int toColIdx, ICellStyle cellStyle)
{
IRow row = sheet.CreateRow(rowIdx);
for (int i = fromColIdx; i <= toColIdx; i++)
{
ICell cell = row.CreateCell(i);
if (cellStyle != null)
{
cell.CellStyle = cellStyle;
}
}
return row;
}
/**
* Highlight multiple cells based on a formula
*/
static void MultiCell(ISheet sheet)
{
// header row
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("Units");
row0.CreateCell(1).SetCellValue("Cost");
row0.CreateCell(2).SetCellValue("Total");
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue(71);
row1.CreateCell(1).SetCellValue(29);
row1.CreateCell(2).SetCellValue(2059);
IRow row2 = sheet.CreateRow(2);
row2.CreateCell(0).SetCellValue(85);
row2.CreateCell(1).SetCellValue(29);
row2.CreateCell(2).SetCellValue(2059);
IRow row3 = sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue(71);
row3.CreateCell(1).SetCellValue(29);
row3.CreateCell(2).SetCellValue(2059);
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Formula Is =$B2>75 (Blue Fill)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("$A2>75");
IPatternFormatting fill1 = rule1.CreatePatternFormatting();
fill1.FillBackgroundColor = (IndexedColors.Blue.Index);
fill1.FillPattern = ((short)FillPattern.SolidForeground);
CellRangeAddress[] regions = {
CellRangeAddress.ValueOf("A2:C4")
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheet.GetRow(2).CreateCell(4).SetCellValue("<== Condition 1: Formula is =$B2>75 (Blue Fill)");
}
// 表格头部设置
private static void HeaderSetting(ExportExcelPageMaker configData, IWorkbook workbook, ISheet sheet)
{
IRow dataRow = sheet.CreateRow(0);
int index = 0;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(index);
ICellStyle styleHeader = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Header);
cell.SetCellValue(fieldInfo.DisplayName);
cell.CellStyle = styleHeader;
int colWith = GetColWidth(fieldInfo);
sheet.SetColumnWidth(index, colWith << 8);
index++;
}
}
public void Init()
{
if (!File.Exists(m_sFileName))
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
IRow Row = m_StrategySheet.CreateRow(0);
int nCount = 0;
foreach(KeyValuePair<int, int> kp in m_DicLetter)
{
Row.CreateCell(nCount).SetCellValue(kp.Key);
nCount++;
}
}
}
/// <summary>
/// 為資料建立成NPOI 的Workbook和Sheet
/// </summary>
/// <param name="sourceTable">要匯出的內容</param>
/// <param name="workbook">The workbook.</param>
/// <param name="sheet">The sheet.</param>
/// <param name="headerRowIndex">標題欄的index起始位置</param>
/// <param name="rowIndexStart">第一筆實際資料的起始位置</param>
private void CreatWorksheetForSingleDataTable(DataTable sourceTable, ref HSSFWorkbook workbook,
ref ISheet sheet, int headerRowIndex = 0, int rowIndexStart = 1)
{
IRow headerRow = sheet.CreateRow(headerRowIndex);
// Set up the Header Coloumn cell style
var headerCellStyle = workbook.CreateCellStyle();
headerCellStyle.BorderTop = BorderStyle.Thin;
headerCellStyle.BorderBottom = BorderStyle.Thin;
headerCellStyle.Alignment = HorizontalAlignment.Center;
// Set up the Header Coloumn cell font style
var headerCellFontStyle = workbook.CreateFont();
headerCellFontStyle.Boldweight = (short)FontBoldWeight.Bold;
headerCellStyle.SetFont(headerCellFontStyle);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
var headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerCellStyle;
}
// handling value.
int rowIndex = rowIndexStart;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
public void LoadExcel()
{
m_OrderLogWorkBook = new XSSFWorkbook();
m_OrderLogSheet = (ISheet)m_OrderLogWorkBook.CreateSheet("Sheet1");
IRow Row = m_OrderLogSheet.CreateRow(0);
Row.CreateCell(0).SetCellValue("日期");
Row.CreateCell(1).SetCellValue("策略");
Row.CreateCell(2).SetCellValue("停利");
Row.CreateCell(3).SetCellValue("停損");
Row.CreateCell(4).SetCellValue("多空");
Row.CreateCell(5).SetCellValue("口數");
Row.CreateCell(6).SetCellValue("成交價");
Row.CreateCell(7).SetCellValue("獲利");
Row.CreateCell(8).SetCellValue("結果");
Row.CreateCell(9).SetCellValue("其他");
}
private static void CreateHeadRow(DataTable dt, HSSFWorkbook workbook, ISheet sheet)
{
ICellStyle titleStyle = workbook.CreateCellStyle();
var styleHeader = CreateHeaderStyle(workbook,
HSSFColor.WHITE.index, HSSFColor.GREEN.index);
IRow row = sheet.CreateRow(0);
ICell c1 = row.CreateCell(0);
ICell c2 = row.CreateCell(1);
ICell c3 = row.CreateCell(2);
int i = 0;
foreach (DataColumn cell in dt.Columns)
{
ICell iCell = row.CreateCell(i);
iCell.SetCellValue(cell.ColumnName);
iCell.CellStyle = styleHeader;
sheet.AutoSizeColumn(i);
i++;
}
}
private static void DrawSheet1(ISheet sheet1)
{
// Create a row and size one of the cells reasonably large.
IRow row = sheet1.CreateRow(2);
row.Height = ((short)2800);
row.CreateCell(1);
sheet1.SetColumnWidth(2, 9000);
// Create the Drawing patriarch. This is the top level container for
// all shapes.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
// Draw some lines and an oval.
DrawLinesToCenter(patriarch);
DrawManyLines(patriarch);
DrawOval(patriarch);
DrawPolygon(patriarch);
// Draw a rectangle.
HSSFSimpleShape rect = patriarch.CreateSimpleShape(new HSSFClientAnchor(100, 100, 900, 200, (short)0, 0, (short)0, 0));
rect.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
}
public bool Init()
{
if (!File.Exists(m_StrategyName))
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
IRow Row = m_StrategySheet.CreateRow(0);
Row.CreateCell(0).SetCellValue("策略名稱");
Row.CreateCell(1).SetCellValue("停利");
Row.CreateCell(2).SetCellValue("停損");
Row.CreateCell(3).SetCellValue("總次數");
Row.CreateCell(4).SetCellValue("成功機率");
Row.CreateCell(5).SetCellValue("期望值");
Row.CreateCell(6).SetCellValue("近2年次數");
Row.CreateCell(7).SetCellValue("近2年成功率");
Row.CreateCell(8).SetCellValue("近2年期望值");
Row.CreateCell(9).SetCellValue("多/空");
return true;
}
return false;
}
/**
* Fills a spreadsheet row with one comparison example. The two numeric values are written to
* columns C and D. Columns (F, G and H) respectively Get formulas ("v0<v1", "v0=v1", "v0>v1"),
* which will be Evaluated by Excel. Column D Gets the expected comparison result. Column I
* Gets a formula to check that Excel's comparison results match that predicted in column D.
*
* @param v0 the first value to be Compared
* @param v1 the second value to be Compared
* @param expRes expected comparison result (-1, 0, or +1)
*/
static void WriteDataRow(ISheet sheet, int rowIx, double v0, double v1, int expRes)
{
IRow row = sheet.CreateRow(rowIx);
int rowNum = rowIx + 1;
row.CreateCell(0).SetCellValue(FormatDoubleAsHex(v0));
row.CreateCell(1).SetCellValue(FormatDoubleAsHex(v1));
row.CreateCell(2).SetCellValue(v0);
row.CreateCell(3).SetCellValue(v1);
row.CreateCell(4).SetCellValue(expRes < 0 ? "LT" : expRes > 0 ? "GT" : "EQ");
row.CreateCell(5).CellFormula = ("C" + rowNum + "<" + "D" + rowNum);
row.CreateCell(6).CellFormula = ("C" + rowNum + "=" + "D" + rowNum);
row.CreateCell(7).CellFormula = ("C" + rowNum + ">" + "D" + rowNum);
// TODO - bug elsewhere in POI - something wrong with encoding of NOT() function
String frm = "if(or(" +
"and(E#='LT', F# , G#=FALSE, H#=FALSE)," +
"and(E#='EQ', F#=FALSE, G# , H#=FALSE)," +
"and(E#='GT', F#=FALSE, G#=FALSE, H# )" +
"), 'OK', 'error')";
row.CreateCell(8).CellFormula = (frm.Replace("#", rowNum.ToString()).Replace('\'', '"'));
}
public static int BuildDataSource(WFActivityField field, ISheet sheetDS, int startIndex)
{
string strSql = "";
switch (field.DataType)
{
case FieldInfo.DATATYPE_LIST:
strSql = String.Format("SELECT [Value] FROM [dbo].[SGP_KeyValue] WHERE [Key] = '{0}' AND ISNULL([Value],'')<>'' AND Status = 1 ORDER BY Sort", field.KeyValueSource);
break;
case FieldInfo.DATATYPE_LIST_SQL:
strSql = field.KeyValueSource;
break;
}
if (strSql != "")
{
DataTable dt = DbHelperSQL.Query(strSql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
sheetDS.CreateRow(startIndex).CreateCell(0).SetCellValue(Convert.ToString(dr["Value"]));
startIndex++;
}
}
return startIndex;
}
public ISheet CreateCell(ISheet sheet, int row_count, int col_count, int start_row)
{
for (int i = 0; i <= row_count; i++)
{
IRow row = null;
if (sheet.GetRow(start_row + i) == null)
{
row = sheet.CreateRow(start_row + i);
}
else
{
row = sheet.GetRow(start_row + i);
}
for (int j = 0; j <= col_count; j++)
{
if (row.GetCell(j) == null)
{
row.CreateCell(j);
}
}
}
return sheet;
}
private void button2_Click(object sender, EventArgs e)
{
isUsername = this.checkBox2.Checked;
usernameLength = this.numericUpDown2.Value;
isPassword = this.checkBox3.Checked;
passwordLength = this.numericUpDown3.Value;
isPppoeUsername = this.checkBox4.Checked;
pppoeUsernameLength = this.numericUpDown4.Value;
isPppoePassword = this.checkBox5.Checked;
pppoePasswordLength = this.numericUpDown5.Value;
count = this.numericUpDown6.Value;
titleList.Add("MAC Address");
titleList.Add("Username");
titleList.Add("Password");
titleList.Add("WirelessKey");
titleList.Add("PPPoE Username");
titleList.Add("PPPoE Password");
titleList.Add("IP Address");
titleList.Add("Mask");
titleList.Add("Gateway");
titleList.Add("DNS");
titleList.Add("2.4GHZ SSID");
titleList.Add("2.4GHZ Guest SSID");
titleList.Add("5GHZ SSID");
titleList.Add("5GHZ Guest SSID");
titleList.Add("Generate Flag");
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("CONFIG INFO");
IRow row;
ICell cell;
ICellStyle s = workbook.CreateCellStyle();
s.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Aqua.Index;
s.FillPattern = FillPattern.SolidForeground;
row = sheet.CreateRow(0);
int tempcount = 0;
foreach (var title in titleList)
{
cell = row.CreateCell(tempcount);
cell.SetCellValue(title);
cell.CellStyle = s;
sheet.SetColumnWidth(tempcount, 20 * 256);
tempcount++;
}
for (decimal index = 0; index < count; index++)
{
row = sheet.CreateRow(Convert.ToInt32(index) + 1);
if (isUsername)
{
cell = row.CreateCell(1);
cell.SetCellValue(getRandomizer(Convert.ToInt32(usernameLength), true, true, true, false));
}
if (isPassword)
{
cell = row.CreateCell(2);
cell.SetCellValue(getRandomizer(Convert.ToInt32(passwordLength), true, true, true, false));
}
if (isPppoeUsername)
{
cell = row.CreateCell(4);
cell.SetCellValue(getRandomizer(Convert.ToInt32(pppoeUsernameLength), true, true, true, false));
}
if (isPppoePassword)
{
cell = row.CreateCell(5);
cell.SetCellValue(getRandomizer(Convert.ToInt32(pppoePasswordLength), true, true, true, false));
}
if (true)
{
cell = row.CreateCell(14);
cell.SetCellValue(0);
}
}
if (File.Exists(filePath))
{
File.Delete(filePath);
}
FileStream sw = File.Create(filePath);
workbook.Write(sw);
sw.Close();
//MessageBox.Show("Generate success in" + filePath);
try
{
System.Diagnostics.Process.Start("explorer.exe", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
}
catch (IOException error)
{
MessageBox.Show("ERROR 13:" + error.Message);
}
}
//public void OutputBinayStream(System.IO.FileInfo file, HttpContext context)
//{
// #region Output Binary Stream
// context.Response.Clear();
// context.Response.Charset = "GB2312";
// context.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(file.Name));
// context.Response.AddHeader("Content-Length", file.Length.ToString());
// context.Response.ContentType = "application/ms-excel";
// context.Response.WriteFile(file.FullName);
// context.Response.End();
// #endregion
//}
#endregion
#region 动态转换成excel
public static bool ToExcelDynamic(string savePath, string SheetName, string HeadName, DataTable table, Dictionary <string, string> excelDataMap)
{
try
{
List <int> dateCellIndex = new List <int>();//日期格式列
bool header = true;
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(SheetName);
#region 表头样式
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 行样式
ICellStyle stylerow = wk.CreateCellStyle();
stylerow.Alignment = HorizontalAlignment.Center;
IFont fontrow = wk.CreateFont();
fontrow.FontName = "宋体";
fontrow.FontHeightInPoints = 12;
stylerow.SetFont(fontrow);
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
#endregion
//合并标题行
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));
IRow rowhead = tb.CreateRow(0); //创建一行
rowhead.HeightInPoints = 25; //行高
ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cellhead.CellStyle = headStyle;
cellhead.SetCellValue(HeadName); //写入表头
//SetCellRangeAddress(tb, 0, 0, 1, 20);
int rowIndex = 1;
if (header)
{
IRow row = tb.CreateRow(rowIndex); //创建一行
rowIndex++;
row.HeightInPoints = 25; //行高
int cellIndex = 0; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.CellStyle = headStyle;
if (columsName == "DEPT_NAME")
{
cell.SetCellValue("单位名称");
}
else
{
cell.SetCellValue(table.Columns[columsName].ToString());//循环往第二行的单元格中添加数据
}
cellIndex++;
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex + i); //创建一行
row.HeightInPoints = 20; //行高
int cellIndex = 0; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.SetCellValue(table.Rows[i][columsName].ToString()); //循环往第二行的单元格中添加数据
cell.CellStyle = stylerow;
cellIndex++;
}
}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// 构造Excel
/// </summary>
/// <param name="sourceTable"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet(sheetName);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, sourceTable.Columns.Count - 1));
#region 样式
// 标题字体
IFont titlefont = workbook.CreateFont();
titlefont.FontName = "黑体";
titlefont.FontHeightInPoints = 18; // 字体大小
titlefont.IsBold = false; //加粗
// 列表标题字体
IFont headfont = workbook.CreateFont();
headfont.FontName = "黑体";
headfont.FontHeightInPoints = 12; // 字体大小
headfont.IsBold = false; //加粗
// 标题样式
ICellStyle titlestyle = workbook.CreateCellStyle();
titlestyle.SetFont(titlefont);
titlestyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // 左右居中
titlestyle.VerticalAlignment = VerticalAlignment.Center; // 上下居中
titlestyle.IsLocked = true;
titlestyle.WrapText = true; // 自动换行
// 列头样式
ICellStyle headstyle = workbook.CreateCellStyle();
headstyle.SetFont(headfont);
headstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // 左右居中
headstyle.VerticalAlignment = VerticalAlignment.Center; // 上下居中
headstyle.IsLocked = true;
// headstyle.WrapText = true;// 自动换行
#endregion
//创建标题
IRow headerRow = sheet.CreateRow(0);
headerRow.Height = 600;
ICell titleCell = headerRow.CreateCell(0);
titleCell.SetCellValue(new HSSFRichTextString(sheetName));
titleCell.CellStyle = titlestyle;
//创建列头
headerRow = sheet.CreateRow(1);
headerRow.Height = 400;
ICell headerCell;
foreach (DataColumn column in sourceTable.Columns)
{
headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headstyle;
}
int rowIndex = 2;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
//switch (column.DataType.ToString())
//{
// case "System.DateTime":
// dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToDateTime(row[column].ToString()));
// break;
// default:
// dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
// break;
//}
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
private int GenerateHelltilted(int rowIndex, ISheet sheet)
{
rowIndex++;
rowIndex++;
var row13 = sheet.CreateRow(rowIndex);
row13.CreateCell(0).SetCellValue("overzicht scheef vlak");
rowIndex++;
rowIndex++;
var list = _calc.CalculateSunRayOnTiltedScreen();
foreach (var data in list)
{
var alpha = sheet.CreateRow(rowIndex);
alpha.CreateCell(0).SetCellValue("Alpha");
alpha.CreateCell(1).SetCellValue(TotalSunHeat.CalculateRadianToDegree(data.Item1));
rowIndex++;
var wandAzimuth = sheet.CreateRow(rowIndex);
wandAzimuth.CreateCell(0).SetCellValue("wand azimuth");
wandAzimuth.CreateCell(1).SetCellValue(data.Item2);
rowIndex++;
int col_hoogte_zon = 2;
var uur = sheet.CreateRow(rowIndex);
uur.CreateCell(0).SetCellValue("uur");
uur.CreateCell(1).SetCellValue("uur");
rowIndex++;
var Qdirv = sheet.CreateRow(rowIndex);
Qdirv.CreateCell(0).SetCellValue("Qdir");
Qdirv.CreateCell(1).SetCellValue("graden");
rowIndex++;
var QhemV = sheet.CreateRow(rowIndex);
QhemV.CreateCell(0).SetCellValue("Qhem");
QhemV.CreateCell(1).SetCellValue("eenheid");
rowIndex++;
var Qgrv = sheet.CreateRow(rowIndex);
Qgrv.CreateCell(0).SetCellValue("Qgr");
Qgrv.CreateCell(1).SetCellValue("eenheid");
foreach (var yeet in data.Item3)
{
uur.CreateCell(col_hoogte_zon).SetCellValue(yeet.Item1);
Qdirv.CreateCell(col_hoogte_zon).SetCellValue(yeet.Item2);
QhemV.CreateCell(col_hoogte_zon).SetCellValue(yeet.Item3);
Qgrv.CreateCell(col_hoogte_zon).SetCellValue(yeet.Item4);
col_hoogte_zon++;
}
rowIndex++;
rowIndex++;
}
return(rowIndex);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
string sql1 = "select distinct Program,Date from [Working_hoursInfor] where SySe like '%" + System.Web.HttpContext.Current.Session["SySe"] + "%'";
DataTable program = Common.datatable(sql1);
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
#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 = DateTime.Now;
* workbook.SummaryInformation = si;
* }*/
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
/* 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;
* }
* }
* }
*/
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头
{
IRow row1 = sheet.CreateRow(0);
ICell cell = row1.CreateCell(0);
cell.SetCellValue(strHeaderText);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, program.Rows.Count + 3)); //合并列 该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
// row1.Height = 30 * 30; //行高
cell.CellStyle = HeadStyle(workbook);
IRow row2 = sheet.CreateRow(1);
ICell cell2 = row2.CreateCell(0);
cell2.SetCellValue("学号");
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
cell2.CellStyle = Sub_HeadStyle(workbook);
ICell cell3 = row2.CreateCell(1);
cell3.SetCellValue("姓名");
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
cell3.CellStyle = Sub_HeadStyle(workbook);
ICell cell4 = row2.CreateCell(2);
cell4.SetCellValue("班级");
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 2, 2));
cell4.CellStyle = Sub_HeadStyle(workbook);
ICell cell5 = row2.CreateCell(program.Rows.Count + 3);
cell5.SetCellValue("合计");
sheet.AddMergedRegion(new CellRangeAddress(1, 2, program.Rows.Count + 3, program.Rows.Count + 3));
cell5.CellStyle = Sub_HeadStyle(workbook);
//活动列的生成
for (int i = 3, j = 0; j < program.Rows.Count; i++, j++)
{
ICell cell_1 = row2.CreateCell(i);
cell_1.SetCellValue(Convert.ToDateTime(program.Rows[j][1]).Date);
cell_1.CellStyle = Sub_HeadStyle(workbook);
ICellStyle cstyle = cell_1.CellStyle;
cstyle.Alignment = HorizontalAlignment.Center;
cstyle.DataFormat = dateStyle.DataFormat;
// cell_1.CellStyle = dateStyle;
}
IRow row3 = sheet.CreateRow(2);
for (int i = 3, j = 0; j < program.Rows.Count; i++, j++)
{
ICell cell_2 = row3.CreateCell(i);
cell_2.SetCellValue(program.Rows[j][0].ToString());
cell_2.CellStyle = Sub_HeadStyle(workbook);
}
}
#endregion
#region 列头及样式
{
for (int columnNum = 0; columnNum <= 26; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度
columnWidth = 20;
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
}
#endregion
rowIndex = 3;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
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;
}
ICellStyle cstyle = newCell.CellStyle;
cstyle.Alignment = HorizontalAlignment.Center;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
// ms.Flush();
// ms.Position = 0;
return(ms);
}
}
private void PlantilllaPXI(DataSet ds)
{
ISheet Hoja1 = hssfworkbook.CreateSheet("Configuración de Invernadero");
IRow Fila1 = Hoja1.CreateRow(0);
IRow Fila3 = Hoja1.CreateRow(2);
IRow Fila4 = Hoja1.CreateRow(3);
Fila3.CreateCell(1).SetCellValue("Clave");
Fila3.CreateCell(2).SetCellValue("Superficie");
Fila3.CreateCell(3).SetCellValue("División");
Fila3.CreateCell(4).SetCellValue("Sustrato Colocado");
Fila3.CreateCell(5).SetCellValue("Vida del Sustrato");
Fila3.CreateCell(6).SetCellValue("Variable");
Fila3.CreateCell(7).SetCellValue("Interplantable");
ICell thisCell = null;
thisCell = Fila4.CreateCell(1, CellType.STRING); thisCell.SetCellValue("5A01"); thisCell.CellStyle = EstCeldas;
thisCell = Fila4.CreateCell(2, CellType.NUMERIC); thisCell.SetCellValue(1); thisCell.CellStyle = EstCeldas;
thisCell = Fila4.CreateCell(3, CellType.STRING); thisCell.SetCellValue("CL1"); thisCell.CellStyle = EstCeldas;
ICell dateCell = Fila4.CreateCell(4, CellType.NUMERIC); dateCell.SetCellValue(DateTime.Now); dateCell.CellStyle = EstCeldasDate;
thisCell = Fila4.CreateCell(5, CellType.NUMERIC); thisCell.SetCellValue(52); thisCell.CellStyle = EstCeldas;
thisCell = Fila4.CreateCell(6, CellType.STRING); thisCell.SetCellValue("V05"); thisCell.CellStyle = EstCeldas;
thisCell = Fila4.CreateCell(7, CellType.STRING); thisCell.SetCellValue("Si"); thisCell.CellStyle = EstCeldas;
int items = ds.Tables[0].Rows.Count;
for (int c = 0; c < (items); c++)
{
Fila3.CreateCell(c + 8).SetCellValue(ds.Tables[0].Rows[c]["Product"].ToString().Trim());
if (c % 3 == 0)
{
ICell newCell = Fila4.CreateCell(c + 8); newCell.SetCellValue("Si"); newCell.CellStyle = EstCeldas;
}
else
{
ICell newCell = Fila4.CreateCell(c + 8); newCell.SetCellValue("No"); newCell.CellStyle = EstCeldas;
}
}
foreach (ICell x in Fila3.Cells)
{
x.CellStyle = EstSubtitulos;
}
for (int i = 0; i < Fila3.Cells.Count; i++)
{
Hoja1.AutoSizeColumn(i + 1);
}
IName name = hssfworkbook.CreateName();
name.NameName = "PXI";
name.RefersToFormula = "'Configuración de Invernadero'" + "!$B$3:$" + getChar(items + 8) + "$130";
Fila1.CreateCell(1).SetCellValue("Configuración de Invernadero");
Hoja1.AddMergedRegion(new CellRangeAddress(0, 1, 1, 7));
Fila1.CreateCell(8).SetCellValue("Productos");
Hoja1.AddMergedRegion(new CellRangeAddress(0, 1, 8, items + 7));
Fila1.Cells[0].CellStyle = EstTitulos;
Fila1.Cells[1].CellStyle = EstTitulos;
for (int i = 4; i < 130; i++)
{
IRow row = Hoja1.CreateRow(i);
for (int j = 1; j < (items + 8); j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = EstCeldas;
}
}
Hoja1.ProtectSheet("<P@ssw0rd>");
}
static Logger()
{
// 设置路径
_pathLog = "Logs";
_pathOp = _pathLog + "/OperationLog";
_pathData = _pathLog + "/Data";
_pathSys = _pathLog + "/SystemLog";
try
{
// 建立日志文件夹
if (!Directory.Exists(_pathLog))
{
Directory.CreateDirectory(_pathLog);
}
if (!Directory.Exists(_pathOp))
{
Directory.CreateDirectory(_pathOp);
}
if (!Directory.Exists(_pathData))
{
Directory.CreateDirectory(_pathData);
}
if (!Directory.Exists(_pathSys))
{
Directory.CreateDirectory(_pathSys);
}
}
catch (Exception ex)
{
Debug.WriteLine("日志目录初始化错误");
}
string tim = DateTime.Now.ToString("yyyy年M月dd日");
_fileOp = _pathOp + "/" + "操作日志 " + tim + ".txt";
_fileData = _pathData + "/" + "数据 " + tim + ".xls";
_fileSys = _pathSys + "/" + "系统日志 " + tim + ".txt";
try
{
// 新建操作日志文件
if (!File.Exists(_fileOp))
{
File.Create(_fileOp).Close();
Debug.WriteLine("日志文件 " + _fileOp + " 不存在，新建该文件！");
_writer = new StreamWriter(_fileOp, true, Encoding.UTF8);
_writer.WriteLine("/*****************************/");
_writer.WriteLine("/******** 操作日志 *********/");
_writer.WriteLine("/******** " + tim + " *********/");
_writer.WriteLine("/*****************************/");
_writer.Flush();
_writer.Close();
_writer = null;
Debug.WriteLine(tim + " 开始写入操作日志文件");
}
}
catch (Exception ex)
{
Debug.WriteLine("写入操作日志错误！");
try { _writer.Close(); _writer = null; } catch { }
}
try
{
// 新建系统日志文件
if (!File.Exists(_fileSys))
{
File.Create(_fileSys).Close();
Debug.WriteLine("日志文件 " + _fileSys + " 不存在，新建该文件！");
_writer = new StreamWriter(_fileSys, true, Encoding.UTF8);
_writer.WriteLine("/*****************************/");
_writer.WriteLine("/******** 系统日志 *********/");
_writer.WriteLine("/******** " + tim + " *********/");
_writer.WriteLine("/*****************************/");
_writer.Flush();
_writer.Close();
_writer = null;
Debug.WriteLine(tim + " 开始写入系统日志文件");
}
}
catch (Exception ex)
{
Debug.WriteLine("写入系统日志错误！");
try { _writer.Close(); _writer = null; } catch { }
}
FileStream fs = null;
HSSFWorkbook wb = null;
try
{
// 新建数据文件
if (File.Exists(_fileData))
{
return;
}
File.Create(_fileData).Close();
Debug.WriteLine("数据文件 " + _fileData + " 不存在，新建该文件！");
fs = new FileStream(_fileData, FileMode.Create);
wb = new HSSFWorkbook();
wb.CreateSheet("实时温度");
wb.CreateSheet("电桥温度");
wb.CreateSheet("电导率数据");
ISheet sheet0 = wb.GetSheet("实时温度");
IRow rowSh0 = sheet0.GetRow(0);
rowSh0 = sheet0.CreateRow(0);
ICell cell0Sh0 = rowSh0.CreateCell(0, CellType.String);
cell0Sh0.SetCellValue("时间");
ICell cell1Sh0 = rowSh0.CreateCell(1, CellType.String);
cell1Sh0.SetCellValue("主槽温度值");
ISheet sheet1 = wb.GetSheet("电桥温度");
IRow rowSh1 = sheet1.GetRow(0);
rowSh1 = sheet1.CreateRow(0);
ICell cell0Sh1 = rowSh1.CreateCell(0, CellType.String);
cell0Sh1.SetCellValue("时间");
ICell cell1Sh1 = rowSh1.CreateCell(1, CellType.String);
cell1Sh1.SetCellValue("电桥温度值");
ISheet sheet2 = wb.GetSheet("电导率数据");
IRow rowSh2 = sheet2.CreateRow(0);
ICell cell0Sh2 = rowSh2.CreateCell(0, CellType.String);
cell0Sh2.SetCellValue("时间");
ICell cell1Sh2 = rowSh2.CreateCell(1, CellType.String);
cell1Sh2.SetCellValue("温度值");
ICell cell2Sh2 = rowSh2.CreateCell(2, CellType.String);
cell2Sh2.SetCellValue("电导率");
fs.Flush();
wb.Write(fs);
fs.Close();
fs.Dispose();
wb = null;
fs = null;
}
catch (Exception ex)
{
Debug.WriteLine("新建数据文件失败！");
try { wb = null; fs.Close(); fs.Dispose(); fs = null; } catch { }
}
}
public static bool ToExcelDate(string savePath, string SheetName, string HeadName, IList listJob, string className)
{
try
{
DataTable table = ToDataTable(listJob, className);
List <int> dateCellIndex = new List <int>();//日期格式列
bool header = true;
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(SheetName);
#region 表头样式
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 行样式
ICellStyle stylerow = wk.CreateCellStyle();
stylerow.Alignment = HorizontalAlignment.Center;
IFont fontrow = wk.CreateFont();
fontrow.FontName = "宋体";
fontrow.FontHeightInPoints = 12;
stylerow.SetFont(fontrow);
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#endregion
Dictionary <string, string> excelDataMap = ReadXml(className, false);
//合并标题行
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));
IRow rowhead = tb.CreateRow(0); //创建一行
rowhead.HeightInPoints = 25; //行高
ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cellhead.CellStyle = headStyle;
cellhead.SetCellValue(HeadName); //写入表头
//SetCellRangeAddress(tb, 0, 0, 1, 20);
int rowIndex = 1;
if (header)
{
IRow row = tb.CreateRow(rowIndex); //创建一行
rowIndex++;
row.HeightInPoints = 25; //行高
int cellIndex = 0; //开始列索引
#region 弃用
//for (int i = 0; i < table.Columns.Count; i++)
//{
// if (!table.Columns[i].ToString().EndsWith("_wennull"))
// {
// if (table.Columns[i].DataType.ToString() == "System.DateTime")//日期型
// {
// tb.SetColumnWidth(cellIndex, 20 * 256);//行宽10个汉字
// dateCellIndex.Add(cellIndex);
// }
// else
// {
// tb.SetColumnWidth(cellIndex, 16 * 256);//行宽8个汉字
// }
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// cell.CellStyle = headStyle;
// cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据
// cellIndex++;
// }
//}
#endregion
foreach (var item in excelDataMap)
{
string columsName = item.Value;
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
tb.SetColumnWidth(cellIndex, 20 * 256); //行宽10个汉字
dateCellIndex.Add(cellIndex);
}
else
{
tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字
}
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.CellStyle = headStyle;
cell.SetCellValue(table.Columns[columsName].ToString()); //循环往第二行的单元格中添加数据
cellIndex++;
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex + i); //创建一行
row.HeightInPoints = 20; //行高
int cellIndex = 0; //开始列索引
#region 弃用
//for (int c = 0; c < table.Columns.Count; c++)
//{
// if (!table.Columns[c].ToString().EndsWith("_wennull"))
// {
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// if (table.Columns[c].DataType.ToString() == "System.DateTime")//日期型
// {
// string value = table.Rows[i][c].ToString();
// if (value != "")
// {
// cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据
// }
// cell.CellStyle = dateStyle;
// }
// else
// {
// cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据
// cell.CellStyle = stylerow;
// }
// cellIndex++;
// }
//}
#endregion
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
string value = table.Rows[i][columsName].ToString();
if (value != "")
{
cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd"));//循环往第二行的单元格中添加数据
}
cell.CellStyle = dateStyle;
}
else
{
cell.SetCellValue(table.Rows[i][columsName].ToString());//循环往第二行的单元格中添加数据
cell.CellStyle = stylerow;
}
cellIndex++;
}
}
//foreach (int cellIndex in dateCellIndex)
//{
// for (int y = table.Rows.Count; y < 500; y++)//至少设置500行格式
// {
// IRow row = tb.CreateRow(rowIndex + y);//创建一行
// row.HeightInPoints = 20; //行高
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// //cell.SetCellValue("");//循环往第二行的单元格中添加数据
// cell.CellStyle = dateStyle;
// }
//}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return(true);
}
catch (Exception)
{
return(false);
}
}
private void Compare_Click(object sender, RoutedEventArgs e)
{
if (workbook1_input == null)
{
MessageBox.Show("请选择文件");
return;
}
if (workbook2_input == null)
{
MessageBox.Show("请选择文件");
return;
}
if (sheet_workbook1 == null)
{
MessageBox.Show("请选择Sheet");
return;
}
if (sheet_workbook2 == null)
{
MessageBox.Show("请选择Sheet");
return;
}
if (col_index1 < 0)
{
MessageBox.Show("请选择列");
return;
}
if (col_index2 < 0)
{
MessageBox.Show("请选择列");
return;
}
Dictionary <string, StrData> dic1 = new Dictionary <string, StrData>();
Dictionary <string, StrData> dic2 = new Dictionary <string, StrData>();
List <int> list_only1 = new List <int>();
List <int> list_only2 = new List <int>();
List <string> list_remove = new List <string>();
for (int i = 1; i <= sheet_workbook1.LastRowNum; ++i)
{
IRow row = sheet_workbook1.GetRow(i);
if (col_index1_1 >= 0)
{
ICell cell = row.GetCell(col_index1_1);
StrData data;
if (cell.ToString() != "")
{
data.col2 = Convert.ToDouble(cell.ToString());
data.row = row.RowNum;
}
else
{
data.col2 = 0;
data.row = row.RowNum;
}
string key = row.GetCell(col_index1).ToString();
if (dic1.ContainsKey(key))
{
dic1[key] = data;
}
else
{
dic1.Add(key, data);
}
}
else
{
StrData data;
data.col2 = 0;
data.row = row.RowNum;
string key = row.GetCell(col_index1).ToString();
if (dic1.ContainsKey(key))
{
dic1[key] = data;
}
else
{
dic1.Add(key, data);
}
}
}
for (int i = 1; i <= sheet_workbook2.LastRowNum; ++i)
{
IRow row = sheet_workbook2.GetRow(i);
if (col_index2_1 >= 0)
{
ICell cell = row.GetCell(col_index2_1);
StrData data;
if (cell.ToString() != "")
{
data.col2 = Convert.ToDouble(cell.ToString());
data.row = row.RowNum;
}
else
{
data.col2 = 0;
data.row = row.RowNum;
}
string key = row.GetCell(col_index2).ToString();
if (dic2.ContainsKey(key))
{
dic2[key] = data;
}
else
{
dic2.Add(key, data);
}
}
else
{
StrData data;
data.col2 = 0;
data.row = row.RowNum;
string key = row.GetCell(col_index2).ToString();
if (dic2.ContainsKey(key))
{
dic2[key] = data;
}
else
{
dic2.Add(key, data);
}
}
}
foreach (string str in dic1.Keys)
{
if (dic2.ContainsKey(str))
{
if (IsSamePrice(dic1[str].col2, dic2[str].col2))
{
list_remove.Add(str);
}
}
else if (is_func00_on == false)
{
list_only1.Add(dic1[str].row);
}
else if (str.Length == 15)
{
bool bFind = false;
int jj = 0;
if (str == "090302002000002")
{
jj = 1;
}
jj++;
string str_1 = Convert_str15_1(str);
string str_2 = Convert_str15_2(str);
if (dic2.ContainsKey(str_1))
{
if (IsSamePrice(dic1[str].col2, dic2[str_1].col2))
{
list_remove.Add(str_1);
bFind = true;
}
}
if (dic2.ContainsKey(str_2))
{
if (IsSamePrice(dic1[str].col2, dic2[str_2].col2))
{
list_remove.Add(str_2);
bFind = true;
}
}
if (!bFind)
{
list_only1.Add(dic1[str].row);
}
}
else
{
string str_tmp = Convert_str(str);
if (dic2.ContainsKey(str_tmp))
{
if (IsSamePrice(dic1[str].col2, dic2[str_tmp].col2))
{
list_remove.Add(str_tmp);
}
}
else
{
list_only1.Add(dic1[str].row);
}
}
}
foreach (string str in list_remove)
{
dic2.Remove(str);
}
foreach (string str in dic2.Keys)
{
list_only2.Add(dic2[str].row);
}
IWorkbook workbook_output = new HSSFWorkbook();
ISheet sheet1_output = workbook_output.CreateSheet(filename1);
ISheet sheet2_output = workbook_output.CreateSheet(filename2);
IRow row0_1 = sheet_workbook1.GetRow(0);
if (row0_1 != null)
{
IRow row_head = sheet1_output.CreateRow(0);
for (int i = 0; i < row0_1.LastCellNum; ++i)
{
ICell cell = row_head.CreateCell(i);
ICell input_cell = row0_1.GetCell(i);
if (input_cell != null)
{
cell.SetCellValue(input_cell.ToString());
}
else
{
cell.SetCellValue("");
}
}
}
for (int i = 0; i < list_only1.Count; ++i)
{
IRow row_input = sheet_workbook1.GetRow(list_only1[i]);
IRow row_output = sheet1_output.CreateRow(i + 1);
for (int j = 0; j < row_input.LastCellNum; ++j)
{
ICell cell = row_output.CreateCell(j);
ICell input_cell = row_input.GetCell(j);
if (input_cell != null)
{
cell.SetCellValue(input_cell.ToString());
}
else
{
cell.SetCellValue("");
}
}
}
IRow row0_2 = sheet_workbook2.GetRow(0);
if (row0_2 != null)
{
IRow row_head = sheet2_output.CreateRow(0);
for (int i = 0; i < row0_2.LastCellNum; ++i)
{
ICell cell = row_head.CreateCell(i);
ICell input_cell = row0_2.GetCell(i);
if (input_cell != null)
{
cell.SetCellValue(input_cell.ToString());
}
else
{
cell.SetCellValue("");
}
}
}
for (int i = 0; i < list_only2.Count; ++i)
{
IRow row_input = sheet_workbook2.GetRow(list_only2[i]);
IRow row_output = sheet2_output.CreateRow(i + 1);
for (int j = 0; j < row_input.LastCellNum; ++j)
{
ICell cell = row_output.CreateCell(j);
ICell input_cell = row_input.GetCell(j);
if (input_cell != null)
{
cell.SetCellValue(input_cell.ToString());
}
else
{
cell.SetCellValue("");
}
}
}
SaveFileDialog dlg = new SaveFileDialog();
dlg.FileName = "result";
dlg.DefaultExt = ".xls";
dlg.Filter = "Excel Worksheets|*.xls";
if (dlg.ShowDialog() == true)
{
string filename = dlg.FileName;
using (FileStream fs = File.OpenWrite(filename))
{
workbook_output.Write(fs);
fs.Close();
MessageBox.Show("保存成功!!");
}
}
}
public void TestBug55752()
{
IWorkbook wb = new XSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet("test");
for (int i = 0; i < 4; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < 2; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = (wb.CreateCellStyle());
}
}
// set content
IRow row1 = sheet.GetRow(0);
row1.GetCell(0).SetCellValue("AAA");
IRow row2 = sheet.GetRow(1);
row2.GetCell(0).SetCellValue("BBB");
IRow row3 = sheet.GetRow(2);
row3.GetCell(0).SetCellValue("CCC");
IRow row4 = sheet.GetRow(3);
row4.GetCell(0).SetCellValue("DDD");
// merge cells
CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1);
sheet.AddMergedRegion(range1);
CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1);
sheet.AddMergedRegion(range2);
CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1);
sheet.AddMergedRegion(range3);
Assert.AreEqual(0, range3.FirstColumn);
Assert.AreEqual(1, range3.LastColumn);
Assert.AreEqual(2, range3.LastRow);
CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1);
sheet.AddMergedRegion(range4);
// set border
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range1, sheet);
row2.GetCell(0).CellStyle.BorderBottom = BorderStyle.Thin;
row2.GetCell(1).CellStyle.BorderBottom = BorderStyle.Thin;
ICell cell0 = CellUtil.GetCell(row3, 0);
CellUtil.SetCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
ICell cell1 = CellUtil.GetCell(row3, 1);
CellUtil.SetCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range4, sheet);
// write to file
Stream stream = new FileStream("55752.xlsx", FileMode.Create, FileAccess.ReadWrite);
try
{
wb.Write(stream);
}
finally
{
stream.Close();
}
}
finally
{
wb.Close();
}
}
/*
* HSSFWorkbook hssfworkbook;
* IFont font;
* NPOI.HSSF.Util.HSSFColor fillForegroundColor;
* FillPattern fillPattern;
* HSSFColor fillBackgroundColor;
* HorizontalAlignment ha;
* VerticalAlignment va;
*/
/*
* foreach (PropertyDescriptor prop in props)
* {
* TableHeaderCell hcell = new TableHeaderCell();
* hcell.Text = prop.Name;
* // hcell.BackColor = System.Drawing.Color.Yellow;
* row.Cells.Add(hcell);
* }
*/
public static void WriteExcelWithNPOI(String extension, DataSet dt)
{
//excel文件格式判断
//要区分.xlsx和.xls文件格式，所用的sheet对象区别
/*xlsx是从Office2007开始使用的，是用新的基于XML的压缩文件格式取代了其目前专有的默认文件格式，
* 在传统的文件名扩展名后面添加了字母x（即：docx取代doc、.xlsx取代xls等等），使其占用空间更小。
*
*/
IWorkbook workbook;
if (extension == "xlsx")
{
workbook = new XSSFWorkbook();
// XSSFWorkbook workbook_XSS = new XSSFWorkbook();
// 右击文件 属性信息
{
//新版本的信息我也不知道在哪里设置。。。
}
}
else if (extension == "xls")
{
//workbook = new HSSFWorkbook();
HSSFWorkbook workbook_HSS = new HSSFWorkbook();
// 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.yongfa365.com/";
workbook_HSS.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_HSS.SummaryInformation = si;
}
workbook = workbook_HSS;
}
else
{
//throw new Exception("This format is not supported");
workbook = new XSSFWorkbook();
}
// dll refered NPOI.dll and NPOI.OOXML
for (int k = 0; k < dt.Tables.Count; k++)
{
DataTable dtSource = dt.Tables[k];
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽 相当于遍历啊，相当耗费资源啊
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = System.Text.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 = System.Text.Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
//int rowIndex = 0;
//ISheet sheet1 = workbook.CreateSheet(dtSource.TableName.ToString());
////make a header row
//IRow row1 = sheet1.CreateRow(0);
//for (int j = 0; j < dtSource.Columns.Count; j++)
//{
// ICell cell = row1.CreateCell(j);
// String columnName = dtSource.Columns[j].ToString();
// cell.SetCellValue(columnName);
//}
////loops through data
//for (int i = 0; i < dtSource.Rows.Count; i++)
//{
// IRow row = sheet1.CreateRow(i + 1);
// for (int j = 0; j < dtSource.Columns.Count; j++)
// {
// ICell cell = row.CreateCell(j);
// String columnName = dtSource.Columns[j].ToString();
// cell.SetCellValue(dtSource.Rows[i][columnName].ToString());
// }
//}
int rowIndex = 0;
ISheet sheet1 = workbook.CreateSheet(dtSource.TableName.ToString());
foreach (DataRow row in dtSource.Rows)
{
var cra = new NPOI.SS.Util.CellRangeAddress(100, 100, 100, 100);
var cra1 = new NPOI.SS.Util.CellRangeAddress(10, 10, 10, 10);
sheet1.AddMergedRegion(cra);
sheet1.AddMergedRegion(cra1);
// 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
// 表头及样式
{
IRow headerRow = sheet1.CreateRow(0);
headerRow.HeightInPoints = 100;
headerRow.CreateCell(0).SetCellValue("The Great Wall is a Great Building");
headerRow.CreateCell(1).SetCellValue("The Great Wall is a Great Building1");
ICellStyle headStyle = workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 100;
font.Boldweight = 1000;
headStyle.SetFont(font);
//单元格样式
var cellStyleBorder = workbook.CreateCellStyle();
cellStyleBorder.BorderBottom = BorderStyle.Thin;
cellStyleBorder.BorderLeft = BorderStyle.Thin;
cellStyleBorder.BorderRight = BorderStyle.Thin;
cellStyleBorder.BorderTop = BorderStyle.Thin;
cellStyleBorder.Alignment = HorizontalAlignment.Center;
cellStyleBorder.VerticalAlignment = VerticalAlignment.Center;
var cellStyleBorderAndColorGreen = workbook.CreateCellStyle();
cellStyleBorderAndColorGreen.CloneStyleFrom(cellStyleBorder);
cellStyleBorderAndColorGreen.CloneStyleFrom(headStyle);
//cellStyleBorderAndColorGreen.FillPattern = FillPattern.SolidForeground;
cellStyleBorderAndColorGreen.FillPattern = FillPattern.ThickBackwardDiagonals;
//cellStyleBorderAndColorGreen.FillBackgroundColor = //new SolidColorBrush(Color.FromArgb(255, 255, 0, 0, 0));
headerRow.GetCell(0).CellStyle = cellStyleBorderAndColorGreen;
headerRow.GetCell(1).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
// 列头及样式
{
//行样式
IRow headerRow = sheet1.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.BorderBottom = BorderStyle.Medium;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.VerticalAlignment = VerticalAlignment.Center;
headStyle.FillBackgroundColor = HSSFColor.Green.Index;
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
IFont font = 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;
//设置列宽
sheet1.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
rowIndex = 2;
}
// 填充内容
IRow dataRow = sheet1.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
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;
}
}
rowIndex++;
}
}
using (var exportData = new MemoryStream())
{
exportData.Flush();
exportData.Position = 0;
//HttpResponse Response;
//System.Web.HttpContext httpContext
//HttpResponse.Clear();
workbook.Write(exportData);
if (extension == "xlsx") //xlsx file format
{ /*
* HttpResponse response = httpContext.Response;
* response.Clear();
* response.BufferOutput = true;
* response.StatusCode = 200; // HttpStatusCode.OK;
* response.Write("Hello");
* response.ContentType = "text/xml";
* response.End();
*/
HttpContext curContext = HttpContext.Current;
// curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
curContext.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_Dict" + DateTime.Now.ToString() + ".xlsx"));
curContext.Response.BinaryWrite(exportData.ToArray());
curContext.Response.End();
}
else if (extension == "xls") //xls file format
{
HttpContext curContext = HttpContext.Current;
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_Dict" + DateTime.Now.ToString() + ".xls"));
curContext.Response.BinaryWrite(exportData.GetBuffer());
curContext.Response.End();
}
workbook.Dispose();
}
}
public static IRow CopyRow(ISheet sheet, int sourceRowIndex, int targetRowIndex)
{
if (sourceRowIndex == targetRowIndex)
{
throw new ArgumentException("sourceIndex and targetIndex cannot be same");
}
// Get the source / new row
IRow newRow = sheet.GetRow(targetRowIndex);
IRow sourceRow = sheet.GetRow(sourceRowIndex);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
sheet.ShiftRows(targetRowIndex, sheet.LastRowNum, 1);
}
else
{
newRow = sheet.CreateRow(targetRowIndex);
}
// Loop through source columns to add to new row
for (int i = sourceRow.FirstCellNum; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
ICell oldCell = sourceRow.GetCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
continue;
}
ICell newCell = newRow.CreateCell(i);
if (oldCell.CellStyle != null)
{
// apply style from old cell to new cell
newCell.CellStyle = oldCell.CellStyle;
}
// If there is a cell comment, copy
if (oldCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = sheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow - cellRangeAddress.FirstRow
)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
sheet.AddMergedRegion(newCellRangeAddress);
}
}
return(newRow);
}
public async Task ExportProtocol(int ProtocolId)
{
Protocol protocol = _repoWrapper.Protocol.GetById(ProtocolId);
if (protocol == null)
{
return;
}
string fileName = @"{0}-{1}.xlsx";
fileName = string.Format(fileName, protocol.Data.ToString("yyyy-MM-dd"), protocol.ProtocolId);
IWorkbook workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet();
IRow rowHeader = excelSheet.CreateRow(0);
IRow rowData = excelSheet.CreateRow(1);
rowHeader.CreateCell(0).SetCellValue("Протокол №");
rowData.CreateCell(0).SetCellValue(protocol.ProtocolId.ToString());
rowHeader.CreateCell(1).SetCellValue("Дата");
rowData.CreateCell(1).SetCellValue(protocol.Data.ToString("dd/MM/yyyy"));
rowHeader.CreateCell(2).SetCellValue("Служебен №");
rowData.CreateCell(2).SetCellValue(protocol.SlujNomer.ToString());
rowHeader.CreateCell(3).SetCellValue("Рег. №");
rowData.CreateCell(3).SetCellValue(protocol.RegNomer);
rowHeader.CreateCell(4).SetCellValue("Пробег");
rowData.CreateCell(4).SetCellValue(protocol.Probeg.ToString());
rowHeader.CreateCell(5).SetCellValue("Водач");
rowData.CreateCell(5).SetCellValue(protocol.Vodach);
rowHeader.CreateCell(6).SetCellValue("ВЪНШЕН ВИД ХИГИЕНА");
rowData.CreateCell(6).SetCellValue(protocol.VVHigiena);
rowHeader.CreateCell(7).SetCellValue("ВЪНШЕН ВИД ХИГИЕНА");
rowData.CreateCell(7).SetCellValue(protocol.VVHigienaDaNe);
rowHeader.CreateCell(8).SetCellValue("ВЪНШЕН ВИД БРОНИ");
rowData.CreateCell(8).SetCellValue(protocol.VVBroni);
rowHeader.CreateCell(9).SetCellValue("ВЪНШЕН ВИД БРОНИ");
rowData.CreateCell(9).SetCellValue(protocol.VVBroniDaNe);
rowHeader.CreateCell(10).SetCellValue("ВЪНШЕН ВИД КАЛНИЦИ");
rowData.CreateCell(10).SetCellValue(protocol.VVKalnici);
rowHeader.CreateCell(11).SetCellValue("ВЪНШЕН ВИД КАЛНИЦИ");
rowData.CreateCell(11).SetCellValue(protocol.VVKalniciDaNe);
rowHeader.CreateCell(12).SetCellValue("ВЪНШЕН ВИД ВРАТИ");
rowData.CreateCell(12).SetCellValue(protocol.VVVrati);
rowHeader.CreateCell(13).SetCellValue("ВЪНШЕН ВИД ВРАТИ");
rowData.CreateCell(13).SetCellValue(protocol.VVVratiDaNe);
rowHeader.CreateCell(14).SetCellValue("ВЪНШЕН ВИД СТЪКЛА");
rowData.CreateCell(14).SetCellValue(protocol.VVStykla);
rowHeader.CreateCell(15).SetCellValue("ВЪНШЕН ВИД СТЪКЛА");
rowData.CreateCell(15).SetCellValue(protocol.VVStyklaDaNe);
rowHeader.CreateCell(16).SetCellValue("ВЪНШЕН ВИД ДРУГИ");
rowData.CreateCell(16).SetCellValue(protocol.VVDrugi);
rowHeader.CreateCell(17).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ДВИГАТЕЛ");
rowData.CreateCell(17).SetCellValue(protocol.ZADvigatel);
rowHeader.CreateCell(18).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ДВИГАТЕЛ");
rowData.CreateCell(18).SetCellValue(protocol.ZADvigatelDaNe);
rowHeader.CreateCell(19).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ РЕМЪЦИ");
rowData.CreateCell(19).SetCellValue(protocol.ZaRemyci);
rowHeader.CreateCell(20).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ РЕМЪЦИ");
rowData.CreateCell(20).SetCellValue(protocol.ZaRemyciDaNe);
rowHeader.CreateCell(21).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ОХЛАЖДАЩА УРЕДБА");
rowData.CreateCell(21).SetCellValue(protocol.ZAOhlajdashtaUredba);
rowHeader.CreateCell(22).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ОХЛАЖДАЩА УРЕДБА");
rowData.CreateCell(22).SetCellValue(protocol.ZAOhlajdashtaUredbaDaNe);
rowHeader.CreateCell(23).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ГОРИВНА УРЕДБА");
rowData.CreateCell(23).SetCellValue(protocol.ZAGorivnaUredba);
rowHeader.CreateCell(24).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ГОРИВНА УРЕДБА");
rowData.CreateCell(24).SetCellValue(protocol.ZAGorivnaUredbaDaNe);
rowHeader.CreateCell(25).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ИЗПУСКАТЕЛНА УРЕДБА");
rowData.CreateCell(25).SetCellValue(protocol.ZAIzpuskatelnaUredba);
rowHeader.CreateCell(26).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ИЗПУСКАТЕЛНА УРЕДБА");
rowData.CreateCell(26).SetCellValue(protocol.ZAIzpuskatelnaUredbaDaNe);
rowHeader.CreateCell(27).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ МАЗИЛНА УРЕДБА");
rowData.CreateCell(27).SetCellValue(protocol.ZAMazilnaUredba);
rowHeader.CreateCell(28).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ МАЗИЛНА УРЕДБА");
rowData.CreateCell(28).SetCellValue(protocol.ZAMazilnaUredbaDaNe);
rowHeader.CreateCell(29).SetCellValue("ЗАДВИЖВАЩ АГРЕГАТ ДРУГИ");
rowData.CreateCell(29).SetCellValue(protocol.ZADrugi);
rowHeader.CreateCell(30).SetCellValue("ТРАНСМИСИЯ СЪЕДИНИТЕЛ");
rowData.CreateCell(30).SetCellValue(protocol.TMSyedinitel);
rowHeader.CreateCell(31).SetCellValue("ТРАНСМИСИЯ СЪЕДИНИТЕЛ");
rowData.CreateCell(31).SetCellValue(protocol.TMSyedinitelDaNe);
rowHeader.CreateCell(32).SetCellValue("ТРАНСМИСИЯ СКОРОСТНА КУТИЯ И ДИФЕРЕНЦИАЛ");
rowData.CreateCell(32).SetCellValue(protocol.TMSkorostnaKutiqIDiferencial);
rowHeader.CreateCell(33).SetCellValue("ТРАНСМИСИЯ СКОРОСТНА КУТИЯ И ДИФЕРЕНЦИАЛ");
rowData.CreateCell(33).SetCellValue(protocol.TMSkorostnaKutiqIDiferencialDaNe);
rowHeader.CreateCell(34).SetCellValue("ТРАНСМИСИЯ ДРУГИ");
rowData.CreateCell(34).SetCellValue(protocol.TMDrugi);
rowHeader.CreateCell(35).SetCellValue("ХОДОВА ЧАСТ КОЛЕЛА");
rowData.CreateCell(35).SetCellValue(protocol.HCHodoviKolela);
rowHeader.CreateCell(36).SetCellValue("ХОДОВА ЧАСТ КОЛЕЛА");
rowData.CreateCell(36).SetCellValue(protocol.HCHodoviKolelaDaNe);
rowHeader.CreateCell(37).SetCellValue("ХОДОВА ЧАСТ ТАМПОНИ МФ");
rowData.CreateCell(37).SetCellValue(protocol.HCTamponiMF);
rowHeader.CreateCell(38).SetCellValue("ХОДОВА ЧАСТ ТАМПОНИ МФ");
rowData.CreateCell(38).SetCellValue(protocol.HCTamponiMFDaNe);
rowHeader.CreateCell(39).SetCellValue("ХОДОВА ЧАСТ АМОРТИЬОРИ ПРЕДНИ");
rowData.CreateCell(39).SetCellValue(protocol.HCAmortisioriPredni);
rowHeader.CreateCell(40).SetCellValue("ХОДОВА ЧАСТ АМОРТИЬОРИ ПРЕДНИ");
rowData.CreateCell(40).SetCellValue(protocol.HCAmortisioriPredniDaNe);
rowHeader.CreateCell(41).SetCellValue("ХОДОВА ЧАСТ НОСАЧИ ПРЕДНИ");
rowData.CreateCell(41).SetCellValue(protocol.HCNosachiPredni);
rowHeader.CreateCell(42).SetCellValue("ХОДОВА ЧАСТ НОСАЧИ ПРЕДНИ");
rowData.CreateCell(42).SetCellValue(protocol.HCNosachiPredniDaNe);
rowHeader.CreateCell(43).SetCellValue("ХОДОВА ЧАСТ ШАРНИРНИ БОЛТОВЕ");
rowData.CreateCell(43).SetCellValue(protocol.HCSharnirniBoltove);
rowHeader.CreateCell(44).SetCellValue("ХОДОВА ЧАСТ ШАРНИРНИ БОЛТОВЕ");
rowData.CreateCell(44).SetCellValue(protocol.HCSharnirniBoltoveDaNe);
rowHeader.CreateCell(45).SetCellValue("ХОДОВА ЧАСТ КАРЕТА");
rowData.CreateCell(45).SetCellValue(protocol.HCKareta);
rowHeader.CreateCell(46).SetCellValue("ХОДОВА ЧАСТ КАРЕТА");
rowData.CreateCell(46).SetCellValue(protocol.HCKaretaDaNe);
rowHeader.CreateCell(47).SetCellValue("ХОДОВА ЧАСТ МАНШОНИ");
rowData.CreateCell(47).SetCellValue(protocol.HCManshoni);
rowHeader.CreateCell(48).SetCellValue("ХОДОВА ЧАСТ МАНШОНИ");
rowData.CreateCell(48).SetCellValue(protocol.HCManshoniDaNe);
rowHeader.CreateCell(49).SetCellValue("ХОДОВА ЧАСТ НОСАЧИ ЗАДНИ");
rowData.CreateCell(49).SetCellValue(protocol.HCNosachiZadni);
rowHeader.CreateCell(50).SetCellValue("ХОДОВА ЧАСТ НОСАЧИ ЗАДНИ");
rowData.CreateCell(50).SetCellValue(protocol.HCNosachiZadniDaNe);
rowHeader.CreateCell(51).SetCellValue("ХОДОВА ЧАСТ АМОРТИСЬОРИ ЗАДНИ");
rowData.CreateCell(51).SetCellValue(protocol.HCAmortisioriZadni);
rowHeader.CreateCell(52).SetCellValue("ХОДОВА ЧАСТ АМОРТИСЬОРИ ЗАДНИ");
rowData.CreateCell(52).SetCellValue(protocol.HCAmortisioriZadniDaNe);
rowHeader.CreateCell(53).SetCellValue("ХОДОВА ЧАСТ ЛАГЕРИ");
rowHeader.CreateCell(53).SetCellValue(protocol.HCLageri);
rowHeader.CreateCell(54).SetCellValue("ХОДОВА ЧАСТ ЛАГЕРИ");
rowData.CreateCell(54).SetCellValue(protocol.HCLageriDaNe);
rowHeader.CreateCell(55).SetCellValue("ХОДОВА ЧАСТ СПИРАЧНА УРЕДБА");
rowData.CreateCell(55).SetCellValue(protocol.HCSpirachnaUredba);
rowHeader.CreateCell(56).SetCellValue("ХОДОВА ЧАСТ СПИРАЧНА УРЕДБА");
rowData.CreateCell(56).SetCellValue(protocol.HCSpirachnaUredbaDaNe);
rowHeader.CreateCell(57).SetCellValue("ХОДОВА ЧАСТ СТАБ. ЩАНГИ И ТАМПОНИ");
rowData.CreateCell(57).SetCellValue(protocol.HCStabilizirashtiShtangiITamponi);
rowHeader.CreateCell(58).SetCellValue("ХОДОВА ЧАСТ СТАБ. ЩАНГИ И ТАМПОНИ");
rowData.CreateCell(58).SetCellValue(protocol.HCStabilizirashtiShtangiITamponiDaNe);
rowHeader.CreateCell(59).SetCellValue("ХОДОВА ЧАСТ КОРМИЛНО УПРАВЛЕНИЕ");
rowData.CreateCell(59).SetCellValue(protocol.HCKormilnoUpravlenie);
rowHeader.CreateCell(60).SetCellValue("ХОДОВА ЧАСТ КОРМИЛНО УПРАВЛЕНИЕ");
rowData.CreateCell(60).SetCellValue(protocol.HCKormilnoUpravlenieDaNe);
rowHeader.CreateCell(61).SetCellValue("ХОДОВА ЧАСТ ГЕОМЕТРИЯ ПРЕДЕН И ЗАДЕН МОСТ");
rowData.CreateCell(61).SetCellValue(protocol.HCGeometriaPredenIZadenMost);
rowHeader.CreateCell(62).SetCellValue("ХОДОВА ЧАСТ ГЕОМЕТРИЯ ПРЕДЕН И ЗАДЕН МОСТ");
rowData.CreateCell(62).SetCellValue(protocol.HCGeometriaPredenIZadenMostDaNe);
rowHeader.CreateCell(63).SetCellValue("ХОДОВА ЧАСТ БЕАЛЕТИ");
rowData.CreateCell(63).SetCellValue(protocol.HCBealeti);
rowHeader.CreateCell(64).SetCellValue("ХОДОВА ЧАСТ БЕАЛЕТИ");
rowData.CreateCell(64).SetCellValue(protocol.HCBealetiDaNe);
rowHeader.CreateCell(65).SetCellValue("ХОДОВА ЧАСТ ДРУГИ");
rowData.CreateCell(65).SetCellValue(protocol.HCDrugi);
rowHeader.CreateCell(66).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА КЛИМАТИК");
rowData.CreateCell(66).SetCellValue(protocol.ESKlimatik);
rowHeader.CreateCell(67).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА КЛИМАТИК");
rowData.CreateCell(67).SetCellValue(protocol.ESKlimatikDaNe);
rowHeader.CreateCell(68).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА АКУМУЛАТОР");
rowData.CreateCell(68).SetCellValue(protocol.ESAkumulator);
rowHeader.CreateCell(69).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА АКУМУЛАТОР");
rowData.CreateCell(69).SetCellValue(protocol.ESAkumulatorDaNe);
rowHeader.CreateCell(70).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА СТАРТЕР И ГЕНЕРАТОР");
rowData.CreateCell(70).SetCellValue(protocol.ESStarterIGenerator);
rowHeader.CreateCell(69).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА СТАРТЕР И ГЕНЕРАТОР");
rowData.CreateCell(69).SetCellValue(protocol.ESStarterIGeneratorDaNe);
rowHeader.CreateCell(70).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА АРМАТУРНО ТАБЛО");
rowData.CreateCell(70).SetCellValue(protocol.ESArmaturnoTablo);
rowHeader.CreateCell(71).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА АРМАТУРНО ТАБЛО");
rowData.CreateCell(71).SetCellValue(protocol.ESArmaturnoTabloDaNe);
rowHeader.CreateCell(72).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА ЧИСТАЧКИ");
rowData.CreateCell(72).SetCellValue(protocol.ESChistachki);
rowHeader.CreateCell(73).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА ЧИСТАЧКИ");
rowData.CreateCell(73).SetCellValue(protocol.ESChistachkiDaNe);
rowHeader.CreateCell(74).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА СВЕТЛИНИ");
rowData.CreateCell(74).SetCellValue(protocol.ESSvetlini);
rowHeader.CreateCell(75).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА СВЕТЛИНИ");
rowData.CreateCell(75).SetCellValue(protocol.ESSvetliniDaNe);
rowHeader.CreateCell(76).SetCellValue("ЕЛЕКТРИЧЕСКА СИСТЕМА ДРУГИ");
rowData.CreateCell(76).SetCellValue(protocol.ESDrugi);
rowHeader.CreateCell(77).SetCellValue("ДРУГИ РЕКЛАМНА ТАБЕЛА");
rowData.CreateCell(77).SetCellValue(protocol.DRReklamnaTabela);
rowHeader.CreateCell(78).SetCellValue("ДРУГИ ВАЛИДНОСТ НА УТГ:");
rowData.CreateCell(78).SetCellValue(protocol.DRValidnostUTG);
rowHeader.CreateCell(79).SetCellValue("ИЗПРАВЕН");
rowData.CreateCell(79).SetCellValue(protocol.AvtomobilatEIzpraven ? "ДА" : "");
rowHeader.CreateCell(80).SetCellValue("НЕ ИЗПРАВЕН");
rowData.CreateCell(80).SetCellValue(protocol.AvtomobilatENeIzpraven ? "ДА" : "");
rowHeader.CreateCell(81).SetCellValue("МЕХАНИК");
rowData.CreateCell(81).SetCellValue(protocol.Mehanik);
rowHeader.CreateCell(82).SetCellValue("Вложени части");
rowData.CreateCell(82).SetCellValue(protocol.VlojeniChasti);
workbook.WriteExcelToResponse(HttpContext, fileName);
}
/// <summary>
/// 生成操作
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnCreate_Click(object sender, RoutedEventArgs e)
{
string checkResultStr = JiChangTool.CheckServer();
if (!string.IsNullOrEmpty(checkResultStr))
{
MessageBox.Show(checkResultStr, "提示");
return;
}
//最终整合的数据
List <FinalData> lstFinalData = new List <FinalData>();
var failDate = DateTime.Now.ToString("yyyyMMdd");
//判断输入内容
var filePath = txtSource.Text.Trim();
if (string.IsNullOrEmpty(filePath) || filePath == "文件夹位置")
{
MessageBox.Show("请选择源文件路径", "提示");
this.txtScreen.Text += $"\r\n请选择源文件路径\r\n";
return;
}
//生成Excel
this.txtScreen.Text += $"工作中...\r\n";
//this.txtScreen.Text += $"竭尽全力...>_<.....\r\n";
//this.txtScreen.Text += $"为了静香...>_<.....\r\n";
//this.txtScreen.Text += $"...>_<.....\r\n";
//获取文件
DirectoryInfo root = new DirectoryInfo(filePath);
FileInfo[] files = root.GetFiles();
List <DataTable> lstDataTable = new List <DataTable>();
foreach (var csvFile in files)
{
DataTable dt = new DataTable();
if (csvFile.Name.ToLower().Contains("csv"))
{
var result = CSVHelper.OpenCSVFile(ref dt, csvFile.FullName);
if (result)
{
lstDataTable.Add(dt);
}
}
}
//循环操作
string TempletFileName = System.IO.Path.Combine(System.IO.Directory.GetCurrentDirectory(), "ExcelTemp.xlsx");
IWorkbook wk = null;
using (FileStream fs = File.Open(TempletFileName, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xlsx文件读入workbook变量里，之后就可以关闭了
wk = new XSSFWorkbook(fs);
fs.Close();
}
foreach (var dataTable in lstDataTable)
{
//获取所有车牌号集合
List <string> lstCarNo = new List <string>();
//获取所有地标集合（xxx鸡场，加工xxx,圣农车队）
List <string> lstAddr = new List <string>();
var ChickenFarm = "";//养鸡场名称
foreach (DataRow row in dataTable.Rows)
{
var carNo = row[0] + "";
var addr = row[1] + "";
if (!lstCarNo.Contains(carNo))
{
if (carNo != "车牌号码")
{
lstCarNo.Add(carNo);
}
}
if (!lstAddr.Contains(addr))
{
if (addr != "地标名称")
{
lstAddr.Add(addr);
}
if (string.IsNullOrEmpty(ChickenFarm) && addr.Contains("鸡场"))
{
ChickenFarm = addr;
}
}
}
this.txtScreen.Text += $"...…^_<.....\r\n";
//循环找到这个车牌所有的数据
InOutRecode inOutRecode1 = new InOutRecode()
{
PlantName = "加工一厂"
};
InOutRecode inOutRecode2 = new InOutRecode()
{
PlantName = "加工二厂"
};
foreach (var itemCarNo in lstCarNo)
{
//col1=车牌号码
//col2=地标名称
//col3=进入时间DatetimeL类型
//col4=离开时间DatetimeL类型
//根据车牌号筛选，根据入场时间排序得到数据
var thisCarNoRows = dataTable.Select($"col1='{itemCarNo}'", " col3 asc");
//计算离开时间和回来时间，都为圣农车队
DateTime?DepartureTime = null; //离开时间
DateTime?ReturnTime = null; //返回时间
#region 计算离开时间
for (int i = 0; i < thisCarNoRows.Length; i++)
{
if (i + 1 < thisCarNoRows.Length)
{
if (thisCarNoRows[i]["col2"].ToString() == "圣农车队")
{
if (thisCarNoRows[i + 1]["col2"].ToString() != "圣农车队")
{
DepartureTime = ((DateTime)thisCarNoRows[i]["col4"]);
break;
}
}
}
else
{
DepartureTime = ((DateTime)thisCarNoRows[i]["col4"]);
}
}
#endregion
#region 计算返回时间
for (int i = thisCarNoRows.Length - 1; i >= 0; i--)
{
if (i - 1 > 0)
{
if (thisCarNoRows[i]["col2"].ToString() == "圣农车队")
{
if (thisCarNoRows[i - 1]["col2"].ToString() != "圣农车队")
{
ReturnTime = ((DateTime)thisCarNoRows[i]["col3"]);
break;
}
}
}
else
{
ReturnTime = ((DateTime)thisCarNoRows[i]["col3"]);
}
}
#endregion
//循环添加数据吧，路线车队-》鸡场-》加工厂-》鸡场-》加工厂。。。。。-》车队
var selectThisCarNoRows = thisCarNoRows.Where(o => o["col2"] + "" != "圣农车队").ToList();//排除圣农车队和加工二厂的数据
FinalData finalData = new FinalData()
{
CarNo = itemCarNo,
DepartureTime = DepartureTime,
ReturnTime = ReturnTime,
Slaughterhouse = "加工一厂",
ChickenFarm = ChickenFarm,
Come2 = DateTime.Today, //默认值
Out2 = DateTime.Today //默认值
};
for (int i = 0; i < selectThisCarNoRows.Count; i++)
{
if (i + 1 < selectThisCarNoRows.Count)
{
//进入鸡场时间记录
if (selectThisCarNoRows[i]["col2"].ToString().Contains("鸡场") && (i == 0 || !selectThisCarNoRows[i - 1]["col2"].ToString().Contains("鸡场")))
{
finalData.Come1 = (DateTime)selectThisCarNoRows[i]["col3"];
}
//出去鸡场时间记录
if (selectThisCarNoRows[i]["col2"].ToString().Contains("鸡场") && (i == selectThisCarNoRows.Count - 1 || !selectThisCarNoRows[i + 1]["col2"].ToString().Contains("鸡场")))
{
finalData.Out1 = (DateTime)selectThisCarNoRows[i]["col4"];
}
//进入加工厂记录
#region 之前记录
//if(i > 0)
//{
// if (selectThisCarNoRows[i]["col2"].ToString().Contains("加工") && (!selectThisCarNoRows[i - 1]["col2"].ToString().Contains("加工")))
// {
// finalData.Come2 = (DateTime)selectThisCarNoRows[i]["col3"];
// }
//}
#endregion
#region 改造后记录，把加工厂数据全部存储起来
if (selectThisCarNoRows[i]["col2"].ToString().Contains("加工一厂"))
{
inOutRecode1.InTime.Add((DateTime)selectThisCarNoRows[i]["col3"]);
inOutRecode1.OutTime.Add((DateTime)selectThisCarNoRows[i]["col4"]);
}
if (selectThisCarNoRows[i]["col2"].ToString().Contains("加工二厂"))
{
inOutRecode2.InTime.Add((DateTime)selectThisCarNoRows[i]["col3"]);
inOutRecode2.OutTime.Add((DateTime)selectThisCarNoRows[i]["col4"]);
}
#endregion
//出去加工厂记录，不包含最后一条
if (selectThisCarNoRows[i]["col2"].ToString().Contains("加工") && (!selectThisCarNoRows[i + 1]["col2"].ToString().Contains("加工")))
{
#region 原来记录
//finalData.Out2 = (DateTime)selectThisCarNoRows[i]["col4"];
////把记录添加了再重新New一个FinalData对象
//lstFinalData.Add(finalData);
//finalData = new FinalData
//{
// CarNo = itemCarNo,
// DepartureTime = DepartureTime,
// ReturnTime = ReturnTime,
// Slaughterhouse = "加工一厂",
// ChickenFarm = ChickenFarm
//};
#endregion
//改造记录
#region 进出时间计算最大值
//1厂进出时间
var inOutRecode1Come = DateTime.Today;
var inOutRecode1Out = DateTime.Today;
if (inOutRecode1.InTime.Count > 0)
{
inOutRecode1Come = inOutRecode1.InTime.OrderBy(o => o).FirstOrDefault();
inOutRecode1Out = inOutRecode1.OutTime.OrderByDescending(o => o).FirstOrDefault();
}
//2场进出时间
var inOutRecode2Come = DateTime.Today;
var inOutRecode2Out = DateTime.Today;
if (inOutRecode2.InTime.Count > 0)
{
inOutRecode2Come = inOutRecode2.InTime.OrderBy(o => o).FirstOrDefault();
inOutRecode2Out = inOutRecode2.OutTime.OrderByDescending(o => o).FirstOrDefault();
}
#endregion
//说明有2厂的数据
if (inOutRecode2Come != DateTime.Today && inOutRecode2Out != DateTime.Today)
{
//有2厂数据且大于30分钟，就认为是2厂的数据
if (inOutRecode2Out - inOutRecode2Come > TimeSpan.FromMinutes(20))
{
finalData.Out2 = inOutRecode2Out;
finalData.Come2 = inOutRecode2Come;
finalData.Slaughterhouse = "加工二厂";
lstFinalData.Add(finalData);
//归零
finalData = new FinalData
{
CarNo = itemCarNo,
DepartureTime = DepartureTime,
ReturnTime = ReturnTime,
Slaughterhouse = "加工一厂",
ChickenFarm = ChickenFarm,
Come2 = DateTime.Today, //默认值
Out2 = DateTime.Today //默认值
};
inOutRecode1.InTime = new List <DateTime>();
inOutRecode1.OutTime = new List <DateTime>();
inOutRecode2.InTime = new List <DateTime>();
inOutRecode2.OutTime = new List <DateTime>();
continue;
}
}
//2厂没数据就是1厂的东西了
finalData.Out2 = inOutRecode1Out;
finalData.Come2 = inOutRecode1Come;
lstFinalData.Add(finalData);
//归零
finalData = new FinalData
{
CarNo = itemCarNo,
DepartureTime = DepartureTime,
ReturnTime = ReturnTime,
Slaughterhouse = "加工一厂",
ChickenFarm = ChickenFarm,
Come2 = DateTime.Today, //默认值
Out2 = DateTime.Today //默认值
};
inOutRecode1.InTime = new List <DateTime>();
inOutRecode1.OutTime = new List <DateTime>();
inOutRecode2.InTime = new List <DateTime>();
inOutRecode2.OutTime = new List <DateTime>();
}
}
else
{
//最后一条记录，如果是鸡场而不是加工厂也把记录添加了
//最后一条记录是鸡场的话，那就没有加工厂的进出时间
if (selectThisCarNoRows[i]["col2"].ToString().Contains("鸡场"))
{
if (finalData.Come1 == null)
{
finalData.Come1 = (DateTime)selectThisCarNoRows[i]["col3"];
}
finalData.Out1 = (DateTime)selectThisCarNoRows[i]["col4"];
}
//正常的加工厂最后一条记录
else if (selectThisCarNoRows[i]["col2"].ToString().Contains("加工"))
{
//if (finalData.Come2 == null)
//{
// finalData.Come2 = (DateTime)selectThisCarNoRows[i]["col3"];
//}
//finalData.Out2 = (DateTime)selectThisCarNoRows[i]["col4"];
#region 改造后记录，把加工厂数据全部存储起来
if (selectThisCarNoRows[i]["col2"].ToString().Contains("加工一厂"))
{
inOutRecode1.InTime.Add((DateTime)selectThisCarNoRows[i]["col3"]);
inOutRecode1.OutTime.Add((DateTime)selectThisCarNoRows[i]["col4"]);
}
if (selectThisCarNoRows[i]["col2"].ToString().Contains("加工二厂"))
{
inOutRecode2.InTime.Add((DateTime)selectThisCarNoRows[i]["col3"]);
inOutRecode2.OutTime.Add((DateTime)selectThisCarNoRows[i]["col4"]);
}
#endregion
#region 进出时间计算最大值
//1厂进出时间
var inOutRecode1Come = DateTime.Today;
var inOutRecode1Out = DateTime.Today;
if (inOutRecode1.InTime.Count > 0)
{
inOutRecode1Come = inOutRecode1.InTime.OrderBy(o => o).FirstOrDefault();
inOutRecode1Out = inOutRecode1.OutTime.OrderByDescending(o => o).FirstOrDefault();
}
//2场进出时间
var inOutRecode2Come = DateTime.Today;
var inOutRecode2Out = DateTime.Today;
if (inOutRecode2.InTime.Count > 0)
{
inOutRecode2Come = inOutRecode2.InTime.OrderBy(o => o).FirstOrDefault();
inOutRecode2Out = inOutRecode2.OutTime.OrderByDescending(o => o).FirstOrDefault();
}
#endregion
#region 计算1，2厂
if (inOutRecode2Come != DateTime.Today && inOutRecode2Out != DateTime.Today)
{
//有2厂数据且大于30分钟，就认为是2厂的数据
if (inOutRecode2Out - inOutRecode2Come > TimeSpan.FromMinutes(20))
{
finalData.Out2 = inOutRecode2Out;
finalData.Come2 = inOutRecode2Come;
finalData.Slaughterhouse = "加工二厂";
lstFinalData.Add(finalData);
//归零
finalData = new FinalData
{
CarNo = itemCarNo,
DepartureTime = DepartureTime,
ReturnTime = ReturnTime,
Slaughterhouse = "加工一厂",
ChickenFarm = ChickenFarm,
Come2 = DateTime.Today, //默认值
Out2 = DateTime.Today //默认值
};
inOutRecode1.InTime = new List <DateTime>();
inOutRecode1.OutTime = new List <DateTime>();
inOutRecode2.InTime = new List <DateTime>();
inOutRecode2.OutTime = new List <DateTime>();
continue;
}
}
//2厂没数据就是1厂的东西了
finalData.Out2 = inOutRecode1Out;
finalData.Come2 = inOutRecode1Come;
//归零
inOutRecode1.InTime = new List <DateTime>();
inOutRecode1.OutTime = new List <DateTime>();
inOutRecode2.InTime = new List <DateTime>();
inOutRecode2.OutTime = new List <DateTime>();
#endregion
}
lstFinalData.Add(finalData);
}
}
}
}
var data = lstFinalData;
//this.txtScreen.Text += $"...>_^....\r\n";
this.txtScreen.Text += $"选择保存位置\r\n";
//Thread.Sleep(3000);
//保存对话框
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.InitialDirectory = this.txtSource.Text;
saveFileDialog1.FileName = $"{DateTime.Now.ToString("yyyy-MM-dd")}数据.xlsx";
saveFileDialog1.Filter = "ext files (*.xlsx)|*.xlsx|All files(*.*)|*>**";
saveFileDialog1.FilterIndex = 2;
saveFileDialog1.RestoreDirectory = true;
var dr = saveFileDialog1.ShowDialog();
if ((bool)dr && saveFileDialog1.FileName.Length > 0)
{
//判断目录是否存在
if (!Directory.Exists(System.IO.Path.GetDirectoryName(saveFileDialog1.FileName)))
{
Directory.CreateDirectory(System.IO.Path.GetDirectoryName(saveFileDialog1.FileName));
}
var savaPath = saveFileDialog1.FileName;
using (FileStream fs = File.Open(TempletFileName, FileMode.Open, FileAccess.Read))
{
wk = new XSSFWorkbook(fs);
fs.Close();
ISheet sheet = null;
sheet = wk.GetSheetAt(0);
IRow row = null;
int rowCount = sheet.LastRowNum + 1;
int j = 1;
for (int i = 0; i < lstFinalData.Count; i++)
{
var item = lstFinalData[i];
row = sheet.CreateRow(rowCount);
//row.CreateCell(0).SetCellValue(j++ +"");
row.CreateCell(1).SetCellValue(item.CarNo);
if (item.DepartureTime > item.ReturnTime)
{
row.CreateCell(2).SetCellValue("无");
}
else
{
row.CreateCell(2).SetCellValue(((DateTime)item.DepartureTime).ToString("H:mm:ss"));
}
//返回时间判断
if ((bool)this.cekJZ.IsChecked)
{
var _cell = row.CreateCell(3);
_cell.SetCellValue(((DateTime)item.ReturnTime).ToString("H:mm:ss"));
var lastOut2DateTime = (DateTime)item.Out2;
var timeSpanSub = BLL.JiChangTool.GetReturnTimeSubLastOut2(i, lstFinalData, ref lastOut2DateTime);
//在1个小时之内
if (timeSpanSub > TimeSpan.FromMinutes(18) && timeSpanSub <= TimeSpan.FromMinutes(60))
{
//介于18分钟到60分钟之间，变成黄色，修改时间
ICellStyle style = wk.CreateCellStyle(); //创建单元格样式
style.FillForegroundColor = HSSFColor.Yellow.Index; //设置单元格样式中的样式
style.FillPattern = FillPattern.SolidForeground;
_cell.SetCellValue(lastOut2DateTime.AddMinutes(18).ToString("H:mm:ss"));
_cell.CellStyle = style;//为单元格设置显示样式
}
else if (timeSpanSub > TimeSpan.FromMinutes(60))
{
//大于1个小时，变成红色
ICellStyle style = wk.CreateCellStyle();//创建单元格样式
style.FillForegroundColor = HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
_cell.CellStyle = style;//为单元格设置显示样式
}
}
else
{
row.CreateCell(3).SetCellValue(((DateTime)item.ReturnTime).ToString("H:mm:ss"));
}
row.CreateCell(4).SetCellValue(item.ChickenFarm);
row.CreateCell(5).SetCellValue(item.Slaughterhouse);
if (item.Come1 != DateTime.Today && item.Come1 != null)
{
var _cell = row.CreateCell(6);
_cell.SetCellValue(((DateTime)item.Come1).ToString("H:mm:ss"));
if (i > 1)
{
if (lstFinalData[i - 1].CarNo == item.CarNo)
{
if (lstFinalData[i - 1].Out2 != null)
{
if ((DateTime)item.Come1 - (DateTime)lstFinalData[i - 1].Out2 <= TimeSpan.FromMinutes(15))
{
ICellStyle style = wk.CreateCellStyle(); //创建单元格样式
style.FillForegroundColor = HSSFColor.Red.Index; //设置单元格样式中的样式
style.FillPattern = FillPattern.SolidForeground;
_cell.CellStyle = style; //为单元格设置显示样式
}
}
}
}
}
else
{
row.CreateCell(6).SetCellValue("无");
}
if (item.Out1 != DateTime.Today && item.Out1 != null)
{
//小于15分钟的标成黄色警告
var _cell = row.CreateCell(7);
_cell.SetCellValue(((DateTime)item.Out1).ToString("H:mm:ss"));
if (item.Come1 != null)
{
if ((DateTime)item.Out1 - (DateTime)item.Come1 <= TimeSpan.FromMinutes(15))
{
ICellStyle style = wk.CreateCellStyle(); //创建单元格样式
style.FillForegroundColor = HSSFColor.Red.Index; //设置单元格样式中的样式
style.FillPattern = FillPattern.SolidForeground;
_cell.CellStyle = style; //为单元格设置显示样式
}
if ((DateTime)item.Out1 - (DateTime)item.Come1 >= TimeSpan.FromHours(3))
{
ICellStyle style = wk.CreateCellStyle(); //创建单元格样式
style.FillForegroundColor = HSSFColor.Red.Index; //设置单元格样式中的样式
style.FillPattern = FillPattern.SolidForeground;
_cell.CellStyle = style; //为单元格设置显示样式
}
}
}
else
{
row.CreateCell(7).SetCellValue("无");
}
if (item.Come2 != DateTime.Today && item.Come2 != null)
{
var _cell = row.CreateCell(8);
_cell.SetCellValue(((DateTime)item.Come2).ToString("H:mm:ss"));
if (item.Out1 != null)
{
if ((DateTime)item.Come2 - (DateTime)item.Out1 <= TimeSpan.FromMinutes(20))
{
ICellStyle style = wk.CreateCellStyle(); //创建单元格样式
style.FillForegroundColor = HSSFColor.Red.Index; //设置单元格样式中的样式
style.FillPattern = FillPattern.SolidForeground;
_cell.CellStyle = style; //为单元格设置显示样式
}
if ((DateTime)item.Come2 - (DateTime)item.Out1 >= TimeSpan.FromMinutes(120))
{
ICellStyle style = wk.CreateCellStyle(); //创建单元格样式
style.FillForegroundColor = HSSFColor.Red.Index; //设置单元格样式中的样式
style.FillPattern = FillPattern.SolidForeground;
_cell.CellStyle = style; //为单元格设置显示样式
}
}
}
else
{
row.CreateCell(8).SetCellValue("无");
}
if (item.Out2 != DateTime.Today && item.Out2 != null)
{
//校正按钮开启
if ((bool)this.cekJZ.IsChecked)
{
var _cell = row.CreateCell(9);
_cell.SetCellValue(((DateTime)item.Out2).ToString("H:mm:ss"));
if ((DateTime)item.Out2 - (DateTime)item.Come2 <= TimeSpan.FromMinutes(15))
{
ICellStyle style = wk.CreateCellStyle(); //创建单元格样式
style.FillForegroundColor = HSSFColor.Red.Index; //设置单元格样式中的样式
style.FillPattern = FillPattern.SolidForeground;
_cell.CellStyle = style; //为单元格设置显示样式
}
if ((DateTime)item.Out2 - (DateTime)item.Come2 >= TimeSpan.FromHours(3))
{
ICellStyle style = wk.CreateCellStyle(); //创建单元格样式
style.FillForegroundColor = HSSFColor.Red.Index; //设置单元格样式中的样式
style.FillPattern = FillPattern.SolidForeground;
_cell.CellStyle = style; //为单元格设置显示样式
}
#region 原来判断修改的是最后一次加工厂时间，注释了
//归来时间大于最后一次出加工厂时间18分钟，修改最后一次出加工厂时间
//if (i < lstFinalData.Count - 1)
//{
// if (item.CarNo != lstFinalData[i + 1].CarNo)
// {
// #region 时间判断
// var timeSpanSub = (DateTime)item.ReturnTime - (DateTime)item.Out2;
// //在1个小时之内
// if (timeSpanSub > TimeSpan.FromMinutes(18) && timeSpanSub <= TimeSpan.FromMinutes(60))
// {
// //介于18分钟到60分钟之间，变成黄色，修改时间
// ICellStyle style = wk.CreateCellStyle();//创建单元格样式
// style.FillForegroundColor = HSSFColor.Yellow.Index;//设置单元格样式中的样式
// style.FillPattern = FillPattern.SolidForeground;
// var _cell = row.CreateCell(9);
// _cell.SetCellValue(((DateTime)item.ReturnTime).AddMinutes(-18).ToString("H:mm:ss"));
// _cell.CellStyle = style;//为单元格设置显示样式
// }
// else if (timeSpanSub > TimeSpan.FromMinutes(60))
// {
// //大于1个小时，变成红色
// ICellStyle style = wk.CreateCellStyle();//创建单元格样式
// style.FillForegroundColor = HSSFColor.Red.Index;
// style.FillPattern = FillPattern.SolidForeground;
// var _cell = row.CreateCell(9);
// row.CreateCell(9).SetCellValue(((DateTime)item.Out2).ToString("H:mm:ss"));
// _cell.CellStyle = style;//为单元格设置显示样式
// }
// else
// {
// //不变
// row.CreateCell(9).SetCellValue(((DateTime)item.Out2).ToString("H:mm:ss"));
// }
// #endregion
// }
// else
// {
// row.CreateCell(9).SetCellValue(((DateTime)item.Out2).ToString("H:mm:ss"));
// }
//}
//else if (i == lstFinalData.Count-1)
//{
// #region 时间判断
// var timeSpanSub = (DateTime)item.ReturnTime - (DateTime)item.Out2;
// //在1个小时之内
// if (timeSpanSub > TimeSpan.FromMinutes(18) && timeSpanSub <= TimeSpan.FromMinutes(60))
// {
// //介于18分钟到60分钟之间，变成黄色，修改时间
// ICellStyle style = wk.CreateCellStyle();//创建单元格样式
// style.FillForegroundColor = HSSFColor.Yellow.Index;//设置单元格样式中的样式
// style.FillPattern = FillPattern.SolidForeground;
// var _cell = row.CreateCell(9);
// _cell.SetCellValue(((DateTime)item.ReturnTime).AddMinutes(-18).ToString("H:mm:ss"));
// _cell.CellStyle = style;//为单元格设置显示样式
// }
// else if (timeSpanSub > TimeSpan.FromMinutes(60))
// {
// //大于1个小时，变成红色
// ICellStyle style = wk.CreateCellStyle();//创建单元格样式
// style.FillForegroundColor = HSSFColor.Red.Index;
// style.FillPattern = FillPattern.SolidForeground;
// var _cell = row.CreateCell(9);
// row.CreateCell(9).SetCellValue(((DateTime)item.Out2).ToString("H:mm:ss"));
// _cell.CellStyle = style;//为单元格设置显示样式
// }
// else
// {
// //不变
// row.CreateCell(9).SetCellValue(((DateTime)item.Out2).ToString("H:mm:ss"));
// }
// #endregion
//}
#endregion
}
else
{
row.CreateCell(9).SetCellValue(((DateTime)item.Out2).ToString("H:mm:ss"));
}
}
else
{
row.CreateCell(9).SetCellValue("无");
}
rowCount++;
}
sheet.ForceFormulaRecalculation = true;
using (FileStream filess = File.OpenWrite(savaPath))
{
wk.Write(filess);
this.txtScreen.Text += $"导出完成\r\n";
MessageBox.Show("导出完成", "提示");
Process.Start(System.IO.Path.GetDirectoryName(savaPath));
}
}
}
}
public FileResult ImportToExcel(string park, string stime, string etime, int model)
{
try
{
HSSFWorkbook book = new HSSFWorkbook();
string fileName = string.Empty;
if (model == 1)
{
//汇总数据导出
fileName = "会议室出租率汇总报表-" + DateTime.Now.ToString("yyyyMMddHHmmsss");
if (string.IsNullOrEmpty(stime) || string.IsNullOrEmpty(etime))
{
stime = DateTime.Now.ToString("yyyy-MM-dd");
etime = DateTime.Now.ToString("yyyy-MM-dd");
}
List <SqlParameter> spList = new List <SqlParameter>
{
new SqlParameter("Model", 1),
new SqlParameter("TimeSplit", 8),
new SqlParameter("BeginTime", stime),
new SqlParameter("EndTime", etime),
new SqlParameter("Account", user.Account)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_StatisticsCR", CommandType.StoredProcedure, spList.ToArray());
//工作簿
DataRow[] rows = string.IsNullOrEmpty(park) ? ds.Tables[0].Select() : ds.Tables[0].Select(string.Format("ParkID='{0}'", park));
ISheet sheet = book.CreateSheet("出租率汇总数据");
//条件列
IRow condition = sheet.CreateRow(0);
condition.CreateCell(0).SetCellValue("园区：");
condition.CreateCell(1).SetCellValue(string.IsNullOrEmpty(park) ? "全部" : rows[0]["ParkName"].ToString());
condition.CreateCell(2).SetCellValue("开始时间：");
condition.CreateCell(3).SetCellValue(stime);
condition.CreateCell(4).SetCellValue("结束时间：");
condition.CreateCell(5).SetCellValue(etime);
//第一行：标题行
IRow title = sheet.CreateRow(1);
title.CreateCell(0).SetCellValue("园区");
title.CreateCell(1).SetCellValue("会议室名称");
title.CreateCell(2).SetCellValue("总可用小时数");
title.CreateCell(3).SetCellValue("客户租赁小时数");
title.CreateCell(4).SetCellValue("内部使用小时数");
title.CreateCell(5).SetCellValue("空置小时数");
title.CreateCell(6).SetCellValue("出租率");
title.CreateCell(7).SetCellValue("内部使用率");
title.CreateCell(8).SetCellValue("空置率");
var rowIndex = 2;
//内容
foreach (var item in rows)
{
IRow row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(item["ParkName"].ToString());
row.CreateCell(1).SetCellValue(item["Name"].ToString());
row.CreateCell(2).SetCellValue(item["Total"].ToString());
row.CreateCell(3).SetCellValue(item["Rent"].ToString());
row.CreateCell(4).SetCellValue(item["Self"].ToString());
row.CreateCell(5).SetCellValue(item["Free"].ToString());
row.CreateCell(6).SetCellValue(item["RentRate"].ToString() + "%");
row.CreateCell(7).SetCellValue(item["SelfRate"].ToString() + "%");
row.CreateCell(8).SetCellValue(item["FreeRate"].ToString() + "%");
rowIndex++;
}
}
else
{
//趋势图数据导出
fileName = "会议室出租率趋势报表-" + DateTime.Now.ToString("yyyyMMddHHmmsss");
if (string.IsNullOrEmpty(stime) || string.IsNullOrEmpty(etime))
{
stime = DateTime.Now.ToString("yyyy-MM") + "-01";
etime = DateTime.Now.ToString("yyyy-MM") + "-01";
}
else
{
stime = stime + "-01";
etime = etime + "-01";
}
List <SqlParameter> spList = new List <SqlParameter>
{
new SqlParameter("Model", 2),
new SqlParameter("TimeSplit", 8),
new SqlParameter("BeginTime", stime),
new SqlParameter("EndTime", etime),
new SqlParameter("Account", user.Account)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_StatisticsCR", CommandType.StoredProcedure, spList.ToArray());
var result = JsonConvert.DeserializeObject <List <StatisticsMonthView> >(JsonConvert.SerializeObject(ds.Tables[0]));
if (!string.IsNullOrEmpty(park))
{
result = result.Where(a => a.ParkID == park).ToList();
}
//工作簿
ISheet sheet = book.CreateSheet("出租率趋势数据");
//第一行：标题行
var column = 2;
IRow monthTitle = sheet.CreateRow(0);
monthTitle.CreateCell(0).SetCellValue("园区");
monthTitle.CreateCell(1).SetCellValue("会议室");
var month = result.GroupBy(a => a.MonthTime).OrderBy(a => a.Key).Select(a => a.Key).ToList();
foreach (var item in month)
{
monthTitle.CreateCell(column).SetCellValue(item);
column++;
}
//内容
var row = 1;
var monthPark = result.GroupBy(a => new { a.ID, a.Name, a.ParkName }).Select(a => new { a.Key.ID, a.Key.Name, a.Key.ParkName }).ToList();
foreach (var item in monthPark)
{
IRow dataRow = sheet.CreateRow(row);
dataRow.CreateCell(0).SetCellValue(item.ParkName);
dataRow.CreateCell(1).SetCellValue(item.Name);
column = 2;
var monthData = result.Where(a => a.ID == item.ID).OrderBy(a => a.MonthTime).ToList();
foreach (var data in monthData)
{
dataRow.CreateCell(column).SetCellValue(data.RentRate + "%");
column++;
}
row++;
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", fileName + ".xls"));
}
catch (Exception)
{
return(null);
}
}
public void WriteToExcel(string filePath)
{
//創建工作薄
IWorkbook wb;
string extension = System.IO.Path.GetExtension(filePath);
//根據指定的文件格式創建對應的類
if (extension.Equals(".xls"))
{
// 2003
wb = new HSSFWorkbook();
}
else
{
// 2007
wb = new XSSFWorkbook();
}
ICellStyle style1 = wb.CreateCellStyle(); //樣式
style1.Alignment = HorizontalAlignment.Left; //文字水平對齊方式
style1.VerticalAlignment = VerticalAlignment.Center; //文字垂直對齊方式
//設置邊框
style1.BorderBottom = BorderStyle.Thin;
style1.BorderLeft = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
style1.WrapText = true; //自動換行
ICellStyle style2 = wb.CreateCellStyle(); //樣式
IFont font1 = wb.CreateFont(); //字體
font1.FontHeightInPoints = 12; // 字體尺寸
font1.FontName = "楷體";
font1.Color = HSSFColor.Red.Index; //字體顏色
font1.Boldweight = (short)FontBoldWeight.Normal; //字體加粗樣式
style2.SetFont(font1); //樣式裏的字體設置具體的字體樣式
//設置背景色
style2.FillForegroundColor = HSSFColor.Yellow.Index;
style2.FillPattern = FillPattern.SolidForeground;
style2.FillBackgroundColor = HSSFColor.Yellow.Index;
style2.Alignment = HorizontalAlignment.Left; //文字水平對齊方式
style2.VerticalAlignment = VerticalAlignment.Center; //文字垂直對齊方式
ICellStyle dateStyle = wb.CreateCellStyle(); //樣式
dateStyle.Alignment = HorizontalAlignment.Left; //文字水平對齊方式
dateStyle.VerticalAlignment = VerticalAlignment.Center; //文字垂直對齊方式
//設置數據顯示格式
IDataFormat dataFormatCustom = wb.CreateDataFormat();
dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
//創建一個表單
ISheet sheet = wb.CreateSheet("Sheet0");
//設置列寬
int[] columnWidth = { 10, 10, 20, 10 };
for (int i = 0; i < columnWidth.Length; i++)
{
//設置列寬度，256*字符數，因為單位是1/256個字符
sheet.SetColumnWidth(i, 256 * columnWidth[i]);
}
//測試數據
int rowCount = 3, columnCount = 4;
object[,] data =
{
{ "列0", "列1", "列2", "列3" },
{ "", 400, 5.2, 6.01 },
{ "", true, "2014-07-02", DateTime.Now }
//日期可以直接傳字符串，NPOI會自動識別
//如果是DateTime類型，則要設置CellStyle.DataFormat，否則會顯示為數字
};
IRow row;
ICell cell;
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i); //創建第i行
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j); //創建第j列
cell.CellStyle = j % 2 == 0 ? style1 : style2;
//根據數據類型設置不同類型的cell
object obj = data[i, j];
SetCellValue(cell, data[i, j]);
//如果是日期，則設置日期顯示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根據內容自動調整列寬，需要先setCellValue再調用
//sheet.AutoSizeColumn(j);
}
}
//合並單元格，如果要合並的單元格中都有數據，只會保留左上角的
//CellRangeAddress(0, 2, 0, 0)，合並0-2行，0-0列的單元格
CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
sheet.AddMergedRegion(region);
try
{
FileStream fs = File.OpenWrite(filePath);
wb.Write(fs); //向打開的這個Excel文件中寫入表單並保存。
fs.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public static IWorkbook Serialize <T>(T[] items, Stream stream, bool exists)
{
var workbook = exists ? new XSSFWorkbook(stream) : new XSSFWorkbook();
Type t = typeof(T);
ISheet sheet = workbook.GetSheet(t.Name) ?? workbook.CreateSheet(t.Name);
//Create columns
if (!exists)
{
sheet.DefaultColumnWidth = 30;
IRow excelRow = sheet.CreateRow(0);
ICellStyle headerCellStyle = excelRow.Sheet.Workbook.CreateCellStyle();
headerCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
headerCellStyle.BorderBottom = BorderStyle.Medium;
headerCellStyle.FillPattern = FillPattern.SolidForeground;
foreach (PropertyInfo prop in t.GetProperties())
{
ICell cell = excelRow.CreateCell(excelRow.Cells.Count, CellType.String);
cell.SetCellValue(prop.Name);
ICellStyle style = cell.Row.Sheet.Workbook.CreateCellStyle();
style.FillBackgroundColor = HSSFColor.PaleBlue.Index;
cell.CellStyle = style;
cell.CellStyle = headerCellStyle;
}
}
var getters = t.GetProperties().Select(x => x.GetGetMethod()).ToList();
var date_style = workbook.CreateCellStyle();
date_style.BorderBottom = BorderStyle.Thin;
date_style.BorderLeft = BorderStyle.Thin;
date_style.BorderTop = BorderStyle.Thin;
date_style.BorderRight = BorderStyle.Thin;
date_style.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
foreach (Object item in items)
{
IRow row = sheet.CreateRow(sheet.LastRowNum + 1);
foreach (var val in getters.Select(x => x.Invoke(item, null)))
{
var cell = row.CreateCell(row.Cells.Count);
if (val == null)
{
continue;
}
var valType = val.GetType();
if (valType == typeof(bool))
{
cell.SetCellValue((bool)val);
}
else if (valType == typeof(string))
{
cell.SetCellValue((string)val);
}
else if (valType == typeof(DateTime))
{
cell.SetCellValue((DateTime)val);
cell.CellStyle = date_style;
}
else if (valType == typeof(double) ||
valType == typeof(int) ||
valType == typeof(float) ||
valType == typeof(decimal))
{
cell.SetCellValue(Convert.ToDouble(val));
}
}
}
return(workbook);
}
public async Task <IActionResult> OnPostAsync(string savemore)
{
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = "Server=103.118.157.29;Port=5432;Database=attendance;User Id=postgres;Password=princetonhive@123;";
connection.Open();
int year = indianTime.Year;
int month = Convert.ToInt32(HttpContext.Session.GetString("amonth"));
string txtdate = "00-" + month + "-" + year;
string sql = "select name,timein,timeout,date from employee inner join login on login.username=employee.employeeid where timein IS NOT NULL and SUBSTRING(date,3,8)=SUBSTRING('" + txtdate + "',3,8) and employeeid='" + HttpContext.Session.GetString("ausername") + "' order by date";
NpgsqlCommand cmd = new NpgsqlCommand(sql, connection);
NpgsqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
Employeedata1 p = new Employeedata1();
p.Name = Convert.ToString(dr["name"].ToString());
p.Timein = Convert.ToString(dr["timein"].ToString());
p.Timeout = Convert.ToString(dr["timeout"].ToString());
string date1 = Convert.ToString(dr["date"].ToString());
DateTime tmpDate = DateTime.ParseExact(date1, "dd-MM-yyyy HH:mm", null);
p.Date = tmpDate.ToString("dd-MM-yyyy");
EmployeeData1.Add(p);
}
EmployeeData = EmployeeData1;
dr.Close();
connection.Close();
}
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = @"MonthlyAttendance.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using (var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write))
{
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Employee");
IRow row = excelSheet.CreateRow(0);
int i = 1;
row.CreateCell(0).SetCellValue("S.No");
row.CreateCell(1).SetCellValue("Name");
row.CreateCell(2).SetCellValue("Date");
row.CreateCell(3).SetCellValue("Time In");
row.CreateCell(4).SetCellValue("Time Out");
foreach (var segment in EmployeeData)
{
row = excelSheet.CreateRow(i);
row.CreateCell(0).SetCellValue(i);
row.CreateCell(1).SetCellValue(segment.Name);
row.CreateCell(2).SetCellValue(segment.Date);
row.CreateCell(3).SetCellValue(segment.Timein);
row.CreateCell(4).SetCellValue(segment.Timeout);
i++;
}
workbook.Write(fs);
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName));
}
catch (Exception ex)
{
throw ex;
}
return(Page());
}
/// <summary>
/// 电桥温度 - 记录
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static bool BridgeData(float data)
{
lock (_Locker)
{
FileStream fs = null;
HSSFWorkbook wb = null;
try
{
if (!File.Exists(_fileData))
{
File.Create(_fileData).Close();
Debug.WriteLine("数据文件 " + _fileData + " 不存在，新建该文件！");
fs = new FileStream(_fileData, FileMode.Create);
wb = new HSSFWorkbook();
wb.CreateSheet("实时温度");
wb.CreateSheet("电桥温度");
wb.CreateSheet("电导率数据");
ISheet sheet0 = wb.GetSheet("实时温度");
IRow rowSh0 = sheet0.GetRow(0);
rowSh0 = sheet0.CreateRow(0);
ICell cell0Sh0 = rowSh0.CreateCell(0, CellType.String);
cell0Sh0.SetCellValue("时间");
ICell cell1Sh0 = rowSh0.CreateCell(1, CellType.String);
cell1Sh0.SetCellValue("主槽温度值");
ISheet sheet1 = wb.GetSheet("电桥温度");
IRow rowSh1 = sheet1.GetRow(0);
rowSh1 = sheet1.CreateRow(0);
ICell cell0Sh1 = rowSh1.CreateCell(0, CellType.String);
cell0Sh1.SetCellValue("时间");
ICell cell1Sh1 = rowSh1.CreateCell(1, CellType.String);
cell1Sh1.SetCellValue("电桥温度值");
ISheet sheet2 = wb.GetSheet("电导率数据");
IRow rowSh2 = sheet2.CreateRow(0);
ICell cell0Sh2 = rowSh2.CreateCell(0, CellType.String);
cell0Sh2.SetCellValue("时间");
ICell cell1Sh2 = rowSh2.CreateCell(1, CellType.String);
cell1Sh2.SetCellValue("温度值");
ICell cell2Sh2 = rowSh2.CreateCell(2, CellType.String);
cell2Sh2.SetCellValue("电导率");
fs.Flush();
wb.Write(fs);
fs.Close();
fs.Dispose();
wb = null;
fs = null;
}
}
catch (Exception ex)
{
Debug.WriteLine("数据文件不存在，且重建失败！");
try { wb = null; fs.Close(); fs.Dispose(); fs = null; } catch { }
//throw new Exception("数据文件不存在，且重建失败！");
return(false);
}
FileStream fout = null;
try
{
// 读取流
fs = new FileStream(_fileData, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
POIFSFileSystem ps = new POIFSFileSystem(fs);
wb = new HSSFWorkbook(ps);
ISheet sheet = wb.GetSheet("电桥温度");
// 写入流
fout = new FileStream(_fileData, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);
// 在工作表结尾添加一行
IRow row = sheet.CreateRow(sheet.LastRowNum + 1);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue(DateTime.Now.ToString("HH:mm:ss"));
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue(data);
// 写入到 xls 文件
fout.Flush();
wb.Write(fout);
wb = null;
fout.Close();
fout = null;
}
catch (System.Exception ex)
{
Debug.WriteLine("电桥温度写入失败");
try { wb = null; fout.Close(); fout = null; } catch { }
return(false);
}
}
return(true);
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="title">自定义显示标题</param>
/// <param name="fileName"></param>
/// <param name="heard">显示列名</param>
/// <param name="titleHeight">标题行高</param>
/// <param name="heardHeight">列标题行高</param>
/// <param name="lineHeight">内容行高</param>
/// <param name="style">样式处理</param>
/// <param name="lineStyle">行样式处理</param>
/// <param name="heardAction">自定义标题</param>
/// <param name="filter">外部过滤列,true:过滤</param>
/// <param name="merged">创建单元格后处理(合并单元格)</param>
/// <param name="sign">生成完成后处理(签名)</param>
/// <param name="args">设置列宽</param>
public static void ToExcel <T>(List <T> list, string title, string fileName, bool heard = true,
short titleHeight = 42, short heardHeight = 20, short lineHeight = 19,
Action <IWorkbook, ICellStyle, ICellStyle, ICellStyle, ICellStyle> style = null,
Func <List <T>, ISheet, int> heardAction = null,
Func <T, IWorkbook, Tuple <ICellStyle, ICellStyle> > lineStyle = null,
Func <List <T>, T, string, bool> filter = null,
Action <List <T>, int, IRow, string, ICell> merged = null,
Action <ISheet> sign = null,
params int[] args)
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
IWorkbook weekBook = null;
if (Path.GetExtension(fileName) == ".xls")
{
weekBook = new HSSFWorkbook();
}
else if (Path.GetExtension(fileName) == ".xlsx")
{
weekBook = new XSSFWorkbook();
}
FileStream fs = null;
try
{
int count = 0;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
ISheet sheet = weekBook.CreateSheet("Sheet1");
var titleStyle = GetCellStyle(weekBook, CellStyle.Header);
var heardStyle = GetCellStyle(weekBook);
var defaultStyle = GetCellStyle(weekBook);
var numberStyle = GetCellStyle(weekBook, CellStyle.Number);
if (!title.IsNullOrEmpty())
{
IRow row = sheet.CreateRow(count++);
row.Height = (short)(titleHeight * 20);
CreateCell(row, 0, titleStyle, title);
}
var type = list.GenericType();
var properties = type.PropertiesCache();
style?.Invoke(weekBook, titleStyle, heardStyle, defaultStyle, numberStyle);
if (heardAction != null)
{
count += heardAction.Invoke(list, sheet);
}
if (heard) //写入DataTable的列名
{
IRow row = sheet.CreateRow(count++);
row.Height = (short)(heardHeight * 20);
foreach (var property in properties)
{
if (!property.IExcel())
{
continue;
}
if (filter != null && filter(list, default, property.Name))
{
continue;
}
var index = row.LastCellNum < 0 ? 0 : row.LastCellNum;
var cell = row.CreateCell(index);
cell.CellStyle = heardStyle;
cell.SetCellValue(property.Text());
sheet.SetColumnWidth(index, 20 * 256);
}
}
var length = 0;
foreach (var property in properties)
{
if (!property.IExcel())
{
continue;
}
if (filter != null && filter(list, default, property.Name))
{
continue;
}
length++;
}
if (length > 0)
{
if (args.Length > 0)
{
for (int i = 0, j = 0; i < length; i++, j++)
{
if (j >= args.Length)
{
j = 0;
}
sheet.SetColumnWidth(i, args[j] * 256);
}
}
if (!title.IsNullOrEmpty())
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, length - 1));
}
}
for (int i = 0; i < list.Count; ++i)
{
if (filter != null && filter(list, list[i], null))
{
continue;
}
IRow row = sheet.CreateRow(count++);
row.Height = (short)(lineHeight * 20);
foreach (var property in properties)
{
if (!property.IExcel())
{
continue;
}
if (filter != null && filter(list, list[i], property.Name))
{
continue;
}
var index = row.LastCellNum < 0 ? 0 : row.LastCellNum;
var dbType = property.PropertyType;
if (dbType.IsGenericType && Nullable.GetUnderlyingType(dbType) != null)
{
dbType = Nullable.GetUnderlyingType(dbType);
}
var tuple = lineStyle?.Invoke(list[i], weekBook);
if (dbType == typeof(double) || dbType == typeof(int))
{
var cell = CreateCell(row, index, tuple?.Item2 ?? numberStyle, property.GetValue(list[i]));
merged?.Invoke(list, i, row, property.Name, cell);
}
else
{
var cell = CreateCell(row, index, tuple?.Item1 ?? defaultStyle, property.GetValue(list[i]));
merged?.Invoke(list, i, row, property.Name, cell);
}
}
}
sign?.Invoke(sheet);
weekBook.Write(fs); //写入到excel
}
finally
{ if (fs != null)
{
fs.Close();
}
}
}
public void Test45338()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
int num0 = wb.NumberOfFonts;
ISheet s = wb.CreateSheet();
s.CreateRow(0);
s.CreateRow(1);
s.GetRow(0).CreateCell(0);
s.GetRow(1).CreateCell(0);
//default font
IFont f1 = wb.GetFontAt((short)0);
Assert.AreEqual((short)FontBoldWeight.Normal, f1.Boldweight);
// Check that asking for the same font
// multiple times gives you the same thing.
// Otherwise, our Tests wouldn't work!
Assert.AreSame(wb.GetFontAt((short)0), wb.GetFontAt((short)0));
// Look for a new font we have
// yet to add
Assert.IsNull(
wb.FindFont(
(short)FontBoldWeight.Bold, (short)123, (short)(22 * 20),
"Thingy", false, true, FontSuperScript.Sub, FontUnderlineType.Double
)
);
IFont nf = wb.CreateFont();
short nfIdx = nf.Index;
Assert.AreEqual(num0 + 1, wb.NumberOfFonts);
Assert.AreEqual(nf, wb.GetFontAt(nfIdx));
nf.Boldweight = (short)(FontBoldWeight.Bold);
nf.Color = (short)123;
nf.FontHeightInPoints = (short)22;
nf.FontName = ("Thingy");
nf.IsItalic = (false);
nf.IsStrikeout = (true);
nf.TypeOffset = FontSuperScript.Sub;
nf.Underline = FontUnderlineType.Double;
Assert.AreEqual(num0 + 1, wb.NumberOfFonts);
Assert.AreEqual(nf, wb.GetFontAt(nfIdx));
Assert.AreEqual(wb.GetFontAt(nfIdx), wb.GetFontAt(nfIdx));
Assert.IsTrue(wb.GetFontAt((short)0) != wb.GetFontAt(nfIdx));
// Find it now
Assert.IsNotNull(
wb.FindFont(
(short)FontBoldWeight.Bold, (short)123, (short)(22 * 20),
"Thingy", false, true, FontSuperScript.Sub, FontUnderlineType.Double
)
);
Assert.AreSame(nf,
wb.FindFont(
(short)FontBoldWeight.Bold, (short)123, (short)(22 * 20),
"Thingy", false, true, FontSuperScript.Sub, FontUnderlineType.Double
)
);
}
public static bool DgvToExcel(string fileName, DataGridView dgv)
{
#region 获取文件路径
//SaveFileDialog sfd = new SaveFileDialog();
////文件后缀列表
//sfd.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx ";
////默然路径是系统当前路径
//sfd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
//if (sfd.ShowDialog() == DialogResult.Cancel) return false;
//string fileName = sfd.FileName;
#endregion
//【1】基于NPOI创建工作簿和工作表对象
HSSFWorkbook hssf = new HSSFWorkbook(); //2007以下版本 .xls
XSSFWorkbook xssf = new XSSFWorkbook(); //2007以上版本 .xlsx
//根据不同的office版本创建不同的工作簿对象
//根据文件后缀名判断office版本，创建不同的工作簿对象
IWorkbook workBook = null;
string extension = Path.GetExtension(fileName);
if (extension == ".xls")
{
workBook = hssf;
}
else if (extension == ".xlsx")
{
workBook = xssf;
}
else
{
//文件后缀有错误
return(false);
}
//【2】创建工作表
ISheet sheet = workBook.CreateSheet("sheet1");
//【3】循环生成列标题和设置样式
IRow rowTitle = sheet.CreateRow(0);
for (int i = 0; i < dgv.ColumnCount; i++)
{
ICell cell = rowTitle.CreateCell(i); //创建单元格
cell.SetCellValue(dgv.Columns[i].HeaderText.Trim()); // 设置行标题
SetCellStyle(workBook, cell); //设置单元格边框
SetColumnWidth(sheet, i, 20); //设置列宽
}
//【4】循环生成各行数据
for (int i = 0; i < dgv.RowCount; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.ColumnCount; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString().Trim()); //赋值
SetCellStyle(workBook, cell);
}
}
//【5】保存为Excel文件
using (FileStream fs = File.OpenWrite(fileName))
{
workBook.Write(fs);
return(true);
}
}
public static string BuildPaperExcel(String[] columnName, DataTable table)
{
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//新建一个字体样式对象
IFont font = workbook.CreateFont();
//设置字体加粗样式
font.Boldweight = short.MaxValue;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//在工作表中：建立行，参数为行号，从0计
IRow row = sheet.CreateRow(0);
IRow row1 = sheet.CreateRow(1);
//在行中：建立单元格，参数为列号，从0计
ICell cell0 = row.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("学校代号");
cell0.CellStyle = style;
ICell cell1 = row.CreateCell(1);
//设置单元格内容
cell1.SetCellValue("学校简称");
cell1.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1));
for (int i = 0; i < columnName.Length; i++)
{
ICell celli = row.CreateCell(i * 3 + 2);
celli.SetCellValue(columnName[i]);
celli.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, i * 3 + 2, i * 3 + 4));
ICell celli1 = row1.CreateCell(i * 3 + 2);
celli1.SetCellValue("应考");
ICell celli2 = row1.CreateCell(i * 3 + 3);
celli2.SetCellValue("实考");
ICell celli3 = row1.CreateCell(i * 3 + 4);
celli3.SetCellValue("缺考");
}
//IRow headerRow = sheet.CreateRow(0);
//foreach (DataColumn column in table.Columns)
//{
// headerRow.CreateCell(column.Ordinal).SetCellValue(columnName[column.Ordinal]);
//}
int rowIndex = 2;
foreach (DataRow r in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(r[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
string fileName = "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd") + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
if (!Directory.Exists(System.AppDomain.CurrentDomain.BaseDirectory + "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd")))
{
Directory.CreateDirectory(System.AppDomain.CurrentDomain.BaseDirectory + "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd"));
}
var f = File.Create(System.AppDomain.CurrentDomain.BaseDirectory + fileName);
ms.WriteTo(f);
ms.Close();
f.Close();
return(fileName);
}
protected void Print_Click(object sender, EventArgs e)
{
//***************************************************************************
//*** for Exporting to a Excel file
HSSFWorkbook workbook = new HSSFWorkbook();
//== 新增試算表 Sheet名稱。使用 NPOI.SS.UserModel命名空間。(v.1.2.4版）
ISheet u_sheet = (ISheet)workbook.CreateSheet("My Sheet_124");
//***************************************************************************
//=======微軟SDK文件的範本=======
SqlConnection Conn = new SqlConnection();
//----上面已經事先寫好 Imports System.Web.Configuration ----
Conn.ConnectionString = WebConfigurationManager.ConnectionStrings["LoveCarrierConnectionString"].ConnectionString;
//----(連結資料庫)----
SqlDataReader dr = null;
SqlCommand cmd = new SqlCommand(@"Select RID as'訂單編號',Reserve.TID as '歌手編號',Team.TeamMember1+' '+Team.TeamMember2 as '歌手',
Day as '星期',Class as '課堂', Song.SongName as '傳情歌曲', Category as '歌曲分類',
ClassName as '課堂名稱', Place as '傳情地點', GeneralLesson as '是否為通識課',
Name as '傳情人', DepartGrade as '傳情人系級',Phone as '傳情人手機', Email as '傳情人Email',
SentName as '被傳情人', SentDepartGrade as '被傳情人系級',SentPhone as '被傳情人手機', SentEmail as '被傳情人Email',
PayDay as '繳費時間', SpecialRequest as '特殊需求', Remark as '備註', ReserveTime as '送出時間', IsPaid as '繳費與否'
from Reserve,Team,Song
where Team.TID = Reserve.TID and Song.SID = Reserve.SID order by Reserve.RID", Conn);
try
{
Conn.Open(); //---- 這時候才連結DB
dr = cmd.ExecuteReader(); //---- 這時候執行SQL指令，取出資料
//===============================================(start)
//== 利用迴圈，把資料寫入 Excel各個儲存格裡面。
IRow u_Row = u_sheet.CreateRow(0);
for (int i = 0; i < dr.FieldCount; i++)
{
u_Row.CreateCell(i).SetCellValue(dr.GetName(i));
}
int k = 1;
while (dr.Read())
{
//**** v.1.2.4版在此有很大的改變！！！請看 http://tonyqus.sinaapp.com/archives/73
//**** 先建好一列（Row），才能去作格子（Cell）
u_Row = u_sheet.CreateRow(k);
for (int i = 0; i < dr.FieldCount; i++)
{ //-- FieldCount是指 DataReader每一列紀錄裡面，有幾個欄位。
//**********************************************************(start)
//**** v.1.2.4版在此有很大的改變！！！請看 http://tonyqus.sinaapp.com/archives/73
u_Row.CreateCell(i).SetCellValue(dr.GetValue(i).ToString()); //== .CreateCell() 可設定為同一列(Row)的 [第幾個格子]
//**********************************************************(end)
//u_Row.CreateCell(i).SetCellType(CellType.STRING);
//設定每一個欄位（格子）的儲存格內容，如：字串。
//XXXXX 以下用法，在 v1.2.4版會有 Bug，只能列出每一列的「最後一格」的資料！！XXXXXXXXXX
//== 避免這樣的錯誤，請看 http://tonyqus.sinaapp.com/archives/73
// u_sheet.CreateRow(k).CreateCell(i).SetCellValue(dr.GetValue(i).ToString()); //*** for Exporting to a Excel file (v.1.2.1版，正常)
}
k++;
}
//===============================================(end)
}
catch (Exception ex) //---- 如果程式有錯誤或是例外狀況，將執行這一段
{
Response.Write("<b>Error Message---- </b>" + ex.ToString() + "<hr />");
}
finally
{
if (dr != null)
{
cmd.Cancel(); //----關閉DataReader之前，一定要先「取消」SqlCommand
dr.Close();
}
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose(); //---- 一開始宣告有用到 New的,最後必須以 .Dispose()結束
}
}
//***************************************************************************
//*** for Exporting to a Excel file
MemoryStream ms = new MemoryStream(); //==需要 System.IO命名空間
workbook.Write(ms);
//== Excel檔名，請寫在最後面 filename的地方
DateTime serverTime = DateTime.Now;
DateTime localTime = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(serverTime, TimeZoneInfo.Local.Id, "Taipei Standard Time");
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("民歌傳情訂單", System.Text.Encoding.UTF8) + localTime.ToString(@"yyyy-MM-dd HHmm") + ".xls");
Response.BinaryWrite(ms.ToArray());
//== 釋放資源
workbook = null; //== C#為 null
ms.Close();
ms.Dispose();
}
public static void Batch_Update(DataTable dtSource)
{
string sql1 = "select distinct Program,Date from [Working_hoursInfor] where SySe like '%" + Common.SySe + "%'";
DataTable program = Common.datatable(sql1);
HSSFWorkbook hwb = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ICellStyle dateStyle = hwb.CreateCellStyle();
IDataFormat format = hwb.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
ISheet sheet = hwb.CreateSheet();//默认是sheet0
IRow row1 = sheet.CreateRow(0);
ICell cell = row1.CreateCell(0);
cell.SetCellValue("信管1501工时表");
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, program.Rows.Count + 3));//合并列 该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
// row1.Height = 30 * 30; //行高
cell.CellStyle = HeadStyle(hwb);
IRow row2 = sheet.CreateRow(1);
ICell cell2 = row2.CreateCell(0);
cell2.SetCellValue("学号");
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
// row2.Height = 30 * 30;
cell2.CellStyle = Sub_HeadStyle(hwb);
// sheet.SetColumnWidth(0, 16 * 256);
ICell cell3 = row2.CreateCell(1);
cell3.SetCellValue("姓名");
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
cell3.CellStyle = Sub_HeadStyle(hwb);
ICell cell4 = row2.CreateCell(2);
cell4.SetCellValue("班级");
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 2, 2));
cell4.CellStyle = Sub_HeadStyle(hwb);
ICell cell5 = row2.CreateCell(program.Rows.Count + 3);
cell5.SetCellValue("合计");
sheet.AddMergedRegion(new CellRangeAddress(1, 2, program.Rows.Count + 3, program.Rows.Count + 3));
cell5.CellStyle = Sub_HeadStyle(hwb);
for (int i = 3, j = 0; j < program.Rows.Count; i++, j++)
{
ICell cell_1 = row2.CreateCell(i);
cell_1.SetCellValue(Convert.ToDateTime(program.Rows[j][1]).Date);
cell_1.CellStyle = Sub_HeadStyle(hwb);
}
IRow row3 = sheet.CreateRow(2);
for (int i = 3, j = 0; j < program.Rows.Count; i++, j++)
{
ICell cell_2 = row3.CreateCell(i);
cell_2.SetCellValue(program.Rows[j][0].ToString());
cell_2.CellStyle = Sub_HeadStyle(hwb);
}
int rowindex = 3;
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
IRow dataRow = sheet.CreateRow(rowindex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
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++;
}
hwb.Write(ms);
HttpContext curContext = HttpContext.Current;
curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=工时-" + DateTime.Now.Date + ".xls"));
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.BinaryWrite(ms.ToArray());
hwb = null;
ms.Close(); ms.Dispose();
}
public static void DataSet_To_Excel(DataSet dataSet, string pFilePath)
{
if (dataSet != null && dataSet.Tables.Count > 0)
{
IWorkbook workbook = null;
ISheet worksheet = null;
string Ext = System.IO.Path.GetExtension(pFilePath); //<-Extension del archivo
switch (Ext.ToLower())
{
case ".xls":
HSSFWorkbook workbookH = new HSSFWorkbook();
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "ERP"; dsi.Manager = "IT Department";
workbookH.DocumentSummaryInformation = dsi;
workbook = workbookH;
break;
case ".xlsx": workbook = new XSSFWorkbook(); break;
}
using (FileStream stream = new FileStream(pFilePath, FileMode.Create, FileAccess.ReadWrite))
{
foreach (DataTable pDatos in dataSet.Tables)
{
try
{
if (pDatos != null && pDatos.Rows.Count > 0)
{
worksheet = workbook.CreateSheet(pDatos.TableName); //<-Usa el nombre de la tabla como nombre de la Hoja
if (pDatos.Columns.Count > 0)
{
int index = 0;
foreach (var col in pDatos.Columns)
{
worksheet.SetColumnWidth(index++, (int)((22 + 0.72) * 256));
}
}
//CREAR EN LA PRIMERA FILA LOS TITULOS DE LAS COLUMNAS
int iRow = 0;
if (pDatos.Columns.Count > 0)
{
int iCol = 0;
IRow fila = worksheet.CreateRow(iRow);
foreach (DataColumn columna in pDatos.Columns)
{
ICell cell = fila.CreateCell(iCol, CellType.String);
cell.SetCellValue(columna.ColumnName);
iCol++;
}
iRow++;
}
//FORMATOS PARA CIERTOS TIPOS DE DATOS
ICellStyle _doubleCellStyle = workbook.CreateCellStyle();
_doubleCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.###");
ICellStyle _intCellStyle = workbook.CreateCellStyle();
_intCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
ICellStyle _boolCellStyle = workbook.CreateCellStyle();
_boolCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("BOOLEAN");
ICellStyle _dateCellStyle = workbook.CreateCellStyle();
_dateCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");
ICellStyle _dateTimeCellStyle = workbook.CreateCellStyle();
_dateTimeCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy HH:mm:ss");
//AHORA CREAR UNA FILA POR CADA REGISTRO DE LA TABLA
foreach (DataRow row in pDatos.Rows)
{
IRow fila = worksheet.CreateRow(iRow);
int iCol = 0;
foreach (DataColumn column in pDatos.Columns)
{
ICell cell = null; //<-Representa la celda actual
object cellValue = row[iCol]; //<- El valor actual de la celda
switch (column.DataType.ToString())
{
case "System.Boolean":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Boolean);
if (Convert.ToBoolean(cellValue))
{
cell.SetCellFormula("TRUE()");
}
else
{
cell.SetCellFormula("FALSE()");
}
cell.CellStyle = _boolCellStyle;
}
break;
case "System.String":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.String);
cell.SetCellValue(Convert.ToString(cellValue));
}
break;
case "System.Int32":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt32(cellValue));
cell.CellStyle = _intCellStyle;
}
break;
case "System.Int64":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(cellValue));
cell.CellStyle = _intCellStyle;
}
break;
case "System.Decimal":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
cell.CellStyle = _doubleCellStyle;
}
break;
case "System.Double":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
cell.CellStyle = _doubleCellStyle;
}
break;
case "System.DateTime":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDateTime(cellValue));
//Si No tiene valor de Hora, usar formato dd-MM-yyyy
DateTime cDate = Convert.ToDateTime(cellValue);
if (cDate != null && cDate.Hour > 0)
{
cell.CellStyle = _dateTimeCellStyle;
}
else
{
cell.CellStyle = _dateCellStyle;
}
}
break;
default:
break;
}
iCol++;
}
iRow++;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
workbook.Write(stream);
stream.Close();
}
}
}
public void AddCell(string data, XSSFCellStyle cellStyle = null, bool isNumeric = false)
{
row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
ICell cell;
if (isNumeric)
{
cell = row.CreateCell(colIndex, CellType.Numeric);
}
else
{
cell = row.CreateCell(colIndex);
}
cell.CellStyle = cellStyle ?? defaultCellStyle;
if (isNumeric)
{
cell.SetCellFormula(data);
}
else
{
cell.SetCellValue(creationHelper.CreateRichTextString(data));
}
colIndex++;
}
public void TestRemoveRow()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet1 = workbook.CreateSheet();
Assert.AreEqual(0, sheet1.PhysicalNumberOfRows);
Assert.AreEqual(0, sheet1.FirstRowNum);
Assert.AreEqual(0, sheet1.LastRowNum);
IRow row0 = sheet1.CreateRow(0);
Assert.AreEqual(1, sheet1.PhysicalNumberOfRows);
Assert.AreEqual(0, sheet1.FirstRowNum);
Assert.AreEqual(0, sheet1.LastRowNum);
sheet1.RemoveRow(row0);
Assert.AreEqual(0, sheet1.PhysicalNumberOfRows);
Assert.AreEqual(0, sheet1.FirstRowNum);
Assert.AreEqual(0, sheet1.LastRowNum);
sheet1.CreateRow(1);
IRow row2 = sheet1.CreateRow(2);
Assert.AreEqual(2, sheet1.PhysicalNumberOfRows);
Assert.AreEqual(1, sheet1.FirstRowNum);
Assert.AreEqual(2, sheet1.LastRowNum);
Assert.IsNotNull(sheet1.GetRow(1));
Assert.IsNotNull(sheet1.GetRow(2));
sheet1.RemoveRow(row2);
Assert.IsNotNull(sheet1.GetRow(1));
Assert.IsNull(sheet1.GetRow(2));
Assert.AreEqual(1, sheet1.PhysicalNumberOfRows);
Assert.AreEqual(1, sheet1.FirstRowNum);
Assert.AreEqual(1, sheet1.LastRowNum);
IRow row3 = sheet1.CreateRow(3);
ISheet sheet2 = workbook.CreateSheet();
try
{
sheet2.RemoveRow(row3);
Assert.Fail("Expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Specified row does not belong to this sheet", e.Message);
}
IRow row20 = sheet2.CreateRow(0);
row20.CreateCell(0).SetCellValue("row20");
IRow row24 = sheet2.CreateRow(4);
row24.CreateCell(0).SetCellValue("row24");
sheet2.RemoveRow(row24);
workbook = _testDataProvider.WriteOutAndReadBack(workbook);
sheet2 = workbook.GetSheetAt(1);
Assert.IsNull(sheet2.GetRow(4));
Assert.AreEqual("row20", sheet2.GetRow(0).GetCell(0).StringCellValue);
}
private static void SetValue(ISheet sheet, int rowIndex, int colIndex, double value)
{
IRow row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
row.CreateCell(colIndex).SetCellValue(value);
}
private void btnExportXLS_Click(object sender, EventArgs e)
{
if (allCamInfo.Count == 0)
{
MessageBox.Show("No Cam Find! Export fail");
return;
}
var newFile = @"export.xlsx";
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
int rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex);
ICell cell_head = row.CreateCell(0);
cell_head.SetCellValue("编号");
ICell cell_1 = row.CreateCell(1);
cell_1.SetCellValue("IP地址");
ICell cell_2 = row.CreateCell(2);
cell_2.SetCellValue("型号");
ICell cell_3 = row.CreateCell(3);
cell_3.SetCellValue("固件版本");
ICell cell_4 = row.CreateCell(4);
cell_4.SetCellValue("MAC地址");
ICell cell_5 = row.CreateCell(5);
cell_5.SetCellValue("设备ID");
rowIndex += 1;
allCamInfo.Sort();
foreach (CamInfo cam in allCamInfo)
{
IRow row1 = sheet1.CreateRow(rowIndex);
//int calIndex = 0;
ICell cell = row1.CreateCell(0);
cell.SetCellValue(rowIndex);
//calIndex++;
ICell cell1 = row1.CreateCell(1);
//string Ipaddress =string.Copy( cam.Ipaddress;
cell1.SetCellValue(string.Copy(cam.Ipaddress));
//calIndex++;
ICell cell3 = row1.CreateCell(2);
cell3.SetCellValue(cam.Model);
//calIndex++;
ICell cell4 = row1.CreateCell(3);
cell4.SetCellValue(cam.FirewVersion);
//calIndex++;
ICell cell5 = row1.CreateCell(4);
cell5.SetCellValue(cam.MacAddress);
//calIndex++;
ICell cell6 = row1.CreateCell(5);
cell6.SetCellValue(cam.DeviceID);
rowIndex += 1;
}
workbook.Write(fs);
}
Console.WriteLine("Export Excel Done");
}
// Get a cell, create as needed
//
private static ICell GetCell(ISheet sheet, int rowIndex, int colIndex)
{
IRow row = sheet.GetRow(rowIndex);
if (row == null)
{
row = sheet.CreateRow(rowIndex);
}
ICell cell = row.GetCell(colIndex);
if (cell == null)
{
cell = row.CreateCell(colIndex);
}
return cell;
}
/// <summary>
/// 将数据导出为Excel文档
/// </summary>
/// <param name="employees">Employee类型List集合</param>
/// <returns></returns>
public bool ExportAsExcel(List <Employee> employees)
{
string fileName;
IWorkbook workbook = null;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel 03文档(*.xls)|*.xls|Excel 07文档(*.xlsx)|*.xlsx";
saveDialog.FileName = DateTime.Now.ToString("yyMMdd-HHmmss");
if (saveDialog.ShowDialog() == true)
{
fileName = saveDialog.FileName;
if (fileName.EndsWith(".xls"))
{
workbook = new HSSFWorkbook();
}
else
{
workbook = new XSSFWorkbook();
}
workbook.CreateSheet("sheet1");
ISheet sheet = workbook.GetSheet("sheet1");
int rowNum = 0;
sheet.CreateRow(rowNum);
IRow rowZero = sheet.GetRow(0);
//此处不是接口的直接实例化，而是声明一个ICell的数组
ICell[] cells = new ICell[8];
for (int i = 0; i < 8; i++)
{
cells[i] = rowZero.CreateCell(i);
}
//设置标题
cells[0].SetCellValue("ID");
cells[1].SetCellValue("Name");
cells[2].SetCellValue("Phone");
cells[3].SetCellValue("Alternate");
cells[4].SetCellValue("Education");
cells[5].SetCellValue("Address");
cells[6].SetCellValue("Email");
cells[7].SetCellValue("Remark");
foreach (var item in employees)
{
sheet.CreateRow(++rowNum);
//新建Row
IRow currentRow = sheet.GetRow(rowNum);
//新建相应的Cells
for (int i = 0; i < 8; i++)
{
cells[i] = currentRow.CreateCell(i);
}
cells[0].SetCellValue(item.ID);
cells[1].SetCellValue(item.Name);
cells[2].SetCellValue(item.Phone);
cells[3].SetCellValue(item.Alternate);
cells[4].SetCellValue(item.Education);
cells[5].SetCellValue(item.Address);
cells[6].SetCellValue(item.Email);
cells[7].SetCellValue(item.Remark);
}
using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate))
{
workbook.Write(fs);
workbook.Close();
}
System.Diagnostics.Process.Start("explorer.exe", @"/select," + fileName);
return(true);
}
else
{
return(false);
}
}