public void NpoiExcel(DataTable dt, string title)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
string DgvValue = dt.Rows[I][j].ToString();
row2.CreateCell(j).SetCellValue(DgvValue);
sheet.SetColumnWidth(j, 20 * 150);
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// 合并单元格
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sheet">工作表</param>
/// <param name="columnIndex">列索引</param>
/// <param name="options">导出选项配置</param>
private void MergeCells<T>(NPOI.SS.UserModel.ISheet sheet, int columnIndex, IExportOptions<T> options)
where T : class, new()
{
string currentCellValue;
var startRowIndex = options.DataRowStartIndex;
NPOI.SS.Util.CellRangeAddress mergeRangeAddress;
var startRow = sheet.GetRow(startRowIndex);
if (startRow == null)
return;
var startCell = startRow.GetCell(columnIndex);
if (startCell == null)
return;
string startCellValue = startCell.StringCellValue;
if (string.IsNullOrWhiteSpace(startCellValue))
return;
for (var rowIndex = options.DataRowStartIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
{
var cell = sheet.GetRow(rowIndex)?.GetCell(columnIndex);
currentCellValue = cell == null ? string.Empty : cell.StringCellValue;
if (currentCellValue.Trim() != startCellValue.Trim())
{
mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex - 1, columnIndex, columnIndex);
sheet.AddMergedRegion(mergeRangeAddress);
startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center;
startRowIndex = rowIndex;
startCellValue = currentCellValue;
}
if (rowIndex == sheet.PhysicalNumberOfRows - 1 && startRowIndex != rowIndex)
{
mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex, columnIndex, columnIndex);
sheet.AddMergedRegion(mergeRangeAddress);
startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center;
}
}
}
public void TestModifySimpleWithStyling()
{
HSSFWorkbook workbook = OpenSample("SimpleWithStyling.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
for (int k = 0; k < 4; k++)
{
ICell cell = sheet.GetRow(k).GetCell(0);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
}
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
for (int k = 0; k < 4; k++)
{
ICell cell = sheet.GetRow(k).GetCell(0);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
}
}
public void TestModifySimpleWithSkip()
{
HSSFWorkbook workbook = OpenSample("SimpleWithSkip.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(1);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
cell = sheet.GetRow(1).GetCell(0);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
cell = sheet.GetRow(0).GetCell(1);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual(DO_NOT_REPLACE, cell.RichStringCellValue.String);
cell = sheet.GetRow(1).GetCell(0);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
cell = sheet.GetRow(1).GetCell(1);
Assert.AreEqual(DO_NOT_REPLACE, cell.RichStringCellValue.String);
}
public void TestEvaluateBooleanInCell_bug44508()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula = ("1=1");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
try
{
fe.EvaluateInCell(cell);
}
catch (FormatException)
{
Assert.Fail("Identified bug 44508");
}
Assert.AreEqual(true, cell.BooleanCellValue);
}
public void TestBOFandEOFRecords()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("SimpleChart.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
IRow firstRow = sheet.GetRow(0);
ICell firstCell = firstRow.GetCell(0);
//System.out.println("first assertion for date");
Assert.AreEqual(new DateTime(2000, 1, 1, 10, 51, 2),
DateUtil.GetJavaDate(firstCell.NumericCellValue, false));
IRow row = sheet.CreateRow(15);
ICell cell = row.CreateCell(1);
cell.SetCellValue(22);
InternalSheet newSheet = ((HSSFSheet)workbook.GetSheetAt(0)).Sheet;
IList records = newSheet.Records;
//System.out.println("BOF Assertion");
Assert.IsTrue(records[0] is BOFRecord);
//System.out.println("EOF Assertion");
Assert.IsTrue(records[records.Count - 1] is EOFRecord);
}
/// <summary>
/// 处理正文
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sheet">NPOI工作表</param>
/// <param name="dataRowStartIndex">数据行起始索引</param>
/// <param name="data">数据集</param>
/// <param name="headerDict">表头映射字典</param>
private void HandleBody <T>(NPOI.SS.UserModel.ISheet sheet, int dataRowStartIndex, IList <T> data,
IDictionary <string, PropertySetting> headerDict) where T : class, new()
{
if (data.Count <= 0)
{
return;
}
for (int i = 0; i < data.Count; i++)
{
var columnIndex = 0;
var row = sheet.CreateRow(dataRowStartIndex + i);
var dto = data[i];
foreach (var kvp in headerDict)
{
if (kvp.Value.Ignored)
{
continue;
}
if (kvp.Value.IsDynamicColumn)
{
var dictionary = dto.GetExtendDictionary(kvp.Key);
foreach (var column in kvp.Value.DynamicColumns)
{
if (column.IsEmpty())
{
continue;
}
row.CreateCell(columnIndex).SetCellValue(dictionary[column]?.ToString());
columnIndex++;
}
continue;
}
row.CreateCell(columnIndex).SetCellValue(dto.GetStringValue(kvp.Key, kvp.Value.Formatter));
columnIndex++;
}
}
}
public void TestWithTwoHyperlinks()
{
HSSFWorkbook wb = OpenSample("WithTwoHyperLinks.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
ICell cell1 = sheet.GetRow(4).GetCell(0);
IHyperlink link1 = cell1.Hyperlink;
Assert.IsNotNull(link1);
Assert.AreEqual("Foo", link1.Label);
Assert.AreEqual("http://poi.apache.org/", link1.Address);
Assert.AreEqual(4, link1.FirstRow);
Assert.AreEqual(0, link1.FirstColumn);
ICell cell2 = sheet.GetRow(8).GetCell(1);
IHyperlink link2 = cell2.Hyperlink;
Assert.IsNotNull(link2);
Assert.AreEqual("Bar", link2.Label);
Assert.AreEqual("http://poi.apache.org/hssf/", link2.Address);
Assert.AreEqual(8, link2.FirstRow);
Assert.AreEqual(1, link2.FirstColumn);
}
public void TestRepeatingColsRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Test Print Titles");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.SetCellValue(new HSSFRichTextString("hi"));
workbook.SetRepeatingRowsAndColumns(0, 0, 1, 0, 0);
string filepath = TempFile.GetTempFilePath("TestPrintTitles", ".xls");
FileStream fileOut = new FileStream(filepath, FileMode.OpenOrCreate);
workbook.Write(fileOut);
fileOut.Close();
Assert.IsTrue(File.Exists(filepath), "file exists");
}
/// <summary>
/// 合并单元格
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheet"></param>
/// <param name="options"></param>
private void CellRangeAddress <T>(NPOI.SS.UserModel.ISheet sheet, IExportOptions <T> options) where T : class, new()
{
//if (options.HeaderRow.All(x => x.Cells.All(m => m.ColumnSpan == 1)))
// return;
//var rows = options.HeaderRow.Where(x => x.Cells.Any(m => m.ColumnSpan > 1)).ToList();
var rows = options.HeaderRow.Where(x => x.Cells.Any(t => t.RowSpan > 1 || t.ColumnSpan > 1)).ToList();
if (rows.Any())
{
rows.OrderBy(x => x.RowIndex).ForEach(row =>
{
row.Cells.OrderBy(x => x.ColumnIndex).ForEach(x =>
{
if (x.RowSpan <= 1 && x.ColumnSpan <= 1)
{
return;
}
var region =
new CellRangeAddress(x.RowIndex, x.EndRowIndex, x.ColumnIndex, x.EndColumnIndex);
sheet.AddMergedRegion(region);
});
});
}
}
public void TestSelectedSheet_bug44523()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1");
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2");
NPOI.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3");
NPOI.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4");
ConfirmActiveSelected(sheet1, true);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetSelectedTab(1);
// see Javadoc, in this case selected means "active"
Assert.AreEqual(wb.ActiveSheetIndex, (short)wb.ActiveSheetIndex);
// Demonstrate bug 44525:
// Well... not quite, since isActive + isSelected were also Added in the same bug fix
if (sheet1.IsSelected)
{
throw new AssertionException("Identified bug 44523 a");
}
wb.SetActiveSheet(1);
if (sheet1.IsActive)
{
throw new AssertionException("Identified bug 44523 b");
}
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, true);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
}
public void TestEvaluateInCellWithErrorCode_bug44950()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(0);
cell.CellFormula = ("na()"); // this formula Evaluates to an Excel error code '#N/A'
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
try
{
fe.EvaluateInCell(cell);
}
catch (InvalidOperationException e)
{
if (e.Message.StartsWith("Cannot get a error value from"))
{
throw new AssertionException("Identified bug 44950 b");
}
throw;
}
}
public void TestAddEmptyRow()
{
//try to Add 5 empty rows to a new sheet
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
for (int i = 0; i < 5; i++)
{
sheet.CreateRow(i);
}
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
//try Adding empty rows in an existing worksheet
workbook = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls");
sheet = workbook.GetSheetAt(0);
for (int i = 3; i < 10; i++)
{
sheet.CreateRow(i);
}
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
}
/// <summary>
/// 处理表头
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sheet">NPOI工作表</param>
/// <param name="headerRowIndex">表头行索引</param>
/// <param name="headerDict">表头映射字典</param>
private void HandleHeader(NPOI.SS.UserModel.ISheet sheet, int headerRowIndex, IDictionary <string, PropertySetting> headerDict)
{
var row = sheet.CreateRow(headerRowIndex);
var columnIndex = 0;
foreach (var kvp in headerDict)
{
if (kvp.Value.Ignored)
{
continue;
}
if (kvp.Value.IsDynamicColumn)
{
foreach (var column in kvp.Value.DynamicColumns)
{
row.CreateCell(columnIndex).SetCellValue(column);
columnIndex++;
}
continue;
}
row.CreateCell(columnIndex).SetCellValue(kvp.Value.Title);
columnIndex++;
}
}
/// <summary>
/// 添加图片
/// </summary>
/// <param name="sheet">NPOI工作表</param>
/// <param name="picInfo">图片信息</param>
public static void AddPicture(this NPOI.SS.UserModel.ISheet sheet, PictureInfo picInfo)
{
var pictureIdx = sheet.Workbook.AddPicture(picInfo.PictureData, PictureType.PNG);
var anchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = picInfo.MinCol;
anchor.Col2 = picInfo.MaxCol;
anchor.Row1 = picInfo.MinRow;
anchor.Row2 = picInfo.MaxRow;
anchor.Dx1 = picInfo.PictureStyle.AnchorDx1;
anchor.Dx2 = picInfo.PictureStyle.AnchorDx2;
anchor.Dy1 = picInfo.PictureStyle.AnchorDy1;
anchor.Dy2 = picInfo.PictureStyle.AnchorDy2;
anchor.AnchorType = AnchorType.MoveDontResize;
var drawing = sheet.CreateDrawingPatriarch();
var pic = drawing.CreatePicture(anchor, pictureIdx);
if (sheet is HSSFSheet)
{
var shape = pic as HSSFShape;
shape.FillColor = picInfo.PictureStyle.FillColor;
shape.IsNoFill = picInfo.PictureStyle.IsNoFill;
//shape.LineStyle = picInfo.PictureStyle.LineStyle;
shape.LineStyleColor = picInfo.PictureStyle.LineStyleColor;
shape.LineWidth = (int)picInfo.PictureStyle.LineWidth;
}
else if (sheet is XSSFSheet)
{
var shape = pic as XSSFShape;
shape.FillColor = picInfo.PictureStyle.FillColor;
shape.IsNoFill = picInfo.PictureStyle.IsNoFill;
//shape.LineStyle = picInfo.PictureStyle.LineStyle;
//shape.LineStyleColor = picInfo.PictureStyle.LineStyleColor;
shape.LineWidth = picInfo.PictureStyle.LineWidth;
}
}
protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex)
{
if (this.ColumnHeadList.Count > 0)
{
// 冻结
sheet.CreateFreezePane(1, 4);
// 数据从第3行开始显示
rowIndex = rowIndex + 2;
// 所有列头居中
this.HeadStyle.Alignment = HorizontalAlignment.Center;
for (int i = 0; i < 2; i++)
{
IRow row = sheet.CreateRow(rowIndex);
foreach (ColumnsMapping cm in this.ColumnHeadList)
{
ICell cell = null;
if (i == 0)
{
if (cm.ColumnsIndex < 3 || cm.ColumnsIndex == 13 || cm.ColumnsIndex == 14)
{
// 合并行
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, cm.ColumnsIndex, cm.ColumnsIndex));
cell = row.CreateCell(cm.ColumnsIndex);
// 设置列宽
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
// 设置列头样式
cell.CellStyle = this.HeadStyle;
cell.SetCellValue(cm.ColumnsText);
}
else if (cm.ColumnsIndex == 3 || cm.ColumnsIndex == 8)
{
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cm.ColumnsIndex, cm.ColumnsIndex + 4));
cell = row.CreateCell(cm.ColumnsIndex);
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
cell.CellStyle = this.HeadStyle;
if (cm.ColumnsIndex == 3)
{
cell.SetCellValue("成本");
}
else if (cm.ColumnsIndex == 8)
{
cell.SetCellValue("收入");
}
for (int j = 3; j <= 12; j++)
{
if (j == 3 || j == 8)
{
continue;
}
cell = row.CreateCell(j);
cell.CellStyle = this.HeadStyle;
}
}
}
else
{
if (cm.ColumnsIndex >= 3 && cm.ColumnsIndex <= 12)
{
cell = row.CreateCell(cm.ColumnsIndex);
cell.CellStyle = this.HeadStyle;
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
cell.SetCellValue(cm.ColumnsText);
}
else if (cm.ColumnsIndex < 3 || cm.ColumnsIndex == 13 || cm.ColumnsIndex == 14)
{
cell = row.CreateCell(cm.ColumnsIndex);
cell.CellStyle = this.HeadStyle;
}
}
}
rowIndex++;
}
}
}
private static void ConfirmActiveSelected(NPOI.SS.UserModel.ISheet sheet,
bool expectedActive, bool expectedSelected)
{
Assert.AreEqual(expectedActive, sheet.IsActive, "active");
Assert.AreEqual(expectedSelected, sheet.IsSelected, "selected");
}
private static void ConfirmActiveSelected(NPOI.SS.UserModel.ISheet sheet, bool expected)
{
ConfirmActiveSelected(sheet, expected, expected);
}
public void TestActiveSheetAfterDelete_bug40414()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet0 = wb.CreateSheet("Sheet0");
NPOI.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1");
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2");
NPOI.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3");
NPOI.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4");
// Confirm default activation/selection
ConfirmActiveSelected(sheet0, true);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetActiveSheet(3);
wb.SetSelectedTab(3);
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, true);
ConfirmActiveSelected(sheet4, false);
wb.RemoveSheetAt(3);
// after removing the only active/selected sheet, another should be active/selected in its place
if (!sheet4.IsSelected)
{
throw new AssertionException("identified bug 40414 a");
}
if (!sheet4.IsActive)
{
throw new AssertionException("identified bug 40414 b");
}
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet4, true);
sheet3 = sheet4; // re-align local vars in this Test case
// Some more cases of removing sheets
// Starting with a multiple selection, and different active sheet
wb.SetSelectedTabs(new int[] { 1, 3, });
wb.SetActiveSheet(2);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
ConfirmActiveSelected(sheet3, false, true);
// removing a sheet that is not active, and not the only selected sheet
wb.RemoveSheetAt(3);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
// removing the only selected sheet
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet2, true, true);
// The last remaining sheet should always be active+selected
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, true, true);
}
///<summary>
/// #region 少量excel数据导入数据库
/// </summary>
public static async Task <DataTable> ImExport(DataTable dt, IWorkbook hssfworkbook)
{
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
dt.Rows.RemoveAt(0);
#region 往数据库表添加数据
using (WuLinEntities1 db = new WuLinEntities1())
{
if (dt != null && dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string 时间 = dt.Rows[i]["时间"].ToString();
string 国产进口 = dt.Rows[i]["国产/进口"].ToString();
string 省 = dt.Rows[i]["省"].ToString();
string 市 = dt.Rows[i]["市"].ToString();
string 县 = dt.Rows[i]["县"].ToString();
string 制造商 = dt.Rows[i]["制造商"].ToString();
string 车辆型号 = dt.Rows[i]["车辆型号"].ToString();
string 品牌 = dt.Rows[i]["品牌"].ToString();
string 车型 = dt.Rows[i]["车型"].ToString();
string 排量 = dt.Rows[i]["排量"].ToString();
string 变速器 = dt.Rows[i]["变速器"].ToString();
string 车辆类型 = dt.Rows[i]["车辆类型"].ToString();
string 车身型式 = dt.Rows[i]["车身型式"].ToString();
string 燃油类型 = dt.Rows[i]["燃油类型"].ToString();
string 使用性质 = dt.Rows[i]["使用性质"].ToString();
string 所有权 = dt.Rows[i]["所有权"].ToString();
string 抵押标记 = dt.Rows[i]["抵押标记"].ToString();
string 性别 = dt.Rows[i]["性别"].ToString();
string 年龄 = dt.Rows[i]["年龄"].ToString();
string 车身颜色 = dt.Rows[i]["车身颜色"].ToString();
string 发动机型号 = dt.Rows[i]["发动机型号"].ToString();
string 功率 = dt.Rows[i]["功率"].ToString();
string 排放标准 = dt.Rows[i]["排放标准"].ToString();
string 轴距 = dt.Rows[i]["轴距"].ToString();
string 轮胎规格 = dt.Rows[i]["轮胎规格"].ToString();
string 车外廓长 = dt.Rows[i]["车外廓长"].ToString();
string 车外廓宽 = dt.Rows[i]["车外廓宽"].ToString();
string 车外廓高 = dt.Rows[i]["车外廓高"].ToString();
string 准确排量 = dt.Rows[i]["准确排量"].ToString();
string 核定载客 = dt.Rows[i]["核定载客"].ToString();
string 总质量 = dt.Rows[i]["总质量"].ToString();
string 整备质量 = dt.Rows[i]["整备质量"].ToString();
string 轴数 = dt.Rows[i]["轴数"].ToString();
string 前轮距 = dt.Rows[i]["前轮距"].ToString();
string 后轮距 = dt.Rows[i]["后轮距"].ToString();
string 保有量 = dt.Rows[i]["保有量"].ToString();;
//int.TryParse(dt.Rows[i]["保有量"].ToString(), out 保有量);
PassengerVehicle pv = new PassengerVehicle();
pv.Id = Guid.NewGuid();
pv.使用性质 = 使用性质;
pv.保有量 = Convert.ToInt32(保有量);
pv.准确排量 = (准确排量);
pv.制造商 = 制造商;
pv.前轮距 = (前轮距);
pv.功率 = (功率);
pv.县 = 县;
pv.发动机型号 = 发动机型号;
pv.变速器 = 变速器;
pv.后轮距 = (后轮距);
pv.品牌 = 品牌;
pv.国产_进口 = 国产进口;
pv.市 = 市;
pv.年龄 = (年龄);
pv.性别 = 性别;
pv.总质量 = (总质量);
pv.所有权 = 所有权;
pv.抵押标记 = 抵押标记;
pv.排放标准 = 排放标准;
pv.排量 = (排量);
pv.整备质量 = (整备质量);
pv.时间 = 时间;
pv.核定载客 = (核定载客);
pv.燃油类型 = 燃油类型;
pv.省 = 省;
pv.车型 = 车型;
pv.车外廓宽 = (车外廓宽);
pv.车外廓长 = (车外廓长);
pv.车外廓高 = (车外廓高);
pv.车身型式 = 车身型式;
pv.车身颜色 = 车身颜色;
pv.车辆型号 = 车辆型号;
pv.车辆类型 = 车辆类型;
pv.轮胎规格 = 轮胎规格;
pv.轴数 = (轴数);
pv.轴距 = (轴距);
db.PassengerVehicles.Add(pv);
try
{
await db.SaveChangesAsync();
}
catch (Exception e)
{
Console.WriteLine("第" + i + "条：" + e.Message);
}
}
}
#endregion
}
return(dt);
}
private void button3_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
FileStream fs;
XSSFWorkbook wk;
PDDocument doc = PDDocument.load(address.pdf_path);
PDFTextStripper pdfstripper = new PDFTextStripper();
string str = pdfstripper.getText(doc);
string[] str1 = str.Split(new string[] { "Sensor" }, StringSplitOptions.RemoveEmptyEntries); //提取sensor之后的内容：数据
string[] str2 = str1[1].Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries); //提取每一行的数据
string[] standard = str2[0].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries); //提取第一行标准值
// swPdfChange.Write(str1[1]); //str1 中保存所需要的内容： 标准测量点 校准值
int num = str2.Length - 1; //获取的值的行数
// swPdfChange.Close();
string TempletFileName = @".\excel.xlsx"; //模板文件
using (fs = File.OpenRead(TempletFileName))
{
FileStream fs2 = File.Create(address.excel_path);
wk = new XSSFWorkbook(fs);
ICellStyle tableStyle = wk.CreateCellStyle();
st = wk.GetSheet("52-301,52-301.1");
IRow r = st.CreateRow(0);
ICell c = null;
int cnt = 0;
for (int i = 0; i < num; i++)
{
string[] value = str2[i + 1].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
if ((i % 4 == 0) && (i >= 4))
{
cnt += 24;
goto A;
}
A:
for (int j = 0; j < standard.Length; j++) //标准值/测量点写入excel
{
char[] TrimChar = { '(', ')' };
int[] col = { 1, 2, 14 };
string[] val = { value[0], standard[j], value[(j + 1) * 2].Trim(TrimChar) };
r = st.CreateRow(i * 5 + 68 + j + cnt);
for (int dic = 0; dic < 3; dic++)
{
c = r.CreateCell(col[dic]);
c.SetCellValue(val[dic]);
}
for (int k = 0; k < 8; k++) //4次读数
{
c = r.CreateCell(k + 5);
c.SetCellValue(value[(j + 1) * 2 - 1]);
}
}
c.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
c.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
c.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
}
wk.Write(fs2);
fs2.Close();
fs.Close();
}
this.Cursor = Cursors.Arrow;
MessageBox.Show("转换完成", "PDF->Excel", MessageBoxButtons.OK);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="ds">要导出的数据源</param>
/// <param name="excelFilePath">文件的物理路径</param>
/// <param name="errorFun"></param>
/// <returns></returns>
public static bool Output(DataSet ds, string excelFilePath, Action <Exception> errorFun = null)
{
if (!System.IO.File.Exists(excelFilePath))
{
throw new Exception("Excel 文件不存在");
}
if (null == ds || ds.Tables.Count == 0)
{
return(false);
}
try
{
//1.0 创建工作薄 和 工作表对象
NPOI.HSSF.UserModel.HSSFWorkbook book;
using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite))
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile);
}
int sheetIndex = 0;
foreach (DataTable dt in ds.Tables)
{
string sheetName = dt.TableName ?? "sheet" + sheetIndex;
NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表
if (null == sheet1)
{
sheet1 = book.CreateSheet(sheetName);
}
else if (sheet1.SheetName != sheetName)
{
book.SetSheetName(sheetIndex, sheetName);
}
int beginRow = 1;
//2.0给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行
for (int i = 0; i < dt.Columns.Count; i++)
{
rowHead.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//3.0 填充表格数据
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + beginRow); //创建数据行
for (int j = 0; j < dt.Columns.Count; j++) //填充行数据
{
rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
sheetIndex++;
}
//4.0 写入文件
using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create))
{
book.Write(wfile);
}
return(true);
}
catch (Exception ex)
{
if (errorFun != null)
{
errorFun(ex);
}
return(false);
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">DataTable：要导出的数据源</param>
/// <param name="excelFilePath">string：文件的物理路径</param>
/// <param name="isCustomHead">bool：是否自定义表头</param>
/// <param name="tbColumnNames">List:表头</param>
/// <param name="sheetName">string：sheet表名</param>
/// <returns></returns>
public static bool Output(DataTable dt, string excelFilePath, bool isCustomHead = false, List <string> tbColumnNames = null, string sheetName = "", Action <Exception> errorFun = null, int sheetIndex = 0)
{
if (!System.IO.File.Exists(excelFilePath))
{
throw new Exception("Excel 文件不存在");
}
if (null == dt && dt.Rows.Count == 0)
{
return(false);
}
try
{
//1.0 创建工作薄 和 工作表对象
NPOI.HSSF.UserModel.HSSFWorkbook book;
using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite))
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile);
}
sheetName = string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName;
NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表
if (null == sheet1)
{
sheet1 = book.CreateSheet(sheetName);
}
else if (sheet1.SheetName != sheetName)
{
book.SetSheetName(sheetIndex, sheetName);
}
//2.0给sheet1添加第一行的头部标题
if (!isCustomHead || tbColumnNames == null || tbColumnNames.Count != dt.Columns.Count)
{
tbColumnNames = new List <string>();
foreach (DataColumn item in dt.Columns)
{
tbColumnNames.Add(item.ColumnName);
}
}
NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行
for (int i = 0; i < tbColumnNames.Count; i++)
{
rowHead.CreateCell(i).SetCellValue(tbColumnNames[i]);
}
//3.0 填充表格数据
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1); //创建数据行
for (int j = 0; j < dt.Columns.Count; j++) //填充行数据
{
rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
//4.0 写入文件
using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create))
{
book.Write(wfile);
}
}
catch (Exception ex)
{
if (errorFun != null)
{
errorFun(ex);
}
return(false);
}
return(true);
}
/// <summary>
/// NPOI 导出会议的管理人员
/// </summary>
/// <param name="id"></param>
/// <param name="ids"></param>
public void ExportMeeting()
{
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("宴会统计");
//excel格式化
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss");
NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle();
numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000");
NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle();
textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@");
//设置单元格宽度
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("电话号码");
row1.CreateCell(2).SetCellValue("会议名称");
row1.CreateCell(3).SetCellValue("开始时间");
row1.CreateCell(4).SetCellValue("结束时间");
//设置列宽
row1.Sheet.SetColumnWidth(0, 100 * 50);
row1.Sheet.SetColumnWidth(1, 100 * 50);
row1.Sheet.SetColumnWidth(2, 150 * 50);
row1.Sheet.SetColumnWidth(3, 100 * 50);
row1.Sheet.SetColumnWidth(4, 100 * 50);
MessasgeData mgdata = new MessasgeData();
mgdata = Datafun.MgfunctionData("select Bank,UserName,b.MeetingName,convert(nvarchar(11),Start_Date,120) ,convert(nvarchar(11),End_Date,120) from tb_login a left join tb_Meeting b on a.Meeting=b.id where Lvl=1 and a.isdel=1 ");
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "会议管理人员.xlsx")); //添加Excel名字
for (int i = 0; i < mgdata.Mgdata.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(mgdata.Mgdata.Rows[i][0].ToString());
rowtemp.CreateCell(1).SetCellValue(mgdata.Mgdata.Rows[i][1].ToString());
rowtemp.CreateCell(2).SetCellValue(mgdata.Mgdata.Rows[i][2].ToString());
rowtemp.CreateCell(3).SetCellValue(mgdata.Mgdata.Rows[i][3].ToString());
rowtemp.CreateCell(4).SetCellValue(mgdata.Mgdata.Rows[i][4].ToString());
rowtemp.GetCell(0).CellStyle = textStyle;
rowtemp.GetCell(1).CellStyle = textStyle;
rowtemp.GetCell(2).CellStyle = textStyle;
rowtemp.GetCell(3).CellStyle = numberStyle;
rowtemp.GetCell(4).CellStyle = textStyle;
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
}
/// <summary>
/// 导出数据行
/// </summary>
/// <param name="dtSource"></param>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelSheet"></param>
/// <param name="excelWorkBook"></param>
protected static void InsertCell(DataTable dtSource, DataRow drSource, NPOI.SS.UserModel.IRow currentExcelRow, NPOI.SS.UserModel.ISheet excelSheet, HSSFWorkbook excelWorkBook, NPOI.SS.UserModel.ICellStyle cellStyle_DateTime)
{
for (int cellIndex = 0; cellIndex < _listColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = _listColumnsName.GetKey(cellIndex).ToString();
NPOI.SS.UserModel.ICell newCell = null;
System.Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
switch (rowType.ToString())
{
case "System.String": //字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
//格式化显示
newCell.CellStyle = cellStyle_DateTime;
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = currentExcelRow.CreateCell(cellIndex);
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 = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
case "System.Guid": //空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
}
}
public static void ExportExcel(string stuID)
{
List <Course> Courses = CourseService.GetCourses(stuID);
//创建Excel工作薄
HSSFWorkbook excelBook = new HSSFWorkbook();
//创建工作表1和工作表2并命名
NPOI.SS.UserModel.ISheet sheet1 = excelBook.CreateSheet("列表模式");
NPOI.SS.UserModel.ISheet sheet2 = excelBook.CreateSheet("周历模式");
//列表模式（表1）添加数据
//创建表头行 CreateRow(0)
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("课头号");
row1.CreateCell(1).SetCellValue("课程名");
row1.CreateCell(2).SetCellValue("课程类型");
row1.CreateCell(3).SetCellValue("学习类型");
row1.CreateCell(4).SetCellValue("授课学院");
row1.CreateCell(5).SetCellValue("授课教师");
row1.CreateCell(6).SetCellValue("专业");
row1.CreateCell(7).SetCellValue("学分");
row1.CreateCell(8).SetCellValue("学时");
row1.CreateCell(9).SetCellValue("上课时间");
row1.CreateCell(10).SetCellValue("备注");
for (int i = 0; i < Courses.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet1.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(Courses[i].LessonNum);
row.CreateCell(1).SetCellValue(Courses[i].LessonName);
row.CreateCell(2).SetCellValue(Courses[i].LessonType);
row.CreateCell(3).SetCellValue(Courses[i].LearninType);
row.CreateCell(4).SetCellValue(Courses[i].TeachingCollege);
row.CreateCell(5).SetCellValue(Courses[i].Teacher);
row.CreateCell(6).SetCellValue(Courses[i].Specialty);
row.CreateCell(7).SetCellValue(Courses[i].Credit);
row.CreateCell(8).SetCellValue(Courses[i].LessonHours);
row.CreateCell(9).SetCellValue(Courses[i].Time);
row.CreateCell(10).SetCellValue(Courses[i].Note);
}
//周历模式（表2）添加数据
NPOI.SS.UserModel.IRow firstRow = sheet2.CreateRow(0);
firstRow.Height = 20 * 20;
firstRow.CreateCell(0).SetCellValue("节次");
firstRow.CreateCell(1).SetCellValue("日");
firstRow.CreateCell(2).SetCellValue("一");
firstRow.CreateCell(3).SetCellValue("二");
firstRow.CreateCell(4).SetCellValue("三");
firstRow.CreateCell(5).SetCellValue("四");
firstRow.CreateCell(6).SetCellValue("五");
firstRow.CreateCell(7).SetCellValue("六");
List <IRow> rowList = new List <IRow>();
rowList.Add(firstRow);
for (int i = 0; i < 13; i++)
{
NPOI.SS.UserModel.IRow row = sheet2.CreateRow(i + 1);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue(i + 1);
rowList.Add(row);
}
for (int i = 0; i < Courses.Count; i++)
{
Course course = Courses[i];
List <List <Object> > temp = CourseTime.ParseClassTime(course);
for (int j = 0; j < temp.Count; j++)
{
string courseFirstWeek = ((int)temp[j][5]).ToString();
string courseLastWeek = ((int)temp[j][6]).ToString();
int courseBegin = (int)temp[j][2];
int courseEnd = (int)temp[j][3];
string weekDayByString = (string)temp[j][4];
int weekDay = CourseTime.WeekDayTrans(weekDayByString);
ICellStyle style = excelBook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = true;
ICell cell = rowList[courseBegin].CreateCell(weekDay);
cell.CellStyle = style;
cell.SetCellValue($"{course.LessonName}\n{courseFirstWeek}-{courseLastWeek}周");
sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(courseBegin, courseEnd, weekDay, weekDay));
}
}
if (!Directory.Exists(CQ.Api.AppDirectory + @"Export"))
{
Directory.CreateDirectory(CQ.Api.AppDirectory + @"Export");
}
using (FileStream fs = File.OpenWrite(CQ.Api.AppDirectory + $@"Export\{stuID}CourseTable.xls"))
{
excelBook.Write(fs);
}
}
public NPOISheet(NModel.ISheet sheet)
{
_sheet = sheet;
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Assert.AreEqual(6, wb.NumberOfFonts);
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CenterSelection;// ((short)22);
NPOI.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.Fill; //((short)111);
NPOI.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
r.CreateCell(3).CellStyle = (cs4);
r.CreateCell(4).CellStyle = (cs5);
r.CreateCell(5).CellStyle = (cs6);
r.CreateCell(6).CellStyle = (cs1);
r.CreateCell(7).CellStyle = (cs2);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(6, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
Assert.AreEqual(22, r.GetCell(1).CellStyle.GetFont(wb).FontHeight);
// cs3 = cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// cs4 --> 24 -> 23
Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
// cs5 --> 25 -> 24
Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
// cs6 = cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);
// Add a new duplicate, and two that aren't used
HSSFCellStyle csD = (HSSFCellStyle)wb.CreateCellStyle();
csD.SetFont(f1);
r.CreateCell(8).CellStyle = (csD);
HSSFFont f3 = (HSSFFont)wb.CreateFont();
f3.FontHeight = ((short)23);
f3.FontName = ("Testing 3");
HSSFFont f4 = (HSSFFont)wb.CreateFont();
f4.FontHeight = ((short)24);
f4.FontName = ("Testing 4");
HSSFCellStyle csU1 = (HSSFCellStyle)wb.CreateCellStyle();
csU1.SetFont(f3);
HSSFCellStyle csU2 = (HSSFCellStyle)wb.CreateCellStyle();
csU2.SetFont(f4);
// Check before the optimise
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(28, wb.NumCellStyles);
// Optimise, should remove the two un-used ones and the one duplicate
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// csD -> cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(8)).CellValueRecord.XFIndex);
}
/// <summary>
/// NPOI 宴会统计导出
/// </summary>
/// <param name="id"></param>
/// <param name="ids"></param>
public void Export(string id, string ids)
{
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("宴会统计");
//excel格式化
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss");
NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle();
numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000");
NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle();
textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@");
//设置单元格宽度
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("电话号码");
row1.CreateCell(2).SetCellValue("签到号");
row1.CreateCell(3).SetCellValue("红包");
row1.CreateCell(4).SetCellValue("礼物");
row1.CreateCell(5).SetCellValue("实际人数");
row1.CreateCell(6).SetCellValue("预计人数");
//设置列宽
row1.Sheet.SetColumnWidth(0, 80 * 50);
row1.Sheet.SetColumnWidth(1, 80 * 50);
row1.Sheet.SetColumnWidth(2, 50 * 50);
row1.Sheet.SetColumnWidth(3, 80 * 50);
row1.Sheet.SetColumnWidth(4, 150 * 50);
row1.Sheet.SetColumnWidth(5, 80 * 50);
row1.Sheet.SetColumnWidth(6, 80 * 50);
MessasgeData mgdata = new MessasgeData();
SqlParameter[] pms =
{
new SqlParameter("@meeting", id),
new SqlParameter("@ms", ids)
};
mgdata = Datafun.MgfunctionData("select MeetingName from tb_Meeting where id=@meeting", pms);
if (mgdata.Mgdatacount > 0)
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "" + mgdata.Mgdata.Rows[0][0].ToString() + ".xlsx"));//添加Excel名字
}
else
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "宴会统计.xlsx"));//添加Excel名字
}
mgdata = Datafun.MgfunctionData("Tj_rstj_poc", pms, "poc");
for (int i = 0; i < mgdata.Mgdata.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(mgdata.Mgdata.Rows[i][0].ToString());
rowtemp.CreateCell(1).SetCellValue(mgdata.Mgdata.Rows[i][1].ToString());
rowtemp.CreateCell(2).SetCellValue(mgdata.Mgdata.Rows[i][2].ToString());
rowtemp.CreateCell(3).SetCellValue(mgdata.Mgdata.Rows[i][3].ToString());
rowtemp.CreateCell(4).SetCellValue(mgdata.Mgdata.Rows[i][4].ToString());
rowtemp.CreateCell(5).SetCellValue(mgdata.Mgdata.Rows[i][5].ToString());
rowtemp.CreateCell(6).SetCellValue(mgdata.Mgdata.Rows[i][6].ToString());
rowtemp.GetCell(0).CellStyle = textStyle;
rowtemp.GetCell(1).CellStyle = textStyle;
rowtemp.GetCell(2).CellStyle = textStyle;
rowtemp.GetCell(3).CellStyle = numberStyle;
rowtemp.GetCell(4).CellStyle = textStyle;
rowtemp.GetCell(5).CellStyle = textStyle;
rowtemp.GetCell(6).CellStyle = numberStyle;
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
}
public void TestOptimiseFonts()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Add 6 fonts, some duplicates
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
IFont f3 = wb.CreateFont();
f3.FontHeight = ((short)33);
f3.FontName = ("Unique");
IFont f4 = wb.CreateFont();
f4.FontHeight = ((short)11);
f4.FontName = ("Testing");
IFont f5 = wb.CreateFont();
f5.FontHeight = ((short)22);
f5.FontName = ("Also Testing");
IFont f6 = wb.CreateFont();
f6.FontHeight = ((short)66);
f6.FontName = ("Also Unique");
// Use all three of the four in cell styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
Assert.AreEqual(5, cs1.FontIndex);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f4);
Assert.AreEqual(8, cs2.FontIndex);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f5);
Assert.AreEqual(9, cs3.FontIndex);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f6);
Assert.AreEqual(10, cs4.FontIndex);
Assert.AreEqual(25, wb.NumCellStyles);
// And three in rich text
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
HSSFRichTextString rtr1 = new HSSFRichTextString("Test");
rtr1.ApplyFont(0, 2, f1);
rtr1.ApplyFont(3, 4, f2);
r.CreateCell(0).SetCellValue(rtr1);
HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");
rtr2.ApplyFont(0, 2, f3);
rtr2.ApplyFont(3, 5, f5);
rtr2.ApplyFont(6, 8, f6);
r.CreateCell(1).SetCellValue(rtr2);
// Check what we have now
Assert.AreEqual(10, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Optimise
HSSFOptimiser.OptimiseFonts(wb);
// Check font count
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Check font use in cell styles
Assert.AreEqual(5, cs1.FontIndex);
Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
Assert.AreEqual(8, cs4.FontIndex); // two have gone
// And in rich text
// RTR 1 had f1 and f2, unchanged
Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
// RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(6));
Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(7));
}