//Dictionary<int, uint> 表格列的索引(from zero)和样式编号
private static Row CreateRow(uint rowNo, uint columnNo, DataRow dataRow, DefaultCellStyle defaultCellStyle, Dictionary <uint, uint> eachCellStyle = null)
{
Row newRow = new Row();
newRow.RowIndex = rowNo;
if (dataRow != null)
{
for (uint i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell newCell = null;
string cellref = GetCellReference(rowNo, columnNo + i);
Type cellType = dataRow.Table.Columns[(int)i].DataType;
object cellValue = dataRow[(int)i];
if (eachCellStyle != null && eachCellStyle.Keys.Contains(columnNo + i))
{
newCell = createCell(cellref, cellType, cellValue, defaultCellStyle, eachCellStyle[columnNo + i]);
}
else
{
newCell = createCell(cellref, cellType, cellValue, defaultCellStyle);
}
if (newCell != null)
{
newRow.Append(newCell);
}
}
}
return(newRow);
}
private static SpreadsheetDocument openFile(string filepath, out DefaultCellStyle defaultCellStyle)
{
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
SetExcelPivotTableCacheAutoReflesh(spreadsheetDocument);
createDeafultStyle(spreadsheetDocument.WorkbookPart, out defaultCellStyle);
return(spreadsheetDocument);
}
private static Cell createCell(string reference, Type type, object value, DefaultCellStyle defaultCellStyle, uint customStyle = 0)
{
if (reference == null || type == null || defaultCellStyle == null)
{
return(null);
}
Cell theCell = new Cell();
theCell.CellReference = reference;
CellValues theCellValue = GetValueType(type);
theCell.DataType = new EnumValue <CellValues>(theCellValue);
if (theCellValue == CellValues.Date)
{
if (value.ToString() != "")
{
theCell.CellValue = new CellValue(((DateTime)value));
}
else
{
theCell.CellValue = new CellValue("");
}
if (customStyle == 0)
{
theCell.StyleIndex = defaultCellStyle.dateTimeIndex;
}
else
{
theCell.StyleIndex = customStyle;
}
}
else
{
theCell.CellValue = new CellValue(value.ToString());
if (customStyle == 0)
{
theCell.StyleIndex = defaultCellStyle.normalIndex;
}
else
{
theCell.StyleIndex = customStyle;
}
}
return(theCell);
}
//category .0 normal 1 border 2 black 3black and border
public static uint getDefaultStyle(System.Type type, DefaultCellStyle defaultCellStyle, int category)
{
uint res = 0;
CellValues theCellValue = GetValueType(type);
if (theCellValue == CellValues.Date)
{
if (category == 0)
{
res = defaultCellStyle.dateTimeIndex;
}
else if (category == 1)
{
res = defaultCellStyle.blackdateTimeIndex;
}
else if (category == 2)
{
res = defaultCellStyle.dateTimeIndex_border;
}
else if (category == 3)
{
res = defaultCellStyle.blackdateTimeIndex_border;
}
}
else
{
if (category == 0)
{
res = defaultCellStyle.normalIndex;
}
else if (category == 1)
{
res = defaultCellStyle.blackIndex;
}
else if (category == 2)
{
res = defaultCellStyle.normalIndex_border;
}
else if (category == 3)
{
res = defaultCellStyle.blackIndex_border;
}
}
return(res);
}
public override object Clone()
{
DataGridViewRow row = (DataGridViewRow)MemberwiseClone();
row.DefaultCellStyle = (DataGridViewCellStyle)DefaultCellStyle.Clone();
row.HeaderCell = (DataGridViewRowHeaderCell)HeaderCell.Clone();
row.SetIndex(-1);
row.cells = null;
foreach (DataGridViewCell cell in Cells)
{
row.Cells.Add(cell.Clone() as DataGridViewCell);
}
row.SetDataGridView(null);
return(row);
}
//建立一个最小样式表.
private static void createDeafultStyle(WorkbookPart workbookpart, out DefaultCellStyle cellStyle)
{
cellStyle = new DefaultCellStyle(0, 0, 0, 0, 0, 0, 0, 0);
if (workbookpart != null)
{
//1.建立必要的文件和其根节点.
if (workbookpart.WorkbookStylesPart == null)
{
workbookpart.AddNewPart <WorkbookStylesPart>();
}
if (workbookpart.WorkbookStylesPart.Stylesheet == null)
{
workbookpart.WorkbookStylesPart.Stylesheet = new Stylesheet();
}
if (workbookpart.WorkbookStylesPart.Stylesheet.Fonts == null)
{
workbookpart.WorkbookStylesPart.Stylesheet.Fonts = new Fonts();
}
if (workbookpart.WorkbookStylesPart.Stylesheet.Fills == null)
{
workbookpart.WorkbookStylesPart.Stylesheet.Fills = new Fills(new Fill(new PatternFill()
{
PatternType = PatternValues.None
}), new Fill(new PatternFill()
{
PatternType = PatternValues.Gray125
}));
}
if (workbookpart.WorkbookStylesPart.Stylesheet.Borders == null)
{
workbookpart.WorkbookStylesPart.Stylesheet.Borders = new Borders();
}
if (workbookpart.WorkbookStylesPart.Stylesheet.CellFormats == null)
{
workbookpart.WorkbookStylesPart.Stylesheet.CellFormats = new CellFormats();
}
if (workbookpart.WorkbookStylesPart.Stylesheet.NumberingFormats == null)
{
workbookpart.WorkbookStylesPart.Stylesheet.NumberingFormats = new NumberingFormats();
}
//自定义字体
UInt32 defaultFont = createFont(workbookpart.WorkbookStylesPart.Stylesheet, "Microsoft YaHei", (double)11, false, System.Drawing.Color.Black);
UInt32 BoldFont = createFont(workbookpart.WorkbookStylesPart.Stylesheet, "Microsoft YaHei", (double)11, true, System.Drawing.Color.Black);
uint borderEmpty = createEmptyBorder(workbookpart.WorkbookStylesPart.Stylesheet);
uint border4Line = create4LinesBorder(workbookpart.WorkbookStylesPart.Stylesheet);
//自定义数字格式,时间格式
UInt32 dateDeafult = 240;
var numberFormatDate_index = new NumberingFormat();
numberFormatDate_index.FormatCode = new StringValue("yyyy-mm-dd");
numberFormatDate_index.NumberFormatId = dateDeafult;//随意定义100~200之间.
workbookpart.WorkbookStylesPart.Stylesheet.NumberingFormats.InsertAt(numberFormatDate_index, workbookpart.WorkbookStylesPart.Stylesheet.NumberingFormats.Count());
//自定义最终给用户的单元格式.
cellStyle.normalIndex = createCellFormat(workbookpart.WorkbookStylesPart.Stylesheet, null, defaultFont, null, null);
cellStyle.dateTimeIndex = createCellFormat(workbookpart.WorkbookStylesPart.Stylesheet, null, defaultFont, null, dateDeafult);
cellStyle.blackIndex = createCellFormat(workbookpart.WorkbookStylesPart.Stylesheet, null, BoldFont, null, null);
cellStyle.blackdateTimeIndex = createCellFormat(workbookpart.WorkbookStylesPart.Stylesheet, null, BoldFont, null, dateDeafult);
cellStyle.normalIndex_border = createCellFormat(workbookpart.WorkbookStylesPart.Stylesheet, border4Line, defaultFont, null, null);
cellStyle.dateTimeIndex_border = createCellFormat(workbookpart.WorkbookStylesPart.Stylesheet, border4Line, defaultFont, null, dateDeafult);
cellStyle.blackIndex_border = createCellFormat(workbookpart.WorkbookStylesPart.Stylesheet, border4Line, BoldFont, null, null);
cellStyle.blackdateTimeIndex_border = createCellFormat(workbookpart.WorkbookStylesPart.Stylesheet, border4Line, BoldFont, null, dateDeafult);
workbookpart.WorkbookStylesPart.Stylesheet.Save();
}
}
private static bool SetOrUpdateCellValue(SheetData sheetData, uint rowNumber, uint columnNumber, Type datatype, object value, DefaultCellStyle defaultCellStyle, uint customStyle = 0)
{
if (sheetData != null && value != null && datatype != null)
{
//创建cell.
//0.不存在row,建立row,插入cell.
//0.row存在1.删除cell.2.是否有比这个cell更大的cell,有插入大cell之前.否则直接附加在row后面.
string cellRefrence = GetCellReference(rowNumber, columnNumber);
IEnumerable <Row> equalOrbiggerRows = sheetData.Elements <Row>().Where(x => x.RowIndex >= rowNumber);
Row equalRow = null, biggerRow = null;
if (equalOrbiggerRows != null && equalOrbiggerRows.Count() > 0 && equalOrbiggerRows.First().RowIndex == rowNumber)
{
equalRow = equalOrbiggerRows.First();
}
else if (equalOrbiggerRows != null && equalOrbiggerRows.Count() > 0 && equalOrbiggerRows.First().RowIndex > rowNumber)
{
biggerRow = equalOrbiggerRows.First();
}
if (equalRow != null)//存在row. 1.是否存在cell,存在跟新,不存在建立新cell.2.是否有比这个cell更大的cell,有插入大cell之前.否则直接附加在Row后面.
{
IEnumerable <Cell> equalOrbiggerCells = equalRow.Elements <Cell>().Where(x => x.CellReference >= new StringValue(cellRefrence));
Cell equalCell = null;
Cell biggerCell = null;
if (equalOrbiggerCells != null && equalOrbiggerCells.Count() > 0 && equalOrbiggerCells.First().CellReference == cellRefrence)
{
equalCell = equalOrbiggerCells.First();
}
else if (equalOrbiggerCells != null && equalOrbiggerCells.Count() > 0 && equalOrbiggerCells.First().CellReference > new StringValue(cellRefrence))
{
biggerCell = equalOrbiggerCells.First();
}
Cell newCell = createCell(cellRefrence, datatype, value, defaultCellStyle, customStyle);
if (equalCell != null)
{
equalOrbiggerRows.First().ReplaceChild(newCell, equalCell);
}
else
{
if (biggerCell != null)
{
equalOrbiggerRows.First().InsertBefore(newCell, biggerCell);
}
else
{
equalOrbiggerRows.First().Append(newCell);
}
}
}
else//不存在.新建row and cell.
{
Row newrow = new Row();
newrow.RowIndex = rowNumber;
Cell theCell = IncOpenExcel.createCell(cellRefrence, datatype, value, defaultCellStyle, customStyle);
if (theCell != null)
{
newrow.Append(theCell);
}
if (biggerRow != null)
{
sheetData.InsertBefore(newrow, equalOrbiggerRows.First());//插入的行不是最大的,插到比它大的前面.
}
else
{
sheetData.Append(newrow);; //插入的行是最大的,直接附加到最后
}
}
}
return(true);
}
private static bool CreateOrUpdateRowAt(SheetData sheetData, DataRow dataRow, uint rowNo, uint columnNo, DefaultCellStyle defaultCellStyle, Dictionary <uint, uint> eachColumnStyle)
{
if (rowNo > 0 && columnNo > 0 && defaultCellStyle != null && sheetData != null)
{
Row newRow = CreateRow(rowNo, columnNo, dataRow, defaultCellStyle, eachColumnStyle);
CreateOrUpdateRowAt(sheetData, newRow, newRow.RowIndex);
}
return(true);
}
private static SpreadsheetDocument CreateFile(string newFilePath, string firstSheetName, out DefaultCellStyle defaultCellStyle)
{
//建立xlsx文件
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(newFilePath, SpreadsheetDocumentType.Workbook, true);
//建立xl,worksheets目录(会默认生成0字节的workbook和worksheet,以及2个res文档)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
//建立workbook文档,设定模式的worksheet (sheet的3个属性必须填写,特别的是name是这里设定,有点不符合常见的抽象思维)
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = firstSheetName
};
sheets.Append(sheet);
workbook.AppendChild <Sheets>(sheets);
workbookpart.Workbook = workbook;
//建立默认的worksheet文档(可以先workbook,后worksheet)
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;//给默认的worksheet赋值,否则0字节.
//建立样式文件
createDeafultStyle(workbookpart, out defaultCellStyle);
spreadsheetDocument.Save();
return(spreadsheetDocument);
}
//0:normal 1. 4lines 2. balck 3black
public static Dictionary <uint, uint> getRowStyles(DataColumnCollection dataColumn, uint startColumnNo, int cateogry, DefaultCellStyle defaultCellStyle)
{
Dictionary <uint, uint> styles = new Dictionary <uint, uint>();
if (dataColumn != null)
{
for (int i = 0; i < dataColumn.Count; i++)
{
uint defaultStyle = IncOpenExcel.getDefaultStyle(dataColumn[i].DataType, defaultCellStyle, cateogry);
styles.Add((uint)i + startColumnNo, defaultStyle);
}
}
return(styles);
}
